Efficiency Problem - Need for more speed

[jmcclain]

I have an Access application that has evolved into more than it should. In doing so, I have split the application into a front-end residing on local PCs and a back-end residing on a network server. The back-end only contains tables.

The application has a drill down feature allowing users to drill down to detail information. The drill down forms show the 'gap counts' for each level. Since this is a multiuser app, we've had to put in code to dynamically calculate gap counts as each form opens up. This has become a big resource hog, especially going across networks and especially as the database becomes more and more populated.

I'm hoping if I put my loop out here, someone will spot an inefficiency in it - allowing me to speed it up. I'm close to scrapping the current model and rewriting a better client/server type app.

Here's the loop:

rst.Open "tblTLevelD", cnn, adOpenDynamic, adLockOptimistic
If Not rst.EOF Then rst.MoveFirst
Do While Not rst.EOF
rst2.Open "select * from tblTLevelE where level_id = '" & rst![Component] & "' and actioncount > 0 order by counter;", cnn, adOpenDynamic, adLockOptimistic
hcount2 = 0
mcount2 = 0
lcount2 = 0
gcount2 = 0
Do While Not rst2.EOF
If rst2!HighCount > 0 Then
hcount2 = hcount2 + 1
gcount2 = gcount2 + 1
ElseIf rst2!MediumCount > 0 Then
mcount2 = mcount2 + 1
gcount2 = gcount2 + 1
ElseIf rst2!LowCount > 0 Then
lcount2 = lcount2 + 1
gcount2 = gcount2 + 1
End If
cntlp1 = cntlp1 + 1
rst2.MoveNext
Loop
rst2.Close
rst!ActionCount = gcount2
rst!LowCount = lcount2
rst!MediumCount = mcount2
rst!HighCount = hcount2
rst.Update
cntlp2 = cntlp2 + 1
rst.MoveNext
Loop
rst.Close

Basically I'm just summing up the gap counts from tblTlevelE and putting them into tblTLevelD. Nothing fancy.

I tried to write a SQL update statement, but I ran into the Access dead-end that doesn't allow you to use a subquery with a summation statement. The sql statement I was trying was

UPDATE tblTLevelD SET tblTLevelD.HighCount = (SELECT Sum(tblTLevelE.HighCount) AS SumOfHighCount FROM tblTLevelE where [tblTLevelE].[Level_Id]=[tblTLevelD].[Component] GROUP BY tblTLevelE.Level_Id );

It was returning 'Operation must use an updateable query' message.

Any thoughts on how to make this loop more efficient would be very much appreciated. If at all possible, I'm trying to avoid having to scrap this type of dynamic updates and being forced into a 'batch processing' type of mode.

 

[vbajock]

Off hand I would say you need to get rid of the constant table opens in your loop. Can you index the table tblTLevelE on level_id? You could then search the index using .seek to retrieve your data, probably a zillion times faster than your current code that opens a record set for every record in your loop table.

 

[Tranman]

Hi,
Maybe I'm missing something, but it looks like you're not doing anything that is dependent upon the order of rst2, so I'd take the "order by counter" out of there.

Have you indexed level_id? If not, that might juice this process up quite a bit, depending on the size of tblTLevelE. I did that once in a similar loop, and it reduced the runtime from 45 minutes to about 4. (I had **Way** big tables.)

In rst2, do you really need all of the fields? If not, specifying a field list, instead of "select *", can speed this logic up some.

On a broader level, though, I think that I would "turn the logic inside out". Instead of running one query, and using it to provide criteria for rst2 (about a zillion times), I would just load up rst2 once, ordered by level_id, and key my loop upon a change in level_id. Like so:

rst2.open "select * from tblTLevelE where actioncount > 0 order by level_id;", cnn, adOpenDynamic, adLockOptimistic

rst2.movefirst
Do while not rst2.eof
'Zero out your accumulators here
strLevel = rst2!level_id
Do until (rst2!level_id <> strLevel)
'Add to your totals here (If.....)
rst2.movenext
If rst2.EOF Then Exit Do
Loop
cnn.Execute &quot;Update tblTLevelD set ActionCount = &quot; &
gcount2 & &quot;, LowCount = &quot; & lcount2 &
&quot; .....etc. where Component = level_id;&quot;
Loop

The beauty of this approach is that you only have to query tblTLevelE one time, instead of one time for each level_id.

My guess is that it will scream.

Good Luck,
Tranman