Visual Basic/VB, ADO Performance

by **Tomasz Klim** » Sat, 18 Feb 2006 04:25:50 GMT

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?

by **Saga** » Sat, 18 Feb 2006 04:54:43 GMT

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

by **Tomasz Klim** » Sat, 18 Feb 2006 06:23:48 GMT

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...

Utkownik "Saga" < XXXX@XXXXX.COM > napisaw wiadomoi
>> >> 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 >>

>>>> 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? >>>> >>>> >> >>

by **Douglas Marquardt** » Sat, 18 Feb 2006 06:38:23 GMT

Why not just dump the data into an array (use GetRows) and traverse
the array instead of the recordset -- it will be much faster.

Doug.

>>>> >>>> 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 >>>>

>>>>>> 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? >>>>>> >>>>>> >>>> >>>> >> >>

by **Gman** » Sat, 18 Feb 2006 23:54:12 GMT

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.

>>> 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 >>>

>>>> 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? >>>> >>>> >>> >> >>

by **Tomasz Klim** » Sun, 19 Feb 2006 00:56:16 GMT

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

both
>>>>> 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 >>>>>

>>>>>> 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? >>>>>> >>>>>> >>>>> >>>> >>>>

by **Gman** » Sun, 19 Feb 2006 05:19:36 GMT

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

a) Is the cursor client or server side?

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

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...)

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

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

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

Tomasz Klim wrote:

by **Tomasz Klim** » Mon, 20 Feb 2006 21:47:35 GMT

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

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

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

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

You're still right.

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:(

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 :(

query,
Basic,
the
and
execution
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? >>>>>>>> >>>>>>>> >>>>>> >>>> >>>>

Visual Basic/VB, ADO Performance

Visual Basic/VB >> ADO Performance

Visual Basic/VB >> ADO Performance

Visual Basic/VB >> ADO Performance

ADO Performance

ADO Performance

ADO Performance

ADO Performance

ADO Performance