• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Speeding up correlated subquery

The attached code is the second part of a union query.  The query works fine but is slow.  I'm trying to modify it and convert it from a subquery to a correlated subquery.  In the Where clause of the NOT in clause I am referring to mTaskOrder which derived from a form control.  I'm not sure, if what I have is actually a correlated query.  
UNION SELECT   '" & mTaskOrder & "'" & _
                     " , [Name], [LName], [OBS], [Department], [Function], Min([MinOfInitialPostedDate]), Max([MaxOfLastPostedDate]), [EligibilityStatus], " & _
                        "[Description],  [Contribution], [DistributionAmt] " & _
                        "FROM qryRolledUpSupportTeam WHERE [TaskOrder] <> '" & mTaskOrder & "'" & _
                        " AND [Name] NOT IN (SELECT [Name] FROM qryShareAllocation WHERE [TaskOrder] = '" & mTaskOrder & "'" & ")" & _
                        " GROUP BY '" & mTaskOrder & "'" & " , [Name], [LName], [OBS], [Department], [Function], [EligibilityStatus], [Description],  [Contribution], [DistributionAmt]"

Open in new window

Juan Velasquez
Juan Velasquez
  • 2
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might try saving each element in your UNION query as a stored query (i.e. the SELECT statement here would be one, the other SELECT statement would be another, and so on). Stored queries can sometimes speed up things of this nature.

If not, you can always dump everything into a temporary table and use that. This is often your best method.
You don't have a nested query, if that is what you are asking.  The NOT IN is not referring to an element in the outer query.  

I would check the speed of each sub query and see if there are any missing indexes.
Juan VelasquezAuthor Commented:
Perhaps I can run the union query on the server, via a stored procedure.  I can pass the sp the tasknumber, and have the sp return a table.  
Juan VelasquezAuthor Commented:
I've decided to dump everything into a table.  I will be performing the processing on the SQL server.   Thanks again
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now