0

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.

Improve this question
3
  • I'll also note that the queries are statically defined as Query Objects in MS Access, not on the SQL Server side. Commented Jan 17, 2011 at 17:50
  • Look at how clean and simple your INNER JOIN query is. Can you really not see how forcing a particular plan (e.g. requiring a second connection to lookup a value in a table) is not allowing the optimizer do its job? Commented Jan 18, 2011 at 11:16
  • @ODW - Yes, in fact I've switched most of them over to use the INNER JOIN query already. There are a couple I can't yet for some other WTF reasons. I'm mostly interested in WHY the original code is so slow. Currently it appears to be an effect of the implicit Variant type the function returns. Commented Jan 19, 2011 at 18:49

3 Answers 3

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I don't think that's true in this case. The Jet/ACE query optimizer will resolve the value returned by the function, and send only the literal value to the server. Jet/ACE would pull down the whole table if the function returned a different value for each row, but it doesn't, so that wouldn't happen.
2

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.

Improve this answer

3 Comments

Wow!!! Adding "as long" made it much faster! Is it because the query is not being optimised because of the implicit Variant type for the function or is causing it to download a bunch more data as iDevlop suggested?
I think it's because of the optimization. I don't know if the lack of optimization results in pulling down the full table -- I'd think it wouldn't, but maybe Jet/ACE makes a different decision on what to send off to the server based on the data type.
You could test by running with SHOWPLAN on and compare the various versions, with no return type, with Variant return type, and with Long (or whatever the appropriate strong data type is), and see what the differences are.
1

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.

Improve this answer

1 Comment

How, exactly, would you execute what you've just suggested in Access? And are you aware that the Jet/ACE query optimizer will already do this (i.e., it will execute the function only once)?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.