Speeding Up A Query

**PianoMan64** · Nov 12 '08, 10:33 AM

Originally posted by glenfernandez

Hi All!

This is my first post although I have been visiting this forum for help with my Access development work. I have been tasked with preparing a Financial Reporting Tool using MS Access 2003 for a financial institution.

This is my first exposure to Access and VBA but I have gor a rudimentary model up and running. However, as I am running many queries in sequence the final report takes around 30 secs to generate. I was just wondering if my codes could be improved to make them run faster.

I run a series of 10 queries in sequence and then purll them together in two joining queries at the end to get my results. An example of one of the 10 smaller queries is as follows:

Code:

Set qdf = db.QueryDefs("Qry1")
       
   strMonth = "" & Me.ComboMonth.Value & ""
   strYear = "" & Me.ComboYear.Value & ""
               
   If IsNull(Me.ComboMISOrg.Value) Then
    strMIS = " Like '*'"
   Else
    strMIS = "='" & Me.ComboMISOrg.Value & "'"
   End If
   
strSQL = "SELECT tblBudgetLine.[BUDGET LINE] AS Category, Nz(Round(Sum(tblTransactions.Amount),2),0) AS Actual " & vbCrLf & _
"FROM ((tblBudgetLine LEFT JOIN tblGLLine ON tblBudgetLine.[BUDGET LINE ACCT]=tblGLLine.[BUDGET LINE ACCT]) LEFT JOIN tblTransactions ON tblGLLine.GL_Code=tblTransactions.GL_Code   " & vbCrLf & _
"And (tblTransactions.MIS_Org)" & strMIS & "And (tblTransactions.Year)=" & strYear & ") LEFT JOIN tblMonth ON tblTransactions.Month=tblMonth.Month " & vbCrLf & _
"WHERE ((tblMonth.Sequence)=" & strMonth & " And ((tblGLLine.GL_Code)=tblBudgetLine.[BUDGET LINE ACCT]And (tblGLLine.GL_Code)=(tblGLLine.GL_Code)) And ((tblBudgetLine.[BUDGET LINE ACCT])=tblGLLine.GL_Code)) Or ((tblTransactions.GL_Code) Is Null) " & vbCrLf & _
"GROUP BY tblBudgetLine.[BUDGET LINE], tblBudgetLine.[BUDGET SEQ];"
 
qdf.SQL = strSQL
   
DoCmd.Close acQuery, "Qry1"

This is followed by another 9 similar queries, each pulling different information for each column in the final report.

The final report is then put together and output using the following:

Code:

BeginTrans

Set qdf = db.QueryDefs("GOT By MIS Org")

strMonth = "" & Me.ComboMonth.Value & ""

strSQL = "SELECT tblExpenseLine.[EXPENSE CATEGORY] AS [Expense Category], tblExpenseLine.[TOE TYPE] as [TOE TYPE], tblExpenseLine.Type AS TYPE, tblBudgetLine.[BUDGET SEQ] AS SEQ, tblBudgetLine.Priority AS PRIORITY, tblBudgetLine.[BUDGET LINE] AS Category," & _
"FORMAT((Qry1.Actual),""#0.00"") AS Actual, FORMAT((Qry2.OP),""#0.00"") AS OP, FORMAT((OP-Actual),""#0.00"") AS Variance,(IIf([OP]<='0.00',""N.A"",Round(((Variance/OP)*100),0)&""%""))AS Var, FORMAT((Qry3.[Last Year]),""#0.00"") AS [Last Year]," & _
"FORMAT((Qry4.[Actual YTD]),""#0.00"") AS [Actual YTD], FORMAT((Qry5.[OP YTD]),""#0.00"") AS [OP YTD], FORMAT([OP YTD]-[Actual YTD],""#0.00"") AS [Variance YTD], (IIf([OP YTD]<='0.00',""N.A"",Round((([Variance YTD]/[OP YTD])*100),0)&""%"")) AS [Var YTD], FORMAT((Qry6.[Last Year YTD]),""#0.00"") AS [Last Year YTD]," & vbCrLf & _
"FORMAT((Qry7.[2008 Actuals]),""#0.00"") AS [2008 Actuals], FORMAT(Nz(([Actual]*(12-" & strMonth & ")+[Actual YTD]),2),""#0.00"") As [2008 Run Rate],FORMAT((Qry8.[2008 OP]),""#0.00"") AS [2008 OP], FORMAT(ROUND(-1*([2008 Run Rate]-[2008 OP]),2),""#0.00"") AS [Run Rate - OP (2008)], FORMAT((Qry9.[2006 Actuals]),""#0.00"") AS [2006 Actuals],FORMAT((Qry10.[2007 Actuals]),""#0.00"") AS [2007 Actuals] " & _
"FROM (((((((((((tblBudgetLine LEFT JOIN tblExpenseLine ON tblBudgetLine.[BUDGET SEQ]=tblExpenseLine.[BUDGET SEQ]) LEFT JOIN tblGLLine ON tblBudgetLine.[BUDGET LINE ACCT]=tblGLLine.[BUDGET LINE ACCT])LEFT JOIN Qry1 ON tblBudgetLine.[BUDGET LINE]=Qry1.Category) LEFT JOIN Qry2 ON tblBudgetLine.[BUDGET LINE]=Qry2.Category) LEFT JOIN Qry3 ON tblBudgetLine.[BUDGET LINE]=Qry3.Category) " & _
"LEFT JOIN Qry4 ON tblBudgetLine.[BUDGET LINE]=Qry4.Category) LEFT JOIN Qry5 ON tblBudgetLine.[BUDGET LINE]=Qry5.Category) LEFT JOIN Qry6 ON tblBudgetLine.[BUDGET LINE]=Qry6.Category) " & vbCrLf & _
"LEFT JOIN Qry7 ON tblBudgetline.[BUDGET LINE]=Qry7.Category) LEFT JOIN Qry8 ON tblBudgetLine.[BUDGET LINE]=Qry8.Category) LEFT JOIN Qry9 ON tblBudgetLine.[BUDGET LINE]=Qry9.Category) LEFT JOIN Qry10 ON tblBudgetLine.[BUDGET LINE]=Qry10.Category " & _
"WHERE tblExpenseLine.[EXPENSE CATEGORY] <> 'Headcount'GROUP BY tblExpenseLine.[EXPENSE CATEGORY], tblExpenseLine.[TOE TYPE], tblExpenseLine.Type, tblBudgetLine.[BUDGET LINE], tblBUdgetLine.[Priority], tblBudgetLine.[BUDGET SEQ], Actual, OP, [Last Year], [Actual YTD], " & _
"[OP YTD], [Last Year YTD], [2008 Actuals], [2008 OP], [2006 Actuals], [2007 Actuals];"
 
 qdf.SQL = strSQL
   
