Timing of Access Queries

[RogerC]

I find some lengthy queries on large tables take so long that I end up leaving the PC on overnight. Does anyone know a way of timing how long Access actually takes to run a query? (Other than stopwatches etc

 

[Pat Hartman]

You could run the query from a form and display the time when it starts and then display the time again when it ends.

Have you looked into trying to improve the performance of the query?

 

[RogerC]

Well the Performance Analyzer seems to be happy with the query, I just think it's the size of the tables involved. (1.89m records in one of the tables.)
I must confess to not using forms much. I'm aware that they have time features but have never ran a query from one. Is it a straightforward process to run a qery from a form and stamp the start and finish time someware?

 

[Pat Hartman]

Create a new unbound form and add two text boxes. One to hold start time and one to hold end time. And optionally a third to show the calculated elapsed time.

Create a new function in a new or existing code module. Write the code to display the current date/time in the first box, then execute the query, then display the current date/time again in the second box. Calculate the elapsed time and display it in the third box.

 

[David R]

Pat, or anyone who's done this:

Do you remember how you accomplished this with queries? I always get a result of 0.00E+00, presumably because it is writing the second time immediately after starting the query, not when it finishes.

It made no difference that my code was behind the unbound form itself, and not in a separate module. Here's what I attempted:

Private Sub Command4_Click()
Dim st As Single, nd As Single

    Dim stDocName As String

    stDocName = "query1"
    st = Now()
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    nd = Now()
    Me.Text0 = nd - st
End Sub

I just tried something I found in another post, but it gave identical results as well:

Dim stDocName As String

    stDocName = "query1"
    Debug.Print "Start: " & Now()
    DoCmd.OpenQuery stDocName
    Debug.Print "End: " & Now()

Is this because the query is simply running too fast? I can't fathom that it is; there are over a million dummy records in this table, and while it takes Access no time to calculate the result, the "Record Count" does take a while to show up at the bottom. Rushmore query strategy?

I guess what I need is a way to stop the second Now() statement from running until the Query has finished.

 

[Pat Hartman]

Does the same thing happen if you run the query from a macro? The macro could populate the start time text box, run the query, then populate the end time text box.

I use the following piece of code to run an automated overnight batch process. We needed a way to log any errors that happened without leaving the database hanging so I created a log file. The time the process starts is saved, and at the end of the process the finish time is recorded and a record added to the log. The method might work for you. The function is run from the Open event of a form. The form is specified as the start up form in the start up options. The database is opened by a batch file that runs on a scheduled basis on one of our production servers so it was important that the job "finish" cleanly without operator intervention.

Function fDailyBatch()
On Error GoTo fDailyBatch_Err
    Dim db As Database
    Dim TD1 As TableDef
    Dim QD1 As QueryDef
    Dim RS1 As Recordset

    Set db = CurrentDb()
    Set TD1 = db.TableDefs!tblLogBatchRuns
    Set RS1 = TD1.OpenRecordset
    RS1.AddNew
    RS1!RunStartTime = Now()
    RS1!Process = "Daily"
    
    DoCmd.SetWarnings False
    'Forms!frmRunBatch!txtStepName = "1. Delete NotesDownload"
    DoCmd.OpenQuery "qDeleteNotesDownload", acNormal, acEdit
    'Forms!frmRunBatch!txtStepName = "2. Import Notes text file"
    DoCmd.TransferText acImportDelim, "ERCfile Import Specification", "dbo_NotesDownload", "C:\Hartman\ERC\ERCfile.txt", False, ""
    'Forms!frmRunBatch!txtStepName = "3. Update people Ids in DWDownload"
    DoCmd.OpenQuery "qUpdatePeopleIds", acNormal, acEdit
    'Forms!frmRunBatch!txtStepName = "4. Add new rows to Cont/Exh"
    DoCmd.OpenQuery "qJoinNotesToDWAppendContExh", acNormal, acEdit
    'Forms!frmRunBatch!txtStepName = "5. Delete CIS Company"
    DoCmd.OpenQuery "qDeleteCISCompany", acNormal, acEdit
    'Forms!frmRunBatch!txtStepName = "6. Append CIS Company"
    DoCmd.OpenQuery "qAppendCISCompany", acNormal, acEdit
    'Forms!frmRunBatch!txtStepName = "7. Add new rows to LOB Group"
    DoCmd.OpenQuery "qJoinNotesToDWAppendLOBGroup", acNormal, acEdit
    'Forms!frmRunBatch!txtStepName = "8. Add new rows to LOB Detail"
    DoCmd.OpenQuery "qJoinNotesToDWAppendLOBDetail", acNormal, acEdit
    'Forms!frmRunBatch!txtStepName = "9. Delete email ToDo"
    DoCmd.OpenQuery "qDeleteToDo", acNormal, acEdit
    'Forms!frmRunBatch!txtStepName = "10. Rebuild email ToDo"
    DoCmd.OpenQuery "qExtractToDo", acNormal, acEdit
    DoCmd.SetWarnings True


fDailyBatch_Exit:
    RS1!RunEndTime = Now()
    RS1.Update
    
    Exit Function

fDailyBatch_Err:
    'MsgBox Err.Number & " - " & Err.Description
    RS1!ErrNum = Err.Number
    RS1!errDesc = Err.Description
    Resume fDailyBatch_Exit

End Function