Performance with VBA functions in queries

[Rémi]

Can anyone elaborate on the performance impact of having a user-
defined function in a query? I inherited a large Access database
which had some interesting design choices applied to it - my current
concern is that business logic are found in a number of queries.

Say I have this:

tableA
* pkFieldTableA
* fielda1
* fielda2
* fielda3

qryB (based on tableB)
* pkQryB
* fkTableA
* fieldb1
* fieldb2
* SomeVBAFunction(fieldb1, fieldb2)


And a query that joins both with a WHERE clause that filters the
result set.

Can anyone tell me:

* Will SomeVBAFunction be evaluated for each and every row before the
set of records are filtered, or will only the subset of valid rows
have the function evaluated?
* From a performance perspective, is it preferable to have the
calculation in-line, or is the VBA call ok? (In my opinion, it would
be preferable not to have to do either, but anyhow.)

If it makes any difference, this runs on Access XP, but I also run
Access 2007.

Regards,
Remi.

 

[Jellifish]

What does the function do?

 

[Albert D. Kallal]

If there is an index, and the where clause only returns two records, I am
quite sure the function only runs for those two records (assuming an index
can be used, and of course the results of that function are NOT part an
condition in the where clause).

* From a performance perspective, is it preferable to have the
calculation in-line, or is the VBA call ok?

If you can use an sql expresison in place of that VBA fucntion, the query
will run MUCH faster. owever, the VBA call is ok, it really depends on what
you do inside the VBA that really going to kill perforamnce. For example if
that VBA opens up any reocrd sets, then this function will run VERY slow
indeed.

So just keep in mind that opening up a recordset is very expensive in terms
of processing time. It's kinda like asking me if it's quicker to cross the
street by walking on foot, or using a helicopter. on the other hand if you
just enter into the helicopter, and go through the very long checklist of
turning on all the control systems, firing up electronic systems, turning
on navagation systems etc, and then waiting for the motor + blades to reach
operating speed I will of long AGO crossed a street by walking accorss it on
foot.

On the other hand if that helicopters at full board speed I can hardly look
over my head and watch it fly over the same street before I've even taken
one foot step into the street and the helicopter is likely long gone out of
sight already..

The cost of opening a record set is about somewhere in the range of 40 to
50,000 records (in other words I can transfer about 40,000 records in the
time it takes to open the record set, so the helicopter startup analogy is a
really great one. Once a recordset is open, the data starts to flow really
fast *once* it been opened and all of the dancing has occurred to get that
record set in place.

Often we see posters in this newsgroup with a function inside of a query,
and inside that function code is an opening of a record sets (which of
course would occur for each row oh of that query).


In most cases, since the query is being sent to a report, you best to put
the expression + vba in the text box expression in the report as opposed to
the query.......

The other consideration is how many records? If you returning small
datasets in the 10,000 record range then it likely not an issue performance
wise for the function (unless of course you're opening record sets inside of
that function, then you're in real trouble as mentioned above)....

 

[Rémi]

If there is an index, and the where clause only returns two records, I am
quite sure the function only runs for those two records (assuming an index
can be used, and of course the results of that function are NOT part an
condition in the where clause).

Thanks Albert - exactly what I wanted to hear. My main concern was
the function being executed for each record before filtering started.

I'm aware of the weight associated with certain operations, like
opening a recordset. In this case, the methods called are
calculations, related to the application's business logic. My goal is
to move them out of queries (where they are duplicated numerous times)
into VBA code, so I can centralize the business logic and make it
easier to maintain over the long run.

Thanks again.
Remi.