DoCmd.Close acQuery, "GOT By MIS Org"
   
   DoCmd.Echo False
   If Application.SysCmd(acSysCmdGetObjectState, acQuery, "GOT By MIS Org") = acObjStateOpen Then
      DoCmd.Close acQuery, "GOT By MIS Org"
   End If
   
 Set qdf = db.QueryDefs("QryHeadcountMIS")

strMonth = "" & Me.ComboMonth.Value & ""

strSQL = "SELECT tblExpenseLine.[EXPENSE CATEGORY] AS [Expense Category], tblExpenseLine.[TOE TYPE] as [TOE TYPE], tblExpenseLine.Type AS TYPE, tblBudgetLine.[BUDGET SEQ] AS SEQ, tblBudgetLine.Priority AS PRIORITY, tblBudgetLine.[BUDGET LINE] AS Category," & _
"FORMAT((Qry1.Actual),""#0"") AS Actual, FORMAT((Qry2.OP),""#0"") AS OP, FORMAT((OP-Actual),""#0"") AS Variance,(IIf([OP]<='0',""N.A"",Round(((Variance/OP)*100),0)&""%""))AS Var, FORMAT((Qry3.[Last Year]),""#0"") AS [Last Year]," & _
"FORMAT((Qry1.[Actual]),""#0"") AS [Actual YTD], FORMAT((Qry2.[OP]),""#0"") AS [OP YTD], FORMAT([OP YTD]-[Actual YTD],""#0"") AS [Variance YTD], (IIf([OP YTD]<='0',""N.A"",Round((([Variance YTD]/[OP YTD])*100),0)&""%"")) AS [Var YTD], FORMAT((Qry3.[Last Year]),""#0"") AS [Last Year YTD]," & vbCrLf & _
"FORMAT((Qry7.[2008 Actuals]),""#0"") AS [2008 Actuals], FORMAT(Nz(([Actual]*(12-" & strMonth & ")+[Actual YTD]),2),""#0"") As [2008 Run Rate],FORMAT((Qry8.[2008 OP]),""#0"") AS [2008 OP], FORMAT(ROUND(-1*([2008 Run Rate]-[2008 OP]),2),""#0"") AS [Run Rate - OP (2008)], FORMAT((Qry9.[2006 Actuals]),""#0"") AS [2006 Actuals],FORMAT((Qry10.[2007 Actuals]),""#0"") AS [2007 Actuals] " & _
"FROM (((((((((((tblBudgetLine LEFT JOIN tblExpenseLine ON tblBudgetLine.[BUDGET SEQ]=tblExpenseLine.[BUDGET SEQ]) LEFT JOIN tblGLLine ON tblBudgetLine.[BUDGET LINE ACCT]=tblGLLine.[BUDGET LINE ACCT])LEFT JOIN Qry1 ON tblBudgetLine.[BUDGET LINE]=Qry1.Category) LEFT JOIN Qry2 ON tblBudgetLine.[BUDGET LINE]=Qry2.Category) LEFT JOIN Qry3 ON tblBudgetLine.[BUDGET LINE]=Qry3.Category) " & _
"LEFT JOIN Qry4 ON tblBudgetLine.[BUDGET LINE]=Qry4.Category) LEFT JOIN Qry5 ON tblBudgetLine.[BUDGET LINE]=Qry5.Category) LEFT JOIN Qry6 ON tblBudgetLine.[BUDGET LINE]=Qry6.Category) " & vbCrLf & _
"LEFT JOIN Qry7 ON tblBudgetline.[BUDGET LINE]=Qry7.Category) LEFT JOIN Qry8 ON tblBudgetLine.[BUDGET LINE]=Qry8.Category) LEFT JOIN Qry9 ON tblBudgetLine.[BUDGET LINE]=Qry9.Category) LEFT JOIN Qry10 ON tblBudgetLine.[BUDGET LINE]=Qry10.Category " & _
"WHERE tblExpenseLine.[EXPENSE CATEGORY] = 'Headcount' GROUP BY tblExpenseLine.[EXPENSE CATEGORY], tblExpenseLine.[TOE TYPE], tblExpenseLine.Type, tblBudgetLine.[BUDGET LINE], tblBUdgetLine.[Priority], tblBudgetLine.[BUDGET SEQ], Actual, OP, [Last Year], [Actual YTD], " & _
"[OP YTD], [Last Year YTD], [2008 Actuals], [2008 OP], [2006 Actuals], [2007 Actuals];"
 
 qdf.SQL = strSQL
   
   DoCmd.Echo False
   If Application.SysCmd(acSysCmdGetObjectState, acQuery, "QryHeadcountMIS") = acObjStateOpen Then
      DoCmd.Close acQuery, "QryHeadcountMIS"
   End If
   
   CommitTrans
       
   'DoCmd.OpenReport "GOT By MIS Org", acViewPreview
   DoCmd.OutputTo acOutputReport, "GOT By MIS Org", _
   acFormatSNP, , True

