Criteria (Access 2002)

Topic

New Reply

I have created a query that runs against a linked file and it runs fine. In fact, it returns the result in less than 2 seconds. I then changed the criteria statement to include an SQL SELECT statement running against a local table. The query run time jumped from 2 seconds to 40 seconds. That a 1900% increase in time!!! What’s up with that?? The local table is currently only about 10 records and the Select query runs in under a second if run on its own. I don’t understand the time lag. Does anyone have any recommendations?

Reply | Quote

Replies

Are you talking about a subquery in the criteria? I’m not clear on which select query you’re saying runs in under a second on its own, however, it’s nearly impossible to diagnose something like this without seeing the SQL. Some things that can affect the speed is the absence or presence of indexes on the fields involved, subdatasheets, and the way the query is constructed.

Reply | Quote