Time a query

[Andy]

Is there anyway to time how long a query takes to run?

 

[Jerry Whittle]

Call me low-tech, but I just use a stopwatch.

 

[Andy]

Yes but i am running queries overnight whilst servers are quite and need to
assess the impact of running them during the daytime.

 

[John Spencer]

What method are you using to run the queries?

Macros?
VBA code?

If you are executing the queries in sequence using VBA code, you can get the
time before you execute the query and the time after the query finishes
executing and then do something with the elapsed time difference - store it in
a table, print it to a text file, ...

The time at night may be significantly different then running them during the
day - network resources being busy.

Are you running these queries against a JET database (Access's normal backend)
or against MS SQL Server database or against some other database? If you are
running against MS SQL database, you might find that using a pass-through
query or a stored procedure would help performance significantly.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Andy]

I am just running via a macro and using Windows Task Scheduler to initiate
the command

 

[John Spencer]

That makes it tough to determine the elapsed time of each query or even all
the queries.

Would you consider using a macro to run a function that executes the queries?
If so you might try the following UNTESTED solution.

You add a table to your database name named QueryResults with 4 fields:
qName - text 64 to hold the query name
qStart - DateTime
qEnd - DateTime
RecCount - the number of records affected by the query.

Then you could have a module that looked like the following and have your
macro RunCode with a function name of fRunQueryList.

Option Compare Database
Option Explicit

Dim dbAny As DAO.Database
Dim rst As DAO.Recordset

Public Function fRunQueryList()

Set dbAny = CurrentDb()
rst = dbAny.OpenRecordset("SELECT QName,qStart,qEnd, RecCount" & _
" FROM QueryResults WHERE False")

fRunQuery "QueryOne"
fRunQuery "QueryTwo"
fRunQuery "QueryThree"

End Function

Private Sub fRunQuery(strQName As String)
Dim StartTime As Date
Dim EndTime As Date
Dim lCount As Long

StartTime = Now()
dbAny.Execute strQName, dbFailOnError
EndTime = Now()
lCount = dbAny.RecordsAffected

rst.AddNew
rst!QName = strQName
rst!QStart = StartTime
rst!qEnd = EndTime
rst!RecCount = lCount
rst.Update

End Sub



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Whoops, just noticed at least one error in the code, the line after
Set dbAny = CurrentDb()
Should read
SET rst = dbAny.OpenRecordset("SELECT QName,qStart,qEnd, RecCount" & _
" FROM QueryResults WHERE False")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Andy]

Will take a look, cheers

Whoops, just noticed at least one error in the code, the line after
Set dbAny = CurrentDb()
Should read
SET rst = dbAny.OpenRecordset("SELECT QName,qStart,qEnd, RecCount" & _
" FROM QueryResults WHERE False")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County