Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Speeding up correlated subquery

Hello,
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

0
chtullu135
Asked:
chtullu135
  • 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.
0
 
lluddenCommented:
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.
0
 
chtullu135Author 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.  
0
 
chtullu135Author Commented:
I've decided to dump everything into a table.  I will be performing the processing on the SQL server.   Thanks again
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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