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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 85

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

724 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