Pass through query works fast but not as basis for other queries

E

elbyc

I have a pass through query that works very quickly - 3 seconds to
return 11,000 records. When I use it as the basis for a find-
duplicates query, it takes several minutes to load. I used the Access
Query wizard to create the find-duplicates query with the following
SQL (I added a filter to show only records created after 10/1/2009 and
took off the order-by sequencing). Any ideas what would slow it down
so much?

SELECT Production_Review_Query.Investment_Team_Member,
Production_Review_Query.preferred_date,
Production_Review_Query.client_name, Production_Review_Query.id,
Production_Review_Query.submitted_on_behalf_of, "#http://smomac01/
PM_Request_form/index.php?ID=" & [id] & "##" AS URL,
Production_Review_Query.submitted_on
FROM Production_Review_Query
WHERE (((Production_Review_Query.Investment_Team_Member) In (SELECT
[Investment_Team_Member] FROM [Production_Review_Query] As Tmp GROUP
BY [Investment_Team_Member],[preferred_date],[client_name] HAVING
Count(*)>1 And [preferred_date] = [Production_Review_Query].
[preferred_date] And [client_name] = [Production_Review_Query].
[client_name])) AND
((Production_Review_Query.submitted_on)>#10/1/2009#));
 
J

John W. Vinson

I have a pass through query that works very quickly - 3 seconds to
return 11,000 records. When I use it as the basis for a find-
duplicates query, it takes several minutes to load. I used the Access
Query wizard to create the find-duplicates query with the following
SQL (I added a filter to show only records created after 10/1/2009 and
took off the order-by sequencing). Any ideas what would slow it down
so much?

SELECT Production_Review_Query.Investment_Team_Member,
Production_Review_Query.preferred_date,
Production_Review_Query.client_name, Production_Review_Query.id,
Production_Review_Query.submitted_on_behalf_of, "#http://smomac01/
PM_Request_form/index.php?ID=" & [id] & "##" AS URL,
Production_Review_Query.submitted_on
FROM Production_Review_Query
WHERE (((Production_Review_Query.Investment_Team_Member) In (SELECT
[Investment_Team_Member] FROM [Production_Review_Query] As Tmp GROUP
BY [Investment_Team_Member],[preferred_date],[client_name] HAVING
Count(*)>1 And [preferred_date] = [Production_Review_Query].
[preferred_date] And [client_name] = [Production_Review_Query].
[client_name])) AND
((Production_Review_Query.submitted_on)>#10/1/2009#));
Click to expand...

When you open a query (passthrough or not) in Access, the program will
retrieve one or a few records immediately and present them for display, and
then keep retrieving additional records in the background. So the three
seconds is probably misleading, unless you are getting to the end of the
recordset in three seconds (which would be rather surprising!)

This duplicates query will indeed be slow, however, since you're searching the
[Production_Review_Query] in the subquery as many times as there are records
in the main query, and it's a Totals query at that. This is just going to be
slow, if you're running a query 11,000 times!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
E

elbyc

I have a pass through query that works very quickly - 3 seconds to
return 11,000 records. When I use it as the basis for a find-
duplicates query, it takes several minutes to load. I used the Access
Query wizard to create the find-duplicates query with the following
SQL (I added a filter to show only records created after 10/1/2009 and
took off the order-by sequencing). Any ideas what would slow it down
so much?
Click to expand...
SELECT Production_Review_Query.Investment_Team_Member,
Production_Review_Query.preferred_date,
Production_Review_Query.client_name, Production_Review_Query.id,
Production_Review_Query.submitted_on_behalf_of, "#http://smomac01/
PM_Request_form/index.php?ID=" & [id] & "##" AS URL,
Production_Review_Query.submitted_on
FROM Production_Review_Query
WHERE (((Production_Review_Query.Investment_Team_Member) In (SELECT
[Investment_Team_Member] FROM [Production_Review_Query] As Tmp GROUP
BY [Investment_Team_Member],[preferred_date],[client_name] HAVING
Count(*)>1  And [preferred_date] = [Production_Review_Query].
[preferred_date] And [client_name] = [Production_Review_Query].
[client_name])) AND
((Production_Review_Query.submitted_on)>#10/1/2009#));
Click to expand...

When you open a query (passthrough or not) in Access, the program will
retrieve one or a few records immediately and present them for display, and
then keep retrieving additional records in the background. So the three
seconds is probably misleading, unless you are getting to the end of the
recordset in three seconds (which would be rather surprising!)

This duplicates query will indeed be slow, however, since you're searching the
[Production_Review_Query] in the subquery as many times as there are records
in the main query, and it's a Totals query at that. This is just going tobe
slow, if you're running a query 11,000 times!
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks - I didn't realize it would cycle through the whole list even
if I put a filter on.
I thought I'd run a filter in a separate query and then base the dups
query off of that, but when I try to run the new query it crashes
Access. I'm trying now with several prep queries rather than a sub
query. That seems to speed things up without crashing anything.
 
J

John Spencer

You might find this query is more efficient. It runs the sub-query in the
FROM clause and so the sub-query executes ONE time. Instead of once for each
record in Production_Review_Query. The disadvantage is that you cannot update
records using this query, while with the sub-query in the where clause the
results MIGHT be updateable.

SELECT P.Investment_Team_Member
, P.preferred_date
, P.client_name, P.id
, P.submitted_on_behalf_of
, "#http://smomac01/PM_Request_form/index.php?ID=" & [id] & "##" AS URL
, P.submitted_on
FROM Production_Review_Query AS P
INNER JOIN
(SELECT Investment_Team_Member, preferred_date, client_name
FROM [Production_Review_Query]
GROUP BY Investment_Team_Member, preferred_date, client_name
HAVING Count(*) > 1) as Dupes
ON P.Investment_Team_Member = Dupes.Investment_Team_Member
AND P.Preferred_Date=Dupes.Preferred_Date
AND P.Client_Name = Dupes.Client_Name
WHERE Production_Review_Query.submitted_on > #10/1/2009#


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On Nov 17, 3:29 pm, John W. Vinson
SELECT Production_Review_Query.Investment_Team_Member,
Production_Review_Query.preferred_date,
Production_Review_Query.client_name, Production_Review_Query.id,
Production_Review_Query.submitted_on_behalf_of, "#http://smomac01/
PM_Request_form/index.php?ID="& [id]& "##" AS URL,
Production_Review_Query.submitted_on
FROM Production_Review_Query
WHERE (((Production_Review_Query.Investment_Team_Member) In (SELECT
[Investment_Team_Member] FROM [Production_Review_Query] As Tmp GROUP
BY [Investment_Team_Member],[preferred_date],[client_name] HAVING
Count(*)>1 And [preferred_date] = [Production_Review_Query].
[preferred_date] And [client_name] = [Production_Review_Query].
[client_name])) AND
((Production_Review_Query.submitted_on)>#10/1/2009#));
Click to expand...
Click to expand...

Thanks - I didn't realize it would cycle through the whole list even
if I put a filter on.
I thought I'd run a filter in a separate query and then base the dups
query off of that, but when I try to run the new query it crashes
Access. I'm trying now with several prep queries rather than a sub
query. That seems to speed things up without crashing anything.
Click to expand...
 
A

a a r o n . k e m p f

I just hate the performance tax that linked tables gives you

use Access Data Projects natively, and you won't have -any- of these
performance troubles
 
A

a a r o n . k e m p f

and btw, these filters should be in the WHERE clause, not the HAVING
clause

WHERE [preferred_date] = [Production_Review_Query].
[preferred_date] And [client_name] = [Production_Review_Query].
[client_name])) AND
((Production_Review_Query.submitted_on)>#10/1/2009#));
HAVING Count(*)>1
 
Top