How can I time long running queries?

  • Thread starter Thread starter Conan Kelly
  • Start date Start date
C

Conan Kelly

Hello all,

Is it possible to time long running select queries?

I was looking at creating some code to do this....something like:

1. Store current time in a time variable
2. Try to programmatically open a query ("open" meaning just like manually
opening a select query from MS Access to view data) using
"Application.CurrentDb.QueryDefs("Query3").OpenRecordset dbOpenTable".
3. Check the current time and compare it to the start time saved at the
beginning

But, I get "Run-time error '3219': Invalid operation." when I run
"...OpenRecordset dbOp...". Also, I get the feeling that the OpenRecordset
method is for opening a query in memory/the background (storing in a
Recordset object variable, not for viewing) rather than actually opening a
query to view the data).

Thanks for any help anyone can provide,

Conan Kelly
 
Conan

Would you describe what having the query data will allow you to accomplish?
You've described a "how" (open a query, view the results), but not the
"why". It may be that there are other ways to get done what you're after.

NOTE: "long running queries" may run long for several reasons, among them
the possibility that the join, sort and/or selection fields are not indexed
properly, and the possibility that data from non-local sources (e.g., linked
SQL-Server table) is being subjected to functions that ONLY exist in the
local (Access) application. Do any of these apply in your situation?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Thanks for your feedback.

All of the data is inside this AC DB (no linked tables SQL Server or
external text/XL files). Normally we set up a query to match our XL
templates, open the query, copy-n-paste from AC to XL.

I'd like to keep that manual copy-n-paste procedure (don't want to
automate/program it) so I can see the data.

The reason it is running so slow is because this client sends us this DB.
It is not a.....I forget the term....."standardized" DB: there are 12
tables with the exact same table definitions (number of columns, column
names, data types). I don't want to put all the data in one table because I
want to leave the clients data unchanged and I don't want to duplicate the
data (taking up more disk space)......so I have these 12 tables UNION ALL'ed
together in a query (as well as 0's converted to nulls using Iif() in the
last 37 of 44 columns). The recordset of this UNION ALL query is 1.58 mil
records.

Then I use the 1.58-mil-records UNION ALL query in another query to look up
an order # and category (separate lookup tables).

Then I use this "Order & Cat" query in a group by select query to summarize
data into 14 categories/order #'s, summing or counting the last 37 of 44
columns, turning 1.58 mil rows into 57.

It is from this 57-row summarized query that I copy-n-paste into XL.

I'd like to keep this process the same, I was just curious as to how long it
was taking for these queries to run and I didn't want to sit here and
twiddle my thumbs, watching my watch, waiting for it finish. It is maybe
something like 5-10 mins.

Thanks again for all of your help,

Conan
 
You didn't mention whether there are any indexes. It might be worth a
look...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
sorry...there are no indexes. Also, I don't quite understand how to use
them/why they speed things up...so I don't really use them too much (in both
AC & SQL Server).

But we haven't had much of a need for them either. Most of our DB's are
just snapshots of clients data. The are not live DB's that have many users
in them looking up/changing data. Usually there is one person in them
(usually me) that just runs queries and copies results to XL files. And
they get bulk inserts as often as every 3 months. Everything we have runs
pretty quickly, so I'm not too concerned with speed. There are just a
handfull that take longer than 5 mins to run, and if they take much longer,
I find ways to speed them up (i.e. make temp tables out of query/view
results, then drop them when I'm done.)

Thanks again,

Conan
 
Hi -

Not sure where I got this or I'd give the author credit. It works!

Option Compare Database
Option Explicit

Private Declare Function a2Ku_apigettime Lib "winmm.dll" _
Alias "timeGetTime" () As Long
Dim lngstartingtime As Long

Sub a2kuStartClock()
lngstartingtime = a2Ku_apigettime()
End Sub
Function a2kuEndClock()
a2kuEndClock = a2Ku_apigettime() - lngstartingtime
End Function


Function QueryTimer(strQueryName As String)

Dim db As Database
Dim qry As QueryDef
Dim rs As Recordset

Set db = CurrentDb
Set qry = db.QueryDefs(strQueryName)

'Start the clock
a2kuStartClock
Set rs = qry.OpenRecordset()

'Stop the clock and print the results to the debug window
Debug.Print strQueryName & " executed in: " & a2kuEndClock & _
" milliseconds"

rs.Close
db.Close
Set db = Nothing

End Function


Conan said:
sorry...there are no indexes. Also, I don't quite understand how to use
them/why they speed things up...so I don't really use them too much (in both
AC & SQL Server).

But we haven't had much of a need for them either. Most of our DB's are
just snapshots of clients data. The are not live DB's that have many users
in them looking up/changing data. Usually there is one person in them
(usually me) that just runs queries and copies results to XL files. And
they get bulk inserts as often as every 3 months. Everything we have runs
pretty quickly, so I'm not too concerned with speed. There are just a
handfull that take longer than 5 mins to run, and if they take much longer,
I find ways to speed them up (i.e. make temp tables out of query/view
results, then drop them when I'm done.)

Thanks again,

Conan
You didn't mention whether there are any indexes. It might be worth a
look...
Click to expand...
[quoted text clipped - 86 lines]
Click to expand...
 
raskew,

Thanks for the code. That will do exactly what I want.

Thanks again,

Conan




raskew via AccessMonster.com said:
Hi -

Not sure where I got this or I'd give the author credit. It works!

Option Compare Database
Option Explicit

Private Declare Function a2Ku_apigettime Lib "winmm.dll" _
Alias "timeGetTime" () As Long
Dim lngstartingtime As Long

Sub a2kuStartClock()
lngstartingtime = a2Ku_apigettime()
End Sub
Function a2kuEndClock()
a2kuEndClock = a2Ku_apigettime() - lngstartingtime
End Function


Function QueryTimer(strQueryName As String)

Dim db As Database
Dim qry As QueryDef
Dim rs As Recordset

Set db = CurrentDb
Set qry = db.QueryDefs(strQueryName)

'Start the clock
a2kuStartClock
Set rs = qry.OpenRecordset()

'Stop the clock and print the results to the debug window
Debug.Print strQueryName & " executed in: " & a2kuEndClock & _
" milliseconds"

rs.Close
db.Close
Set db = Nothing

End Function


Conan said:
sorry...there are no indexes. Also, I don't quite understand how to use
them/why they speed things up...so I don't really use them too much (in
both
AC & SQL Server).

But we haven't had much of a need for them either. Most of our DB's are
just snapshots of clients data. The are not live DB's that have many
users
in them looking up/changing data. Usually there is one person in them
(usually me) that just runs queries and copies results to XL files. And
they get bulk inserts as often as every 3 months. Everything we have runs
pretty quickly, so I'm not too concerned with speed. There are just a
handfull that take longer than 5 mins to run, and if they take much
longer,
I find ways to speed them up (i.e. make temp tables out of query/view
results, then drop them when I'm done.)

Thanks again,

Conan
You didn't mention whether there are any indexes. It might be worth a
look...
Click to expand...
[quoted text clipped - 86 lines]
Conan Kelly
Click to expand...
Click to expand...
Click to expand...
 
Conan

Usually, a field that gets used for joining, for sorting, and/or for
selecting on can be indexed for faster query performance. Of course there
are exceptions, and indexing is part art, part science... you usually have
to just try to see if it works better.


Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Thanks for the feedback.

I'll try to keep this in mind the next time I run into long running queries.

Thanks again,

Conan
 
Back
Top