Re: Is Dlookup the best option?

On Tue, 18 Dec 2007 06:31:01 -0800 (PST), lyle
wrote:

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 wrote:
>> On Mon, 17 Dec 2007 17:13:32 -0800 (PST), lyle
>>
>> wrote:
>>
>> 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.
>
>
>
>

Re: Is Dlookup the best option?

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