Can my code be speeded up? 1

[mrf1xa]

Hi

I am fairly new to VBA and in at the deep end. I have come up with the code below, which does what I want accurately. However, the issue is time. On my standalone development machine, split FE and BE, it runs in around 5 seconds, which is quite acceptable. When I run it on the network, this slows down to around a minute, which isn't acceptable. Can the experts offer any advice on how to speed it up please?

Using A2K with Win2K.

Thanks in advance.

'************UPDATES ACTUAL AND FORECAST RESOURCE COST FOR ALL PROJECTS IN tblPROJECT LIST********************

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim rst4 As DAO.Recordset
Dim mth As Date
Dim totcost As Long
Dim actcost As Long
Dim i As Integer
i = 0
'Open recordset based on tblProjectList
Set rst4 = CurrentDb.OpenRecordset("SELECT * FROM [tblProjectList]&quot
If rst4.RecordCount > 0 Then 'check that there are records in the recordset
rst4.MoveFirst
Do Until rst4.EOF
'Open rcordset from tblCBA where it matches rst4
Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM [tblCBA] WHERE (([tblCBA].[ID] = " & _
" " & rst4![ID] & &quot);&quot
If rst1.RecordCount > 0 Then 'Check that there are records in recordset
rst1.MoveFirst
Do Until rst1.EOF 'Initiate loop through all monhtly records for this project
mth = Format(rst1![Month], "mm/dd/yyyy&quot 'set mth to American date format
'open subrecordset from tblResourceUsed for this project and selected month
Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM [tblResourceUsed]" & _
"WHERE (([tblResourceUsed]![Month] = #" & mth & "#)) AND " & _
"(([tblResourceUsed]![ID] = " & rst4![ID] & &quot);&quot
If rst2.RecordCount > 0 Then 'Check that there are records in the recordset
rst2.MoveFirst
totcost = 0 'initialise totcost at zero
Do Until rst2.EOF 'initialise loop through all records for this month
totcost = totcost + rst2![Time] * 7 * DLookup("[CostPerHour]", _
"tblResources", "[ResourceID] = '" & rst2![ResourceID] & "'&quot
rst2.MoveNext
Loop
rst2.Close
Set rst2 = Nothing
End If
'Open a subrecordset frm tblResourceWSheet for this project and selected month
Set rst3 = CurrentDb.OpenRecordset("SELECT * FROM [tblResourceWSheet]" & _
"WHERE (([tblResourceWSheet]![ResMonth] = #" & mth & "#)) AND " & _
"(([tblResourceWSheet]![ProjID] = " & rst4![ID] & &quot);&quot
If rst3.RecordCount > 0 Then 'Check that there are records in the recordset
rst3.MoveFirst
actcost = 0 'initialise actcost at zero
Do Until rst3.EOF 'initialise loop through all records for this month
actcost = actcost + rst3![Hours] * DLookup("[CostPerHour]", _
"tblResources", "[ResourceID] = '" & rst3![ResourceID] & "'&quot
rst3.MoveNext
Loop
rst3.Close
Set rst3 = Nothing
End If
rst1.Edit
rst1![FCastResCost] = totcost 'insert totcost total into CBA table
rst1![ActResCost] = actcost 'insert actcost total into CBA table
'Calculate the total Forecast cost
If IsNull(rst1![FcastNonResCost]) Then
rst1![FcastNonResCost] = "0"
End If
rst1![FcastCost] = rst1![FcastNonResCost] + rst1![FCastResCost]
'Now calulate the actual total cost
If IsNull(rst1![ActNonResCost]) Then
rst1![ActNonResCost] = "0"
End If
rst1![Cost] = rst1![ActNonResCost] + rst1![ActResCost]
'calculate the forecast difference and actual difference
If IsNull(rst1![FCastBen]) Then
rst1![FCastBen] = 0
ElseIf IsNull(rst1![Bens]) Then
rst1![Bens] = 0
End If
rst1![FCastDiff] = rst1![FcastCost] - rst1![FCastBen]
rst1![Diff] = rst1![Cost] - rst1![Bens]
'Now calculate the differences
rst1![Diff] = rst1![Cost] - rst1![Bens]
rst1![VarBens] = rst1![Bens] - rst1![FCastBen]
rst1![VarCost] = rst1![Cost] - rst1![FcastCost]
rst1![VarNett] = rst1![Diff] - rst1![FCastDiff]
'Now calculate the %s
If rst1![VarBens] = 0 Then
rst1![VarBens%] = 0
Else
rst1![VarBens%] = rst1![Bens] * 100 / rst1![FCastBen]
End If
If rst1![VarCost] = 0 Then
rst1![VarCost%] = 0
ElseIf rst1![FcastCost] = 0 Then
rst1![VarCost%] = 0
Else
rst1![VarCost%] = rst1![Cost] * 100 / rst1![FcastCost]
End If
If rst1![FCastDiff] = 0 Then
rst1![VarNet%] = 0
Else
rst1![VarNet%] = rst1![Diff] * 100 / rst1![FCastDiff]
End If

rst1.Update
rst1.MoveNext
Loop
End If
rst1.Close
Set rst1 = Nothing
rst4.MoveNext
Loop
End If
rst4.Close
Set rst4 = Nothing Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[mp9]

I would recommend declaring some variables of type field and setting these to be equal to data items in your recordsets. These can then be referred to inside your loops. In a simple example, the following code

Dim strSupplier As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSupplier", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
    strSupplier = rst("SupplierName")
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

would become

Dim strSupplier As String
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSupplier", dbOpenDynaset)
Set fld = rst("SupplierName")
rst.MoveFirst
Do Until rst.EOF
    strSupplier = fld
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

This can make looping through recordsets up to 20 times faster! For a slightly more detailed explanation of this approach, check out

http://www22.brinkster.com/accessory/modules/007.shtml

As an aside, for a miniscule performance hike you could replace

CurrentDb

with

DBEngine(0)(0)

HTH.

http://www22.brinkster.com/accessory/

 

[MichaelRed]

I haven't reviewed the code in any detail. At a GLANCE through the first few lines, it APPEARS to be simply retrieving RELATED records and doing some conditional update to a few fields.

If this is even "close", you should do most (all) of the work in one (or more) queries. Queries will almost invariably preform better than code, so even if it required three queries to join the whole thing into a single resordset and a module for the calcs, this would probably be somewhat faster than looping through the FOUR nested recordsets via code.

You are using SQL strings where stored (e.g. comppiled) queries would easily work. Stored queries will always be 'faster' than the SQL string, as Ms. A. will compile the query each time you use it.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[planetdrouin]

Have you deselected all the 'Name AutoCorrect' check boxes which is found under 'Options' then the 'General' tab. By doing this, you will greatly improve the speed of your app.

Lawrence

 

[mrf1xa]

Thank you all for your input.

MP9, I did some tinkering over the weekend and found your changes have improved things by about 50%- thanks a lot, have a star!

Michael, will have a think about your comments since the reason I went this way to start with was that I couldn't think how easily to do it with queries.

Lawarence, thanks, I had already deleselected these options having rad of the problems they can cause in other posts.

Thanks again. Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....