[AccessD] Access Sub Queries

That's my normal approach too, but I cant do that here because the goal is to get the top 2 records for each policy in the sample table.

Lambert 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Wednesday, October 27, 2010 3:53 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access Sub Queries

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

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