CODEWITHSUNDEEP
sqlsql-servervbatimems-access-2016
DarthVoid
DarthVoid

Reputation: 636

How can I test the speed of 2 different querying methods in MS Access?

I have a database that is linked to external SQL Server tables. I am playing around with pass-through queries to try and run queries on the server and return the results to my local machine. I'm told that this is faster for tables with tens of millions of rows. However, I want to be able to prove to myself that this is faster than a querying using my local machine. How can I display querying time in MS Access? Or if there exists some other way of seeing which query is faster, I'm all ears.

My code for the pass-through query:

    queryname = "qrypass_AbstrList_QuickInfo"
    Set qdf = CurrentDb.CreateQueryDef(queryname)
    qdf.Connect = "ODBC;DSN=Syteline;Description=Syteline;UID=userID;Trusted_Connection=Yes;DATABASE=ACW_App;LANGUAGE=us_english;"
    qdf.sql = "SELECT LTRIM(RTRIM(lp.lp_num)) as trim_lp, lot.item, lp.loc, lp.whse, lot.lot, lot.qty_on_hand " _
               & "FROM isw_lp As lp inner join isw_lplot as lot on lp.lp_num = lot.lp_num " _
               & "WHERE " & whereclause & ";"

    qdf.ReturnsRecords = True

    DoCmd.OpenReport "rpt_AbstrList_QuickInfo", acViewReport

Upvotes: 1

Views: 566

Answers (3)

Doug Coats
Doug Coats

Reputation: 7107

Try using this to test the timing difference

 Dim StartTime As Double
 Dim SecondsElapsed As Double

 StartTime = Timer

and at end of [what you're testing]

 SecondsElapsed = Round(Timer - StartTime, 2)
 MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

This was a copy/paste from my previous answer here: DAO.Recordset performance

Upvotes: 0

iDevlop
iDevlop

Reputation: 25272

dim t as single
t= timer
'do something you want to measure'
debug.print timer -t

Timer is not very precise (returns seconds), but that's generally enough for me

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

Select the current time before the query, and again after the query.

Upvotes: 0

Related Questions