Why do SQL queries with VBA function run so slow?

Question

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

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

or a parameter:

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

or with a join:

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

It runs much faster.

Why?

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

Answer 1

Your sample with GetFooF4 cannot be optimised neither by Sql Server, neither by Access. And reopening this rs all the time is very inefficient. As a general rule, avoid using Access specific functions or code in your queries. This prevent Acces from sending the query 'as is' to Sql server. It must instead download the full bunch of data and process it locally, which means more traffic and less speed.
See http://msdn.microsoft.com/en-us/library/bb188204(v=sql.90).aspx#optaccsql_topic2

Answer 2

And how does it compare to :

r = getFooF4()

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

If this is as slow as the original, then the answer is simple: The getFooF4() function is the slow part.

Answer 3

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

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.

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

...with this:

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

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.

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.