在VBA中优化查询的最佳工具是什么？为什么？

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.

我对编程很新,我很想知道;定时和优化代码的最佳方法是什么?我目前想要的代码是VBA for MS-Access中的一系列查询,但我也想在VB.NET和ASP.NET中计算代码。

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.

提前致谢。

2 个解决方案

#1

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

此计时器可能会有所帮助: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.

利用索引(sargable)的查询将运行得更快。如果没有必要,还有其他要点,例如避免订购。通常最好发布似乎花费太长时间的SQL,并且您经常会收到一些提高速度的建议。

#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.

除非你有一个非常大的应用程序,我不会担心工具。您通常可以使用一些debug.print语句和Timer()调用来解决任何瓶颈问题。

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.

尝试注释掉msgbox(),你会在代码窗口的底部看到一个漂亮的小调试信息。

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

#1