[AccessD] Access Sub Queries

They truncate memo fields too

Charlotte

Sent from my Samsung Captivate(tm) on AT&T

"Heenan, Lambert" <Lambert.Heenan at chartisinsurance.com> wrote:

>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
>
>-- 
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com