What are the best tools for optimizing queries in VBA and why?

Question

I'm fairly new to programming and I was wondering; What are the best ways to time and optimize my code? The code I'm currently wanting to time is a series of queries in VBA for MS-Access, but I would also like to time code in VB.NET and ASP.NET as well.

So to reiterate, what is the best way to time code and optimize it for each language?

Please give reasons and explanations to help me understand how to do it.

Thanks in advance.

Answer 1

This timer may help: http://support.microsoft.com/kb/233275

A query that takes advantage of indexes (sargable) will run faster. There are other points, such as avoiding Order By, if it is not necessary. Generally it is best to post SQL that seems to take too long and you will often get a number of suggestions for improving the speed.

Answer 2

Unless you had a very large app i wouldn't worry about tools. You can generally zero in on any bottlenecks with a few debug.print statements and Timer() calls.

The code below i've ripped from a very pedantic answer on array resizing :)

Try commenting out the msgbox() and you'll see a nice little debug msg at the bottom of your code window.

Option Explicit

Sub RedimTest()
  Dim tA, tB As Single
  tA = RedimTestA(1000000)
  tB = RedimTestB(1000000)

  MsgBox "Test A takes : " & tA & ", and Test B takes : " & tB

End Sub


Function RedimTestA(iterations As Long) As Single
  Dim t As Single
  Dim i As Long
  Dim aryString() As String
  Dim myString As String

  t = Timer
  Do While i <= iterations
    ReDim Preserve aryString(i) As String
    aryString(i) = "ABCEFG123"
    i = i + 1
  Loop

  RedimTestA = Timer - t
  Debug.Print "RedimTestA: " & Format(RedimTestA, "#0.0000ms")

End Function


Function RedimTestB(iterations As Long) As Single
  Dim t As Single
  Dim i As Long
  Dim aryString() As String
  Dim myString As String

  t = Timer

  ReDim aryString(0) As String
  Do While i <= iterations
    If i >= UBound(aryString) Then
      ReDim Preserve aryString(i * 2) As String
    End If

    aryString(i) = "ABCEFG123"
    i = i + 1
  Loop

  ReDim Preserve aryString(i - 1) As String ' i - 1 becuase of the final i = i + 1
  RedimTestB = Timer - t
  Debug.Print "RedimTestB: " & Format(RedimTestB, "#0.0000ms")

End Function