Re: ADO Performance

It's really quite strange in my opinion...

a) Is the cursor client or server side?

Honestly, I don't know. But it's default in both programs.

b) Is the cursor type forwardonly, static or dynamic?

Dynamic. But I tried all of these, with similar effects.

I think should be able to break down this bottleneck into two (at least)
sections (this will vary a little depending on cursor location):

(1) Find out how long it takes to run the query and get the recordset
returned. (I don't think this is going to vary much...)

You're right. Besides, I start to counting time after the query executes.

(2) Find out how long it takes to loop thru the recset - I suspect this
is where the hit is...

You're still right.

Further questions:
(c) Are the loops thru the recset identical, you don't have a DoEvents
knocking around in any of them do you?

I have DoEvents in both programs, but I disabled it. Also, I removed
from the loop everything but oRS.MoveNext, with no change on performance:(

(d) What are you going when you go through the loop? Updating?

Dim dt As Date
....
dt = Now
While Not oRS.EOF
oRS.MoveNext
Wend
MsgBox DateDiff("s",dt,Now)

And it's still running slower, that the other program. And this other
program
is bigger, do more things, and still runs about 2 times faster... I don't
understand
it, it's quite abnormal IMHO :(


Tomasz Klim wrote:
You're of course right - but note that _both_ programs use the same
query,
and one is almost two times faster, than the other.

Are there any interdepenciences between internal components of Visual
Basic,
COM servers etc.?


Uzytkownik "Gman" <nah> napisal w wiadomosci
news:OgPeUOKNGHA.3460@xxxxxxxxxxxxxxxxxxxxxxx
As an aside, SELECT * will always run slower than specifying the colum
names since the engine has to figure out what columns are available,
the
order etc.

Further, loading a whole table into a recordset is often necessary if
it's a config table or such but if it's a large table you could try and
place some logic on the server side to reduce the execution time.


Tomasz Klim wrote:
Yes, both programs use ADO 2.6, the same connection strings, the same
queries, the same options and everything.

Moreover, I created new program, that connects do the same database,
and
sends the same query. After traversing the loop, it shows the
execution
time: 45 seconds...

I traced both programs using Visual Basic debugger and found out, that
both
programes have identical database-related variables...



U¿ytkownik "Saga" <antiSpam@xxxxxxxxxxxxx> napisa³ w wiadomo¶ci
news:%23kIvhRANGHA.3852@xxxxxxxxxxxxxxxxxxxxxxx
Do both programs use ADO?
The same version of ADO?
The same connect strings?
The same open recordset statements?
Any differences in cursor location?

Regards
Saga

"Tomasz Klim" <tklim-msNON@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23xkPZBANGHA.3100@xxxxxxxxxxxxxxxxxxxxxxx
Hi

I have a strange question about ADO performance:

I have a program in Visual Basic 6.0, that connects to Pervasive.SQL
via
DSN, and then executes "select * from bd". Then, it slides through
Recordset:

While Not oRS.EOF
' processing part
oRS.MoveNext
Wend

And I have another program, that does exactly the same (it is
different in
many ways, but at the database part, it does the same). I don't know
if it's
important, but it's much bigger, than the first program.


The problem is, that the first program processes the table in 23-24
seconds,
while the other in 39-40 seconds!

I don't know, what's going on - I even removed everything except
core
While
loop with oRS.MoveNext command. I also checked the .vbp files, and
don't see
anything strange, or different between these files.

Did someone had such problem already?







.

Relevant Pages

  • Re: ADO Performance
    ... It's really quite strange in my opinion... ... Is the cursor type forwardonly, ... Find out how long it takes to loop thru the recset - I suspect this is where the hit is... ... place some logic on the server side to reduce the execution time. ...
    (microsoft.public.vb.general.discussion)
  • Re: Cursor ODBC
    ... Aus der SQL Server Dokumentation zu DECLARE CURSOR ... gibts auch keine absoluten Positioniervorgänge. ... > Ja, solange dieses Recordset nicht geschlossen wird, bleiben die ...
    (microsoft.public.de.access.clientserver)
  • Re: ADO und Index
    ... Private WithEvents mCnn as ADODB.Connection ... Recordset etwas übersichtlicher und wartungsfreundlicher gestalten würdest, ... Das Recordset wird im gezeigten Beispiel mit clientseitigem Cursor geöffnet. ... Sekunden vom Server zum Client. ...
    (microsoft.public.de.vb.datenbank)
  • Re: ADO Performance, command cachezize > 1?
    ... most likely you opened recordset on a server side. ... > Opening rs using the recordset method, server side cursor, cachesize = ... > The reason Im trying to use the command method: ...
    (microsoft.public.vb.database.ado)
  • Re: ADO Performance, command cachezize > 1?
    ... most likely you opened recordset on a server side. ... > Opening rs using the recordset method, server side cursor, cachesize = ... > The reason Im trying to use the command method: ...
    (microsoft.public.vb.general.discussion)