If anybody can spot bottle necks or redundancies in my code, I would appreciate it if you could help point them out to me. Similarly, if there is a more efficient way of running my queries, please do help me improve my coding style by sharing with me.

My problem is not urgent (since it is already working) but I am just hoping to improve and learn by sharing my mistakes. Please forgive my primitive coding as this has been my first attempt at coding in VBA.

I thank everyone who reads this in advance for your time :)

Cheers!

Glen

Hello glenfernandez, Welcome to Bytes:

After looking over your code, there are a few things that are definately giving you a performance hit.

1. Using Format Functions within the SQL String. For each call, that takes about 1/8th of a sec for each one roughly. You would be better off to format them inside a actual query and save them. This would cut down on your clutter of your queries.
2. I can't tell if any of the tables that you're pulling data from, that you're linking or joining with have index values, this would be a BIG time comsumer if you don't have those indexed. (Think of it like this, If your files are a mess and everything is everywhere. It take a great deal longer to find stuff, right?
Same here with indexes. If you index each of the values that you're linking, and/or joining with, than would make it super easy for Jet Engine to find each record, or group of records to do your calculations.

Other than that, looks good.

Hope that helps some,

Joe P.

**glenfernandez** · Nov 14 '08, 01:42 AM

HI Joe

Thank you for your kind reply.

I have tried your suggestions with the folowing outcomes:

1. Need to clarify that all the queries I am running are being done on the fly, using a form to dynamically pass the query parameters through. I tried doing the formatting in the 10 sub-queries, but I this formatting doesnt get pulled through to the main query. I even checked my table formats and the data is formatted correctly. It seems the only way I can format them (given my limited knowledge) is at the final query that pulls all the sub-queries together.

If you know a better way please post a code snippet for my guidance - Im not quite seasoned enough to figure out how to do things without a little code hint - just a line or two to point me in the right direction if thats not too much trouble.

2. As for index referencing, if you mean setting the correct Primary/Foreign key relationships and specifying joins between tables, then yes I have done this (correctly, I hope).

Im running this report across some 300,000+ records and cross-referencing between some 8 tables. So, maybe 26secs isnt such a bad time? Afterall, it includes formatting for the report as well.

Well, again, thank you in advance for any insights you could share with me.

Cheers!

Glen

Originally posted by PianoMan64

Hello glenfernandez, Welcome to Bytes:

After looking over your code, there are a few things that are definately giving you a performance hit.

1. Using Format Functions within the SQL String. For each call, that takes about 1/8th of a sec for each one roughly. You would be better off to format them inside a actual query and save them. This would cut down on your clutter of your queries.
2. I can't tell if any of the tables that you're pulling data from, that you're linking or joining with have index values, this would be a BIG time comsumer if you don't have those indexed. (Think of it like this, If your files are a mess and everything is everywhere. It take a great deal longer to find stuff, right?
Same here with indexes. If you index each of the values that you're linking, and/or joining with, than would make it super easy for Jet Engine to find each record, or group of records to do your calculations.

Other than that, looks good.

Hope that helps some,

Joe P.

**glenfernandez** · Nov 14 '08, 05:38 AM

Hi Joe,

Just an update. Checked my indexing (figured it out after playing with the db) and indexed a few more fields.

Queries are running under 10secs now.

Your idea was brilliant and I learnt something invaluable. Thanks very much!

Glen

Speeding Up A Query

Speeding Up A Query

Comment

Comment

Comment