Improve query performance - copy and sum

[solidfish]

Hello,

I was searching for some help trying to improve the performance of VBA function in access. I have two functions that are taking 40+ minutes to complete against a table that has 300,000+ records. I'm running on a quad i5 processor laptop.

I'm not sure if my query can be improved, or if this is expected.

My first function loops through the table and for each record, queries a lookup table for some mapping data and updates the first table. I had to reset the dbMaxLocksPerFile to make this work since the table has so many records. The code looks like: (for brevity sake I only show the major logic flow)

...
sql = "SELECT * FROM " & stagingTable
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

On Error GoTo Handler
DAO.DBEngine.SetOption dbMaxLocksPerFile, 750000 ' temporary increase lock count for large aetna

Do While Not rs.EOF
  ...
  tsql = "SELECT [Plan Code],[Status] FROM " & mapTable & " WHERE [Plan Code] = " & planCode 
  ...
  Set rs2 = db.OpenRecordset(tsql, dbOpenDynaset)
  If Not rs2.EOF Then
     result = rs2![Status]
  End If
  rs2.Close
  ...
  rs.Edit
  rs![Validated] = result
  rs![error] = message
  rs.Update
        
ResumeLoop:
  rs.MoveNext
Loop
rs.Close
db.Close
...

This next function also takes 40+ minutes to run. Here, I need to back through the first table and run a sum of sub-totals. I insert these results into a second table. The condensed code looks like:

...
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
sql = "SELECT [Division],[Plan Code],[Status],Sum([Claim]) AS [SumOfClaim] FROM stagingTable " _
& "WHERE ((([Recorded Date]) Between #" & startDate & "# And #" & endDate & "#)) GROUP BY [Division],[Plan Code],[Status];"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
...
DoCmd.SetWarnings False
Do While Not rs.EOF
  ...
  tsql = "INSERT INTO " & outputTable & " (" & outputColumns & ") VALUES (" _
   & Format(startDate, "\#yyyy-mm-dd\#") & ",'" 
   & rs![Division] & "','" _
   & rs![Plan Code] & "','" _
   & rs![Status] & "','" _
   & rs![SumOfClaim] & "');"
   ...
   DoCmd.RunSQL (tsql)
   ...
ResumeLoop:
   rs.MoveNext
Loop
rs.Close
db.Close
...

Would anyone see anything obvious that could improve the performance of this please?

Thank you

 

[arnelgp]

on your first function does the variable [plancode] changes or is it the same the entire loop?
the second function is really simple:
docmd.runsql "INSERT INTO " & outputTable & " (" & outputColumns & ") SELECT [Division],[Plan Code],[Status],Sum([Claim]) AS [SumOfClaim] FROM stagingTable " _
& "WHERE ((([Recorded Date]) Between #" & startDate & "# And #" & endDate & "#)) GROUP BY [Division],[Plan Code],[Status];"

 

[solidfish]

Yes in the first function the planCode comes from the first query. It could look something like rs![Plan Code]

As for the second function, are you saying I should remove the DoWhile and do the single INSERT statement?

Many thanks for your help!

 

[arnelgp]

yes remove the do while loop and instead use action query.

on the second function i think:

DoCmd.RunSQL "UPDATE " & stagingTable & " AS T1 INNER JOIN " & myTable & " AS T2 ON T1.[PlanCode] = T2.[PlanCode] SET T1.[VALIDATED] = T2.[Status], T1.[Error] = T2.[[COLOR=Blue]what field is message[/COLOR]];"

replace the blue text with appropriate field name of myTable.

 

[solidfish]

Ok I see what you are doing. I'm trying this out now. One more quick question on that SUM function please.

So the table I am inserting into has a field that does not come from the second table. Here is the complete sql i have:

"INSERT INTO " & outputTable ([Date],[Carrier],[Division],[Plan Code],[Status],[Paid Claim]) SELECT [Division],[Plan Code],[Status],Sum([Claim]) AS [SumOfClaim] FROM " & stagingTable _
& "WHERE ((([Recorded Date]) Between #" & startDate & "# And #" & endDate & "#)) GROUP BY [Division],[Plan Code],[Status];"

I need to add Today's date in the date column and another string value for [Carrier]. For example "ABC". This doesnt come from any of the tables (comes from form). Is there a way to insert these as part of the sql?

Thanks agian!

 

[solidfish]

Perhaps this will make the question more clear - in the following SQL, the [Date] and [Carrier] values need to come from another variable, not the 'stagingTable'. Is it possible to insert those variables into this query?

sql = "INSERT INTO " & outputTable ([Date],[Carrier],[Division],[PCode],[Status],[Total]) 
SELECT [Division],[PCode],[Status],Sum([Claim]) AS [SumOfClaim] 
FROM " & stagingTable & "
GROUP BY [Division],[PCode],[Status];"

 

[arnelgp]

sql = "INSERT INTO " & outputTable ([Date],[Carrier],[Division],[PCode],[Status],[Total], [Date], [Carrier])
SELECT [Division],[PCode],[Status],Sum([Claim]) AS [SumOfClaim], Date, '" & Forms!FormName!CarrierControlName & "" & "' " &
FROM " & stagingTable & "
GROUP BY [Division],[PCode],[Status];"