How can I speed up a Do Loop with Nested If Then statements

[jmcclain]

I have an application where I need to run a series of do loops with nested if then statements. The code looks like this:

sql statement to calculate rst2!variance

Do until rst1.eof
If rst1!AR = rst2!AR then
If rst1!COA = rst2!COA then
If rst1!Part_No = rst2!Part_No
rst1!Variance = rst2!Variance
rst2.movenext
rst1.movefirst
else
rst1.movenext
end if
else
rst1.movenext
end if
else
rst1.movenext
end if
loop

Unfortunately, I have about 30 of these loops in my code as I update a table with various calculated fields. The calc fields are not simple A*B, but more like sum of this group of records...count of records that meet this criteria...

As you can imagine the code takes a long, long time to run especially when running over 10,000+ records.

Does anyone have any ideas on how to speed up the code to better handle this? Is there a different logic path that I should be going down?

Help!!

 

[Darrylles]

Hiya,

I'm no SQL expert, but this looks like VBA to me rather than SQL.

Can you explain that to me, or at least post the actual SQL.

Also, post another one of your 30 loops please, for comparison (it may look identical, but the data name references will be different).

Something that seems obvious to me is that you have nested 'if's when you should really be 'and'ing the conditions.....

e.g.

if (a1=b1) then
if (a2=b2) then
if (a3=b3) then
do this
else
do that
end if
else
do that
endif
else
do that
endif

Should logically be:

if ((a1=b1) and (a2=b2) and (a3=b3)) then
do this
else
do that
endif

because the 'do this' only happens when all 3 conditions are true, and when ANY of the 3 is false - the same 'do that' happens.
This would speed execution up.

If you have 30 identical sections of code like this (apart from the 'field' and 'data' references, then please post another 'loop' as I requested above.
This can all be cut down to 5 lines of code as I've shown - and you will call it with 30 lines of code (so all you have is 35 lines of code in total - where you now have over 500 lines of code.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[MichaelRed]

Also, thte process looks like it really SHOULD be a query.


in pesudo SQL.
'strSql = Update rst2!Variance = rst1!Variance
'where
'rst2!Ar = rst!ar and
'rst2!COA = rst!COA and
'rst2!Part_No = rst!ar Part_No;

missing some (important) syntax, but just paraphrasing your ?sql? ?code?


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Darrylles]

Hiya,

OK, I understand why you referred to it as SQL incorrectly - you prepare a SQL string and use this to produce a query string and produce a recordset with it by using VBA.
This 'pseudo SQL' string IS in the 'DO THIS' portion of the code yes?

If so, I can't add anything more to my last post, until you supply another one of the 30 'loops'. (In order for me to show you an example of the function that you'll need to simplify your VBA code).

I think that my adjustment to your 'if..then..else' construct will be the main factor in making the whole process faster.

If you show an example of a different one of the 30 'loops', then I can cut your code from 500 lines to 35-ish.

Regards,

Darylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."