Calling functions from queries

  • Thread starter Thread starter ABL
  • Start date Start date
A

ABL

I have a query that has calculated fields and joins in it. I am
wondering what kind of performance hit I might expect if I have to call
functions 8 times(!) per row returned.

6 of these calls are to two functions (3 each) with different values.
All 8 columns are consecutive. Would it be quicker to call one big
function and return the "8 columns" in one big string (I am not using
header rows, and these calculations are read only, so I don't need to
change them)?

Any suggestions for increasing performance are welcome.

I am using A2K.

Thanks,
Alden

Plese reply only to the newsgroup. This email address is seldom checked
and is used to catch unsolicited email.
 
My best advice would be to try it out and see.
Some functions run pretty quickly, others take longer.
Today's processor speeds are such that what used to be significant
performance hits are often barely noticable.

If you're calling these functions from a query, I don't know how you'd
spread the big string back out over the 8 columns without another major
bunch of function calls...

There's often a lot that can be done with query optimization, but a lot of
that is already done internally by Access (Jet), and it's usually pretty
specific to the functions in question...

- Turtle
 
If you find that running the query with all the function calls takes too
long, and the values being passed to the function are a rather distinct set
of values (integers within a reasonable range), then you could create a
lookup table with the parameter values in one or more columns and the
results of the function as a column. If this is a function you call
frequently in your program, you might want to make this a permanent table.
If it is one that is called infrequently, you might only want to create it
the first time it is needed during a particular session and then delete it
during your programs shutdown process. The advantage of this latter method
would be that you could build it at runtime to contain only those parameter
values that are needed (see sample code below).

Then, when you write your query, you could do one of two things:
1. Call DLOOKUP instead of the function
2. Add a copy of this lookup table to the query for each of the function
calls, with joins between the parameter fields.

Lets assume that you have three fields that you are using for parameters to
your function. The SQL to build this temporary table might look something
like.

SELECT ParamValue, fnYourFunction([ParamValue])
FROM (SELECT Field1 as ParamValue
FROM yourTable
UNION
SELECT Field2 as ParamValue
FROM yourTable
UNION
SELECT Field3 as ParamValue
FROM yourTable) as T


HTH
Dale
 
Back
Top