[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