Code efficiency (2000(SP3))

Topic

New Reply

I have some code that writes to a storage table information about the prevailing language of service for clients in a social services agency. It looks at each client within a prescribed date range (not in the code yet since I have hard-coded the dates in a setup query for testing purposes). For each client, it counts the number of meetings in any given language. If there is a clear majority, it takes the language of service and writes it to the storage table. If there isn’t it cycles through the choices available and chooses the most appropriate one based on some pre-determined criteria.

I have the code working fine, but it is SLOW. It currently takes about 7 minutes to work through the 432 records in my test data. In the field it will have to work through between 1500 and 2000 records in a reporting period. It seems to be particularly slow in the line bolded where there is a new recordset created. I tried fooling around with querydefs and using copyquerydef in a function as outlined in DAO help, but it doesn’t save an awful lot of time. This is the version of code before the querydef attempt. Various bist of code like Dims and error/exit handling have been omitted.

Set dbs = CurrentDb()
strSQLClnt = “SELECT DISTINCTROW qryLangPrevailFinal.ISAPClient, Max(qryLangPrevailFinal.Cnt) ” & _
“AS MaxOfCnt ” & _
“FROM qryLangPrevailFinal ” & _
“GROUP BY qryLangPrevailFinal.ISAPClient;”

Set rstClient = dbs.OpenRecordset(strSQLClnt, dbOpenDynaset)
rstClient.MoveLast
rstClient.MoveFirst

‘start the loop
Do While Not rstClient.EOF

With rstClient
strID = rstClient.Fields(“ISAPClient”).Value
‘get the higest count from the current client
rc = rstClient.Fields(“maxOfCnt”).Value

‘select all the records that have that count
strSQL = “SELECT qryLangPrevailSetup.ISAPClient, qryLangPrevailSetup.Lang, ” & _
“Count(qryLangPrevailSetup.ISAPMeetingID) AS Cnt ” & _
“FROM qryLangPrevailSetup ” & _
“GROUP BY qryLangPrevailSetup.ISAPClient, qryLangPrevailSetup.Lang ” & _

Set rstIn = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Set rstOut = dbs.OpenRecordset(“tblPrevail”, dbOpenDynaset)
If rstIn.BOF = False Then
rstIn.MoveLast
rstIn.MoveFirst
intCount = rstIn.RecordCount
End If
‘see how many there are
If intCount = 1 Then
strLang = rstIn.Fields(“Lang”).Value
*************
Large chunk of code that makes choices when the count is > 1 removed.
*************
End If

With rstOut
.AddNew
![ClientID] = strID
![PrevailLang] = strLang
.Update

End With
.MoveNext
End With
Loop

Reply | Quote

Replies

You should use stored queries where possible. Without seeing the database, it’s hard to be more specific.

Reply | Quote

I realized after I saw your response that I omitted the most important line of the SQL:
“HAVING qryLangPrevailSetup.ISAPClient='” & strID & “‘ AND Count(qryLangPrevailSetup.ISAPMeetingID)=” & rc & “;”

Without the HAVING line, this actually is also stored as a query: qryLangPrevailFinal is there a way to specify these parameters in dbs.OpenRecordset(“qryLangFinal”, dbOpenDynaset) ? This is what I need to do.

Thanks for clearing up my misconception that stored queries were slower.

Reply | Quote

The Jet Engine (the database engine that powers Access) optimizes queries the first time they are run. This also applies to SQL statements used in the record source of a form or report, or in the row source of a list box or combo box. But it obviously can’t optimize SQL statements that are assembled on the fly in VBA code, so those will run less efficiently. In many situations you won’t notice the difference, but when things get complicated, as here, you will.

Reply | Quote

Which brings me back to the previous question, what is the best way to feed the parameters (rc and strID in the VBA) to a stored query if I am looping through the records in code? Is it possible to write them to an unbound textbox on the form and have qryPrevailLangFinal get them from there everytime it is called? or is there a way to specify this in the HAVING clause in the stored query?

Reply | Quote

You can refer to a text box on a form in the criteria line of a query:

