By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

Is Dlookup the best option?

P: n/a
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
Dec 17 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
If you Google this group for DLookup you will find a lot of comments about DLookup.
It is relatively slow (as all Aggregate functions are).
There are quicker functions available.
http://easyweb.easynet.co.uk/~trevor.../baslookup.zip
But do you notice any slowness??

Sometimes I use global vars.
When using globals you must realise that these will be reset when your app errors (unhandled).
When using a runtime version (as I do) the app will crash in that case... so after a restart the globals are 'there' and valid

A hidden form might be the best option for 'static' values. Values are always available and quickly retrieved.
But did you notice any slowness with DLookup ??

btw: don't use HTML when posting questions. Not needed and not wanted.

Arno R

Dec 17 '07 #2

P: n/a
I haven't noticed slowness (yet) but I recall reading about it somewhere.
And I have another app that uses several Dlookups in a form's load event and
sometimes loading of that form is slow but it could have another reason.

Thanks for your comments and suggestions. I'll check them out.
John
"Arno R" <ar****************@planet.nlschreef in bericht
news:47***********************@text.nova.planet.nl ...
If you Google this group for DLookup you will find a lot of comments about
DLookup.
It is relatively slow (as all Aggregate functions are).
There are quicker functions available.
http://easyweb.easynet.co.uk/~trevor.../baslookup.zip
But do you notice any slowness??

Sometimes I use global vars.
When using globals you must realise that these will be reset when your app
errors (unhandled).
When using a runtime version (as I do) the app will crash in that case... so
after a restart the globals are 'there' and valid

A hidden form might be the best option for 'static' values. Values are
always available and quickly retrieved.
But did you notice any slowness with DLookup ??

btw: don't use HTML when posting questions. Not needed and not wanted.

Arno R
Dec 17 '07 #3

P: n/a
On Dec 17, 3:04 pm, <joh...@johnie.comwrote:
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
DLookup was slow in Access 95? It got fast in Access 97? (Maybe these
two should be 97 and 2000.)

It's fine now.

Why not test it?

Private Declare Function GetTickCount& Lib "Kernel32" ()
Public Sub CheckDLookup()
Dim FirstName$
Dim Ticks&
Ticks = GetTickCount
FirstName = DLookup("[First Name]", "Employees", "[Last
Name]='Kotas'")
Ticks = GetTickCount - Ticks
Debug.Print FirstName, Ticks / 1000
' MsgBox FirstName & ":" & Ticks / 1000
End Sub

Uncomment the Msgbox line if you're in a situation where reading the
immediate window may not be convenient.

On my machine looking up a firstname in Northwind.mdb takes 16
thousandths of a second the first time. After that, it doesn't take
enough time to register.

I doubt I'd bother to find something quicker.

Dec 17 '07 #4

P: n/a

"Tom van Stiphout" <no*************@cox.netschreef in bericht news:9n********************************@4ax.com...
The performance variations may be more noticeable with
every two extra zeros in the recordcount.

-Tom.
Agreed.
Obviously it also depends on how many Lookup's or DCount's you need at the *same* time.

I clearly remember the difference with one of my 'old' app's.
Presenting a form with several complex (5-6) counts (from an inventory).
With DCount's the form loaded slowly (up to two seconds).
With DAO-code the form loads almost immediately.

Arno R
Dec 18 '07 #5

P: n/a
On Dec 17, 11:58 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Mon, 17 Dec 2007 17:13:32 -0800 (PST), lyle

<lyle.fairfi...@gmail.comwrote:

Not to spoil you and Larry's tete-a-tete, but a thorough test would at
least include tables of various sizes. Northwind probably has dozens
of employees. The performance variations may be more noticeable with
every two extra zeros in the recordcount.
Enough to matter?

Private Declare Function GetTickCount& Lib "Kernel32" ()

Public Sub CallAll()
HowManyRecords
CheckDCount
CheckDAO
CheckADO
Debug.Print "No, I don't know why USDA uses strings for numeric
values."
End Sub

Public Sub HowManyRecords()
Debug.Print "Number of Records: " & DCount("*", "NUT_DATA")
End Sub

