Speeding up correlated subquery

Posted on 2011-03-11
Last Modified: 2012-05-11
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

Question by:chtullu135
  • 2
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35110612
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.
LVL 18

Expert Comment

ID: 35110710
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.

Author Comment

ID: 35111657
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.  

Author Closing Comment

ID: 35129854
I've decided to dump everything into a table.  I will be performing the processing on the SQL server.   Thanks again

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question