[Forms]![frmTest]![txtValue]

However, since DAO (and ADO) don’t “know” about Access forms, you cannot use such a query directly in DAO code. You’d have to specify the parameters in code, which more or less defeats the purpose.

There might be more efficient ways of doing this, but we’d need to see the database to be able to tell.

Reply | Quote

Here’s some sample code to insert values programatically into a the parameters of a ‘stored’ query:

'===============================================================================
'Example query showing how parameters can be passed programatically
'Also showing building a new querydef from an existing query, that needs parameters.
Sub ExampleParameterQuery()
    Dim rs As Recordset
    Dim nCount As Integer
    Dim qd1 As QueryDef
    Dim sQueryName As String    'Query name
    Dim dFrom As Date
    Dim dTo As Date
    Dim sWhere As Variant
    
    sQueryName = "q AuditList"
    dFrom = "1 Oct 2005"
    dTo = "31 Dec 2005"
    

    Set qd1 = CurrentDb().CreateQueryDef("", _
        "SELECT [q AuditList].AgentID, [q AuditList].AgentName " & _
        "FROM [q AuditList] " & _
        "GROUP BY [q AuditList].AgentID, [q AuditList].AgentName;")
    
    qd1.Parameters("[Enter start date]") = Format(CDate(dFrom), "mm/dd/yyyy")
    qd1.Parameters("[Enter end date]") = Format(CDate(dTo), "mm/dd/yyyy")
    
    Set rs = qd1.OpenRecordset(dbOpenDynaset)
    If rs.EOF Then
        nCount = 0
    Else
        rs.MoveLast
        nCount = rs.RecordCount
    End If
    qd1.Close
    rs.Close
    
    'Just display the number of records
    MsgBox nCount
End Sub

Peter

Reply | Quote

Thanks for this Peter and Hans. For the time being I have something that works even if it is slow. This report is only generated twice per year for a government funding agency which has a tendency to change their minds about the information they want. I will put in a message box to tell the supervisor to go have lunch when she runs this report.

Any further work I do on this will be merely out of intellectual curiosity and sheer pigheadedness and I won’t get paid for it. In the meantime, if I get a chance, I will try to post a stripped down version of the database later this week with some fake data since the real data is confidential and you can see what’s what.

Reply | Quote

I got it! Many thanks Peter for pointing me to parameters, which eventually led me to filters which led me to the revised code. Down from 6-7 minutes to about 20 seconds. Now, of course, I have to check that it is working properly but it is a start.

Snip*************

With rstClient
strID = rstClient.Fields(“ISAPClient”).Value
‘get the higest count from the current client
rc = rstClient.Fields(“maxOfCnt”).Value

rstInPrlm.Filter = “[ISAPClient] = ‘” & strID & “‘ AND [cnt]=” & rc

Set rstIn = rstInPrlm.OpenRecordset

Set rstOut = dbs.OpenRecordset(“tblPrevail”, dbOpenDynaset)

Snip ********************

Reply | Quote

I agree on the database issue; it seems to me to be a database issue rather than an Excel issue.
To start with, check that any GROUP BY and ORDER BY fields are indexed. Also look at the underlying queries – any joins also need indexes for speed.

Reply | Quote

I’m not sure what you mean by Excel as that does not enter into it at all.

How do you index a join?

Reply | Quote

You don’t index the join, you index the field that the join ‘links’ to.
If you’re using Access QBE to create the query, then it’s the field that either has the ‘arrow’ pointing to it, or if you have set up the relationships in the database, the ‘many’ or ‘infinity’ field of the link.
If you’re not using Access, then think of the ‘secondary’ table; it’s the one that the main table is ‘looking up’. It’s the field in the secondary table that needs to be indexed. This can make a huge performance difference.
If the database is in Access (or if you can it into Access) and not too complicated (or if you create a cut-down version of it), then the Performance Analyzer can be useful (Tools, Analyze, Performance).

All of the above issues would apply and will affect performance whether you are using Excel to analyze the data, or some other tool. That’s what I meant by it’s not an Excel issue.

Peter

Reply | Quote