Public Sub CheckDCount()
Dim Iterator&
Dim Ticks&
Dim Whatever$
Ticks = GetTickCount
For Iterator = 1 To 100
Whatever = DLookup("Deriv_CD", "NUT_DATA", "[NDB_No] = '93600' AND
[Nutr_No] = '421'")
Next Iterator
Ticks = GetTickCount - Ticks
Debug.Print "DLookup:(100 iterations) " & Whatever & " / " & Ticks /
1000 & " seconds"
End Sub

Public Sub CheckDAO()
Dim Iterator&
Dim Ticks&
Dim Whatever$
Ticks = GetTickCount
For Iterator = 1 To 100
Whatever = DBEngine(0)(0).OpenRecordset("SELECT Deriv_CD FROM
NUT_DATA WHERE [NDB_No] = '93600' AND [Nutr_No] = '421'")(0)
Next Iterator
Ticks = GetTickCount - Ticks
Debug.Print "DAO :(100 iterations) " & Whatever & " / " & Ticks /
1000 & " seconds"
End Sub

Public Sub CheckADO()
Dim Iterator&
Dim Ticks&
Dim Whatever$
Ticks = GetTickCount
For Iterator = 1 To 100
Whatever = CurrentProject.Connection.Execute("SELECT Deriv_CD FROM
NUT_DATA WHERE [NDB_No] = '93600' AND [Nutr_No] = '421'")(0)
Next Iterator
Ticks = GetTickCount - Ticks
Debug.Print "ADO :(100 iterations) " & Whatever & " / " & Ticks /
1000 & " seconds"
End Sub

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

No, I don't know why USDA uses strings for numeric values.

Dec 18 '07 #6

P: n/a
On Tue, 18 Dec 2007 06:31:01 -0800 (PST), lyle
<ly************@gmail.comwrote:

Thanks Lyle. I would not have guessed this outcome.
Is this a db downloadable from USDA? Do you have a link?

-Tom.

>On Dec 17, 11:58 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
>On Mon, 17 Dec 2007 17:13:32 -0800 (PST), lyle

<lyle.fairfi...@gmail.comwrote:

Not to spoil you and Larry's tete-a-tete, but a thorough test would at
least include tables of various sizes. Northwind probably has dozens
of employees. The performance variations may be more noticeable with
every two extra zeros in the recordcount.

Enough to matter?

Private Declare Function GetTickCount& Lib "Kernel32" ()

Public Sub CallAll()
HowManyRecords
CheckDCount
CheckDAO
CheckADO
Debug.Print "No, I don't know why USDA uses strings for numeric
values."
End Sub

Public Sub HowManyRecords()
Debug.Print "Number of Records: " & DCount("*", "NUT_DATA")
End Sub

Public Sub CheckDCount()
Dim Iterator&
Dim Ticks&
Dim Whatever$
Ticks = GetTickCount
For Iterator = 1 To 100
Whatever = DLookup("Deriv_CD", "NUT_DATA", "[NDB_No] = '93600' AND
[Nutr_No] = '421'")
Next Iterator
Ticks = GetTickCount - Ticks
Debug.Print "DLookup:(100 iterations) " & Whatever & " / " & Ticks /
1000 & " seconds"
End Sub

Public Sub CheckDAO()
Dim Iterator&
Dim Ticks&
Dim Whatever$
Ticks = GetTickCount
For Iterator = 1 To 100
Whatever = DBEngine(0)(0).OpenRecordset("SELECT Deriv_CD FROM
NUT_DATA WHERE [NDB_No] = '93600' AND [Nutr_No] = '421'")(0)
Next Iterator
Ticks = GetTickCount - Ticks
Debug.Print "DAO :(100 iterations) " & Whatever & " / " & Ticks /
1000 & " seconds"
End Sub

Public Sub CheckADO()
Dim Iterator&
Dim Ticks&
Dim Whatever$
Ticks = GetTickCount
For Iterator = 1 To 100
Whatever = CurrentProject.Connection.Execute("SELECT Deriv_CD FROM
NUT_DATA WHERE [NDB_No] = '93600' AND [Nutr_No] = '421'")(0)
Next Iterator
Ticks = GetTickCount - Ticks
Debug.Print "ADO :(100 iterations) " & Whatever & " / " & Ticks /
1000 & " seconds"
End Sub

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

No, I don't know why USDA uses strings for numeric values.

Dec 19 '07 #7

P: n/a
On Dec 18, 11:12 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Tue, 18 Dec 2007 06:31:01 -0800 (PST), lyle

<lyle.fairfi...@gmail.comwrote:

Thanks Lyle. I would not have guessed this outcome.
Is this a db downloadable from USDA? Do you have a link?
http://www.ars.usda.gov/Services/docs.htm?docid=15867
Dec 19 '07 #8

P: n/a
Tom van Stiphout wrote:
On Tue, 18 Dec 2007 06:31:01 -0800 (PST), lyle
<ly************@gmail.comwrote:
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
Dec 19 '07 #9

P: n/a
On Dec 19, 1:42 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
Tom van Stiphout wrote:
On Tue, 18 Dec 2007 06:31:01 -0800 (PST), lyle
<lyle.fairfi...@gmail.comwrote:
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.
Dec 19 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.