Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code efficiency (2000(SP3))

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code efficiency (2000(SP3))

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

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Code efficiency (2000(SP3))

    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.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code efficiency (2000(SP3))

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

    How do you index a join?

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code efficiency (2000(SP3))

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code efficiency (2000(SP3))

    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.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code efficiency (2000(SP3))

    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?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code efficiency (2000(SP3))

    You can refer to a text box on a form in the criteria line of a query:
    <code>
    [Forms]![frmTest]![txtValue]
    </code>
    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.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Code efficiency (2000(SP3))

    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

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Code efficiency (2000(SP3))

    Here's some sample code to insert values programatically into a the parameters of a 'stored' query:
    <pre>'============================================ ===================================
    '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
    </pre>


    Peter

  11. #11
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code efficiency (2000(SP3))

    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.

  12. #12
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code efficiency (2000(SP3))

    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 ********************

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •