Timing performance

[Stevie]

I'm would like to time how long two or three different methods return records from SQL statements.

Can anybody tell me how I'd go about this?

[Iain17]

No doubt i will get flamed for the use of the timer function, as everyone seems to insist it is not accurate enough, and they use the API.

But in my humble opinion, most of the people on this site seem to have forgotten the number 1 rule of programming.

Keep it simple whenever possible.

    Dim sngTime As Single
    
    sngTime = Timer
    'execute code
    MsgBox Timer - sngTime

[Stevie]

Cheers.

Seem to be getting wierd results (I am running it in Access) which are saying that it is taking no time to return the results of the queries.

[Iain17]

If that is the case, then i am guessing that the query takes under 60ms. You could try the getTickCount API i suppose.

'IN MODULE
Public Declare Function GetTickCount Lib "kernel32" () As Long


'CODE
Dim lTime As Long

lTime = GetTickCount
'CODE
MsgBox GetTickCount - lTime

[kedaman]

Querryperformance counter is the next step, you'll get an error diffusion up to one microsecond, 1/1000 of gettickcount.

Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long

Dim Start as currency, Finish as currency, Freq as currency

  QueryPerformanceCounter Start
  'your code you want to performance check
  QueryPerformanceCounter Finish
  QueryPerformanceFrequency Freq
  Msgbox CDbl(Finish - Start) * CDbl(Freq) * 1000) & "microseconds"

[Iain17]

*sigh*

Ok, i knew there would be one. If you want to start getting all offical about it. Lets take into acount the API overhead as well.

Option Explicit

Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long


Private Sub Form_Load()
    Time_Addition
End Sub

Private Sub Time_Addition()
   Dim ctr1 As Currency, ctr2 As Currency, cFreq As Currency
   Dim cOverhead As Currency, lNum As Long, I As Long
     
     'get the frequency
     QueryPerformanceFrequency cFreq
     QueryPerformanceCounter ctr1
     QueryPerformanceCounter ctr2
     
     'determine API overhead
     cOverhead = ctr2 - ctr1
     
     'start timing loop
     QueryPerformanceCounter ctr1
     For I = 1 To 10000000
       lNum = lNum + 1
     Next I
     'get the end time
     QueryPerformanceCounter ctr2
     
     Debug.Print "("; ctr1; "-"; ctr2; "-"; cOverhead; ") /"; cFreq
     Debug.Print I - 1 & " additions took";
     Debug.Print (ctr2 - ctr1 - cOverhead) / cFreq; "seconds"
End Sub

[Stevie]

Cor blimey! Turn my back for two minutes to go to lunch and I get loads of replies.

I tried the GetTickCount way before I posted my question here and had the same results as with the Timer method.

I've now tried the QueryPerformanceCounter method and all is working well apart from the fact that the one that should be quickest is the slowest!!

Thanks for all your help.