Is Dlookup the best option?

This is a multi-part message in MIME format.

------=_NextPart_000_0015_01C840F0.7674BD70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

In my database I have a 'control table' in which basic info is stored =
about the application, for instance the application's path and the name =
of the company that is using it. In all of the application's reports I =
have a page footer with an unbound field that retrieves and shows the =
name of the company. This is done by Dlookup. My question is: is Dlookup =
the best way to do this, performance wise, or is there a more efficient =
way?
Thanks in advance,
John
------=_NextPart_000_0015_01C840F0.7674BD70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: Is Dlookup the best option?

"lyle" wrote

> DLookup was slow in Access 95? It got fast
> in Access 97? (Maybe these two should be
> 97 and 2000.)

My recall is that the domain aggregate functions were slower than a query in
some circumstances, in some versions, and that the circumstances were
difficult (or impossible) to predict.

I have used domain aggregate functions with very satisfactory performance in
Access 2.0 and later. I am sure I used them in Access 1.0 and 1.1, too, but
at that time, didn't yet know to worry about them being slow.

Almost always, the circumstances in which I have had need to use a domain
aggregate function were such that it was the only feasible option (Using DAO
code to open a db and an rs, and execute a query, and return a result, close
the rs and db, and set them to Nothing isn't necessarily _fast_, either.),
it was executed so infrequently that it wouldn't be big hindrance even if it
were slow, so I never did "get worked up" over it.

Larry Linson
Microsoft Access

Re: Is Dlookup the best option?

On Dec 19, 1:42 pm, "Rick Brandt" wrote:
> Tom van Stiphout wrote:
> > On Tue, 18 Dec 2007 06:31:01 -0800 (PST), lyle
> > wrote:
> > > Number of Records: 520378
>
> >> DLookup:(100 iterations) CAZN / 0.047 seconds
>
> > >DAO :(100 iterations) CAZN / 0.031 seconds
>
> > >ADO :(100 iterations) CAZN / 0.141 seconds
> > Thanks Lyle. I would not have guessed this outcome.
> > Is this a db downloadable from USDA? Do you have a link?
>
> On top of that, Lyle used Workspaces(0)(0) to avoid the overhead of
> CurrentDB collection refreshing. If DLookup() were compared to a DAO
> Recordset using CurrentDB (something that many people are likely to do) then
> I suspect that the recordset would lose what little speed advantage it has
> in Lyle's code.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com

Yes.
I tried that and the results for a hundred iterations of CurrentDB
and DLookup were always identical or very, very close:

Number of Records: 520378
<1>
***********************************
ADO:(100 iterations) CAZN / 0.172 seconds
CurrentDB:(100 iterations) CAZN / 0.047 seconds
DBengine:(100 iterations) CAZN / 0.031 seconds
DLookup:(100 iterations) CAZN / 0.047 seconds
***********************************
<2>
***********************************
ADO:(100 iterations) CAZN / 0.156 seconds
CurrentDB:(100 iterations) CAZN / 0.047 seconds
DBengine:(100 iterations) CAZN / 0.031 seconds
DLookup:(100 iterations) CAZN / 0.047 seconds
***********************************
<3>
***********************************
ADO:(100 iterations) CAZN / 0.141 seconds
CurrentDB:(100 iterations) CAZN / 0.047 seconds
DBengine:(100 iterations) CAZN / 0.031 seconds
DLookup:(100 iterations) CAZN / 0.047 seconds
***********************************
<4>
***********************************
ADO:(100 iterations) CAZN / 0.14 seconds
CurrentDB:(100 iterations) CAZN / 0.047 seconds
DBengine:(100 iterations) CAZN / 0.031 seconds
DLookup:(100 iterations) CAZN / 0.047 seconds
***********************************
<5>
***********************************
ADO:(100 iterations) CAZN / 0.157 seconds
CurrentDB:(100 iterations) CAZN / 0.046 seconds
DBengine:(100 iterations) CAZN / 0.032 seconds
DLookup:(100 iterations) CAZN / 0.047 seconds
***********************************
<6>
***********************************
ADO:(100 iterations) CAZN / 0.14 seconds
CurrentDB:(100 iterations) CAZN / 0.047 seconds
DBengine:(100 iterations) CAZN / 0.031 seconds
DLookup:(100 iterations) CAZN / 0.047 seconds
***********************************
<7>
***********************************
ADO:(100 iterations) CAZN / 0.141 seconds
CurrentDB:(100 iterations) CAZN / 0.047 seconds
DBengine:(100 iterations) CAZN / 0.031 seconds
DLookup:(100 iterations) CAZN / 0.047 seconds
***********************************
<8>
***********************************
ADO:(100 iterations) CAZN / 0.156 seconds
CurrentDB:(100 iterations) CAZN / 0.047 seconds
DBengine:(100 iterations) CAZN / 0.031 seconds
DLookup:(100 iterations) CAZN / 0.047 seconds
***********************************
<9>
***********************************
ADO:(100 iterations) CAZN / 0.141 seconds
CurrentDB:(100 iterations) CAZN / 0.047 seconds
DBengine:(100 iterations) CAZN / 0.031 seconds
DLookup:(100 iterations) CAZN / 0.047 seconds
***********************************
<10>
***********************************
ADO:(100 iterations) CAZN / 0.14 seconds
CurrentDB:(100 iterations) CAZN / 0.047 seconds
DBengine:(100 iterations) CAZN / 0.047 seconds
DLookup:(100 iterations) CAZN / 0.047 seconds
***********************************
No, I don't know why USDA uses strings for numeric values.

Re: Is Dlookup the best option?

lyle wrote in
news:ff424f1b-9399-47de-85fe-d70591a34250@r60g2000hsc.google groups.co
m:

> I tried that and the results for a hundred iterations of
> CurrentDB and DLookup were always identical or very, very close:
>
> Number of Records: 520378
><1>
> ***********************************
> ADO:(100 iterations) CAZN / 0.172 seconds
> CurrentDB:(100 iterations) CAZN / 0.047 seconds
> DBengine:(100 iterations) CAZN / 0.031 seconds
> DLookup:(100 iterations) CAZN / 0.047 seconds
> ***********************************

It would seem pretty obvious to me that DLookup refreshes the
collections before running, just as CurrentDB does. It's probably a
wise thing, really, for a built-in function that could be run any
time after a collection has been altered by a user.

I don't use domain lookup functions, but instead use Trevgor Best's
tLookup functions, altered to use an optional predefined database
variable. Since I always cache a reference to CurrentDB, this would
likely give results similar to the DBEngine example.

On the other hand, I can't think of circumstances in which it would
matter, as if you're using DLookup in a loop of any significance,
it's probably a mistake. The most common place for such a mistake
would be in a query, where you should just use the source tables
directly in the SQL.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/