Select query w. DLookup => 2 secs., make-table version => 2 hrs.

[Gerwin Ramaker]

Dear all,


I have created a database with 35.000 records. In this database, I have
created a query that uses the DLookup function to retrieve data. In "select"
mode, this query takes about two seconds to finish. In "make-table" mode
however, it takes two hours. Access seems to freeze ("Not responding"), but
remains active as the database file size continually grows until query
finishes in the end.

Normally I use Access 2003, but to test this I have also tried Access 2007,
again with the same result.

The field I use for DLookup is indexed.

Does somebody know more about the possible cause?


Thanks,

Gerwin Ramaker
Nijmegen, NL

 

[Stefan Hoffmann]

hi Gerwin,

I have created a database with 35.000 records. In this database, I have
created a query that uses the DLookup function to retrieve data. In "select"
mode, this query takes about two seconds to finish.

Create a simple VBA test:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("yourQuery", dbOpenSnapshot)
Debug.Print "Start : " & Now()
Do While
rs.MoveNext
Loop
Debug.Print "End : " & Now()

Does somebody know more about the possible cause?

DLookup() is a very expensive call, in some cases it is not evaluated at
once for all rows. Maybe this is a case.

The best solution is trying to use a INNER/LEFT JOIN instead of the
DLookup(). But this depends on your SELECT query.


mfG
--> stefan <--

 

[S.Clark]

Before I pass judgement, post the SQL.

(Hint: The answer is going to be, "Don't use DLookup when performance is an
issue.", but thought I'd see what you're up to first.)

 

[John W. Vinson]

On Mon, 19 Oct 2009 01:13:01 -0700, Gerwin Ramaker <Gerwin

Dear all,


I have created a database with 35.000 records. In this database, I have
created a query that uses the DLookup function to retrieve data. In "select"
mode, this query takes about two seconds to finish. In "make-table" mode
however, it takes two hours. Access seems to freeze ("Not responding"), but
remains active as the database file size continually grows until query
finishes in the end.

Hrm. So it *DOES* finish? How big is the resulting table?

By all means, as requested, post the SQL and indicate how any tables are
related. I'm suspecting that there might be a Cartesian join in the mix
(causing Access to need to process millions of virtual records to create a
small number of actual records).

I must also wonder why a MakeTable query would be needed if a Select query
returns the desired data in seconds...! Even if the MakeTable were fast,
what's the point of cluttering your database, systems tables, indexes, etc.
etc. with a new table?