A
Andy
Is there anyway to time how long a query takes to run?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jerry Whittle
Call me low-tech, but I just use a stopwatch.
A
Andy
Yes but i am running queries overnight whilst servers are quite and need to
assess the impact of running them during the daytime.
assess the impact of running them during the daytime.
J
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
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
A
Andy
I am just running via a macro and using Windows Task Scheduler to initiate
the command
the command
J
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
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
J
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
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
A
Andy
Will take a look, cheers
John Spencer said: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