Re: Calling functions from queries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dale Fye (dale.fye_at_nospam.com)
Date: 08/25/04 

Date: Tue, 24 Aug 2004 20:25:19 -0400

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

"ABL" <ablnews@newsguy.com> wrote in message
news:cgfv4i02iua@news1.newsguy.com...
> 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.

Relevant Pages

  • Calling functions from queries
    ... I have a query that has calculated fields and joins in it. ... wondering what kind of performance hit I might expect if I have to call ... and is used to catch unsolicited email. ...
    (microsoft.public.access.formscoding)
  • Re: Calling functions from queries
    ... performance hits are often barely noticable. ... If you're calling these functions from a query, ... > I have a query that has calculated fields and joins in it. ... > and is used to catch unsolicited email. ...
    (microsoft.public.access.formscoding)
  • Re: advice on loading and searching large map in memory
    ... We have a requirement to query across two disparate systems. ... need to check for updates. ... To avoid 1000 relational queries I was planning to "cache" the entire ... Again, no one has measured performance so we don't even know if it's a performance hit at all, much less a "serious" one. ...
    (comp.lang.java.programmer)
  • Re: Looking for libraries to monitor postgres queries
    ... Yeah there is definitely a performance hit on logging every query. ... If all the apps use the proxy, ...
    (comp.lang.ruby)
  • SQL Syntax Checking
    ... I did get my answers but I guess I should rephrase my question. ... The query could have lots of join conditions in it, so I dont want to run it ... Is there any way of achieving this, without the performance hit? ... TIA, ...
    (microsoft.public.sqlserver.programming)