Re: Global variables are slowing down the query

Steve Jorgensen <nospam@xxxxxxxxxxxxx> wrote in
news:902kf1hekurcb11t1rsgt7gg6hhejpsu44@xxxxxxx:

> On Wed, 10 Aug 2005 08:08:06 +0100, Trevor Best
> <nospam@xxxxxxxxxxxxxx> wrote:
>
>>Steve Jorgensen wrote:
>>
>>> Not only would the query not have to determine the types at
>>> run-time, I'm betting it's so slow now because it calls the
>>> function multiple times. Whenever JET has to make calls into a
>>> user-defined function, there's a big time hit.
>>
>>IME if the function has no parameters Access will call it only
>>once. Although I've not used one in the criteria so that may be
>>the difference.
>
> My experience matches yours. I'm just not sure if one can rely on
> that rule in all cases.

If it's in the criteria and it doesn't take an argument why in the
world would the optimizer call it more than once?

Also, having a function in the criteria won't cause all the
processing to be client-side (instead of server-side), since once
the functions have been evaluated, the result can be sent to the
server as the criteria to filter on.

It is only when you have Access functions that *can't* be processed
before sending to the server that everything gets pulled to the
client and processed there. An example would be sorting on a
function. Since the function is client-side, all the data has to be
pulled and sorted. And if there are criteria on a function, same
thing.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
.

Relevant Pages

  • Re: time criteria in query
    ... Query with Time Criteria Returns No Records from Microsoft SQL ... type I'm using in SQL server? ...
    (microsoft.public.access.queries)
  • Re: 2003 Network Design Request for Reco
    ... The criteria is to provide a robust network system that has disaster ... and is fault tolerant using Windows 2003 server. ... I suggest you to hire a consultant that does this work for you. ... redundancy, file shares just need R2 with DFS and DFS-R for full ...
    (microsoft.public.windows.server.general)
  • Re: Update table from ODBC
    ... criteria are interpreted differently. ... SQLGetInfo and SQLGetTypeInfo to ask the ODBC driver whether the server ... If you want, post the SQL Strings of your Pass-Through Query, your Append ... Query using the Pass-through and your Append Query using ODBC-linked Table ...
    (microsoft.public.access.queries)
  • Re: performance logs and alerts
    ... If your server is crashing or hanging you might want to ... download our ReJuvin8 tool. ... ReJuvin8 has both an automatic and a custom mode. ... > out there that explain each and every criteria that would be great too. ...
    (microsoft.public.inetserver.iis)
  • Secure FTP
    ... I want to create an secure ftp account on our server. ... criteria is that other colleagues should not require specialised ...
    (microsoft.public.win2000.security)