Speeding up the loop

[chip_zli]

Hello!

Using DAO/ODBC I opened the (non - Access ) database and loaded DAO.Recordset . Then I loop thrue rs to sum up some data, but it is so slow that I suspect I'm missing something. Is usual that VBA loops are so slow, or what? The code looks something like :

initBs ' create workspace

artskup = "[PAR_ID] = '" & partner & "' AND [VR_DOK] = 'I01' AND [DAT_KNJ] >= #" & ForEngDate(pocdat) & "# AND [DAT_KNJ] <= #" & ForEngDate(krajdat) & "#" ' format query

selectBs baza, artskup, rs ' create connection, make select and
' returns filled recordset

rs.MoveFirst ' preps
broj = 0 ' Single type

Do Until rs.EOF

broj = broj + NulltoZero(rs!KOL) ' security reason, null enteries are
' possible
rs.MoveNext

Loop ' sum it up!


Entire loop takes like 10 seconds, raw recordset data is cca 100kB in size.
Note that NulltoZero isn't reason, without it isn't very faster.

 

[namliam]

Why dont you use a group by/sum query to start with? This would run (i think) faster AND more accurate...

All you would need then is

Broj = rs!SumKol

Greetz & GL

 

[chip_zli]

Why dont you use a group by/sum query to start with? This would run (i think) faster AND more accurate...

---cut---

Greetz & GL

Hey, thanks ... sum query did speed things up, for cca 20% . OTOH when
summing thrue 35MB database, it still takes like 5-10 minutes. Is it posible
to compile VBA code (one switchboard, 6 forms, modules used : DAO)
with VB6.0 ??? Most of execution time is taken by SQL/ODBC queries, but can help!

And btw why did you wrote >> more ACCURATE << ???


thx agn

 

[namliam]

More accurate, because you dont need to catch nulls and stuff....

Same code in VB as in VBA will take more or less the same amount off time i am afraid....

If you are running a lot of odbc try executing the count on the server side instead of the client side. This will cut down on the processing... and dataflow and ... stuff..

Greetz

 

[chip_zli]

Same code in VB as in VBA will take more or less the same amount off time i am afraid....

I assumed to be so, but there is a another reason why I wanted to compile
program, basically copyright issues.

If you are running a lot of odbc try executing the count on the server side instead of the client side. This will cut down on the processing... and dataflow and ... stuff..
Greetz

Gee, I didn't know I can do that. How do I do it? I mean, don't send me code, just point out where do I tell odbc to perform caliculations on server
side. Thanks a lot!

 

[namliam]

I assumed to be so, but there is a another reason why I wanted to compile
program, basically copyright issues.

You can compile to an MDE, which is basicaly a closed off mdb. No way (i know off) to compile to vb ... or anything else for that matter....

Gee, I didn't know I can do that. How do I do it? I mean, don't send me code, just point out where do I tell odbc to perform caliculations on server
side. Thanks a lot!

You might allready be doing that....
You "simply" use a pass-through query with a select from where group by statement.
This way calculation is done on the server AND only the aggregat results are transferred to to client instead of the whole db...

Greetz & GL