[AccessD] Access Sub Queries

Dan Waters df.waters at comcast.net
Wed Oct 27 14:53:00 CDT 2010


Hi Lambert,

I try to break down a complex query like this into more than one query, with
each subsequent query using the previous query(s) as a data source.  They
are easier to test, and I think they might run faster as well.

Good Luck!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Wednesday, October 27, 2010 2:46 PM
To: ACCESS-L Email (ACCESS-L at PEACH.EASE.LSOFT.COM); Access-D
Email(accessd at databaseadvisors.com)
Subject: [AccessD] Access Sub Queries

x-posted to Access-L and AccessD

Why is it that queries with sub-queries can be so very, very slow in Access?

Here's a query

SELECT Data_Stream_qry.PK_ID, Data_Stream_qry.dDateCreated
FROM DataFile_Sample_tbl INNER JOIN Data_Stream_qry ON
DataFile_Sample_tbl.[Policy ID] = Data_Stream_qry.PK_ID
WHERE (((Data_Stream_qry.dDateCreated) In (select top 2 dDateCreated from
Data_Stream_qry order by dDateCreated)))
ORDER BY Data_Stream_qry.PK_ID, Data_Stream_qry.dDateCreated;

The intention of it is to list the top two date values in the table
Data_Stream_qry for each policy listed in the table DataFile_Sample_tbl. The
sample table contains 500 rows, the Date_Stream_qry is a union query which
returns 13,000 unique rows from a total of 1.5million and the date field has
an index on it in both source tables involved in the union query.

This query has been running for well over an hour now, and only two segments
of the progress meter have shown up. Yet if I write a little code to simply
execute the TOP 2 sub query on its own, once for each policy in
DataFile_Sample_tbl, and save the results to a third table, then those 500
query instances only take about 20 minutes to run.


Lambert
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list