[AccessD] Access Sub Queries

That sounds like a good idea which I will pursue. I did not know that the union would blow away my index.

Thanks,

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, October 27, 2010 4:33 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Access Sub Queries

Hi Lambert

Because you are doing it about as complicated as it can be - using the union query not twice but three times, the last in the subquery for each row. And a union query carries no index.
Break it down. You may write the output from the union query to a temp table and perhaps as well the output from the union query combined with the subquery.

/gustav


>>> Lambert.Heenan at chartisinsurance.com 27-10-2010 21:46 >>>
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