Geek Answers Handbook

What are the best tools for query optimization in VBA and why?

I am new to programming and I was curious; What are the best ways to time and optimize my code? The code I want now is a series of requests in VBA for MS-Access, but I would also like to use the time code in VB.NET and ASP.NET.

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

Please explain the reasons and explanations to help me understand how to do this.

Thanks in advance.

0
source share
2 answers

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

, (sargable), . , , Order By, . , SQL, , , , .

+2

, . debug.print Timer().

, :)

msgbox(), msg .

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
0

More articles:


All Articles