为什么使用 VBA 函数的 SQL 查询运行速度如此之慢? [英] Why do SQL queries with VBA function run so slow?

问题描述

我继承了一个在很多地方执行以下类型查询的应用:

I've inherited an app that does the following kind query in a lot of places:

select foo.f1, foo.f2, foo.f3
from foo
where foo.f4 = getFooF4()

getFooF4 看起来像这样

getFooF4 looks like this

Public Function getFooF4()
Dim dbCurrent As Database
Dim rstBar As Recordset

    Set dbCurrent = CurrentDb
    Set rstBar = dbCurrent.OpenRecordset("Bar", _
                                            dbOpenDynaset, _
                                            dbSeeChanges)
    getFooF4 = rstBar![myF4]
    ''yes this appears broken... Bar only contains one row :-/

    rstBar.close
    Set rstBar = Nothing
    dbCurrent.close
    Set dbCurrent = Nothing    
End Function
'' Note: in my experimentation getFooF4 only runs once during the 
''       execution of the query.

这最终运行得很慢.如果我使用常量从查询中删除 getFooF4():

This ends up running fairly slow. If I remove getFooF4() from the query with a constant:

select foo.f1, foo.f2, foo.f3
from foo
where foo.f4 = 123456

或参数:

select foo.f1, foo.f2, foo.f3
from foo
where foo.f4 = [myFooF4]

或加入:

select foo.f1, foo.f2, foo.f3
from foo
INNER JOIN bar ON bar.myF4 = foo.f4

它运行得更快.

为什么?

规格:应用在 MS Access 2003 中编写并运行，后端数据库为 SQL Server 2008.

Specs: App written and running in MS Access 2003, back-end database is SQL Server 2008.

推荐答案

提高效率的两件事(尽管只有其中一项适用于这样的特定情况):

Two things to improve efficiency (though only one or the other will ever apply to a particular case like this):

1. 为你的函数定义一个返回类型，即 Public Function getFooF4() 应该是 Public Function getFooF4() As Long(或任何适当的数据type 是.如果没有明确的数据类型，它会返回一个变体.实际上，从来没有一个 VBA 函数应该缺少返回类型声明——如果它返回一个变体(这是完全合理的，特别是当你需要返回时在某些情况下为空)，用 As Variant 定义它.当它是其他数据类型时，明确定义它.

1. define a return type for your function, i.e., Public Function getFooF4() should be Public Function getFooF4() As Long (or whatever the appropriate data type is. Without an explicit data type, it's returning a variant. In reality, there is never a VBA function that should ever lack a return type declaration -- if it's returning a variant (which is perfectly reasonable, particularly when you need to return Null in some cases), define it with As Variant. When it's some other data type, explicitly define it.

在您的 SQL 中声明一个参数，以便查询优化器可以在其查询计划的计算中使用该信息.当您的 WHERE 子句使用函数来提供条件时，这不适用，但如果您使用对控件上的字段的引用，则应替换为:

declare a parameter in your SQL so that the query optimizer can use that information in its calculation of the query plan. That doesn't apply when your WHERE clause is using a function to supply the criterion, but if you were using a reference to a field on a control, you'd replace this:

.

  select foo.f1, foo.f2, foo.f3
  from foo
  where foo.f4 = Forms!MyForm!MyControl

...这样:

  PARAMETERS [Forms]![MyForm]![MyControl] Long;
  select foo.f1, foo.f2, foo.f3
  from foo
  where foo.f4 = Forms!MyForm!MyControl

现在，在这两种情况下，由于函数/参数在 WHERE 子句中，它只需要解析一次，所以即使函数效率低下(就像这里的情况一样，它初始化了一个数据库变量和打开记录集)，实际上不会有太大区别.

Now, in either of these cases, since the function/parameter is in the WHERE clause, it needs to be resolved only once, so even if the function is inefficient (as is the case here, with it initializing a database variable and opening a recordset), it won't actually make much difference.

另一件需要考虑的事情是用一个简单的 DLookup() 替换该函数，该函数正是为此目的而设计的.或者，由于该值来自一个表，您应该能够将它加入到您的单行表中:

Another thing to consider is replacing the function with a simple DLookup(), which is designed for exactly this purpose. Alternatively, since the value is coming from a table, you should be able to JOIN it to your one-row table:

  select foo.f1, foo.f2, foo.f3
  from foo INNER JOIN Bar ON foo.f4 = Bar.MyF4

这将被查询优化器最大程度地优化，因为它根本没有未知数——查询优化器将知道它需要知道的关于数据类型和表统计信息的一切，并且可以选择最有效的检索方法.

This would be maximally optimizable by the query optimizer since there are no unknowns in it at all -- the query optimizer will know everything it needs to know about data types and table stats and can pick the most efficient retrieval method possible.

这篇关于为什么使用 VBA 函数的 SQL 查询运行速度如此之慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！