Speed up SQL query performance with nested queries

Question

Could anyone help me speed this query up? It currently take 17 minutes to run but does return the correct data and it populates a subform in MS Access. Functions in the rest of the VBA are declared as long to try to speed up more.

Here's the full query:

SELECT     lots of things
FROM       (((((((((((((((ngstest
INNER JOIN patients
ON         ngstest.internalpatientid = patients.internalpatientid)
INNER JOIN referral
ON         ngstest.referralid = referral.referralid)
INNER JOIN checker
ON         ngstest.bookby = checker.check1id)
INNER JOIN ngspanel
ON         ngstest.ngspanelid = ngspanel.ngspanelid)
LEFT JOIN  ngspanel AS ngspanel_1
ON         ngstest.ngspanelid_b = ngspanel_1.ngspanelid)
INNER JOIN status
ON         ngstest.statusid = status.statusid)
INNER JOIN dbo_patient_table
ON         patients.patientid = dbo_patient_table.patienttrustid)
LEFT JOIN  dna
ON         ngstest.dna = dna.dnanumber)
INNER JOIN status AS status_1
ON         patients.s_statusoverall = status_1.statusid)
LEFT JOIN  gw_gendertable
ON         dbo_patient_table.genderid = gw_gendertable.genderid)
LEFT JOIN  ngswesbatch
ON         ngstest.wesbatch = ngswesbatch.ngswesbatchid)
LEFT JOIN  checker AS checker_1
ON         ngstest.check1id = checker_1.check1id)
LEFT JOIN  checker AS checker_2
ON         ngstest.check2id = checker_2.check1id)
LEFT JOIN  checker AS checker_3
ON         ngstest.check3id = checker_3.check1id)
LEFT JOIN  ngspanel AS ngspanel_2
ON         ngstest.ngspanelid_c = ngspanel_2.ngspanelid)
LEFT JOIN  checker AS checker_4
ON         ngstest.check4id = checker_4.check1id
WHERE    ((ngstest.referralid IN
   (SELECT referralid FROM referral
      WHERE grouptypeid = 14)
        AND ngstest.ngstestid IN
           (SELECT ngstest.ngstestid
              FROM ngsanalysis
        INNER JOIN ngstest
                ON ngsanalysis.ngstestid = ngstest.ngstestid 
             WHERE ngsanalysis.pedigree = 3302) )
               AND status.statusid = 1202218800)
ORDER BY   ngstest.priority,
           ngstest.daterequested;

The two nested queries are strings from elsewhere in the code so are called in the vba as " & includereferralls & " And " & ParentsStatusesFilter & "

They are:

    ParentsStatusesFilter = "NGSTest.NGSTestID in 
         (SELECT NGSTest.NGSTestID 
          FROM NGSAnalysis 
          INNER JOIN NGSTest 
          ON NGSAnalysis.NGSTestID = NGSTest.NGSTestID 
          WHERE NGSAnalysis.Pedigree IN (3302,3303,3304)"

And

    includereferrals = "NGSTest.ReferralID 
         (SELECT referralid FROM referral WHERE referral.grouptypeid = 14)"

The query needs to remain readable (and therefore editable) so can't use things like Distinct, Group By or contain any Unions. Have tried Exists instead of In for the nested queries but that stops it from actually filtering the results.

WHERE EXISTS (SELECT NGSTest.NGSTestID 
              FROM NGSAnalysis 
              INNER JOIN NGSTest 
              ON NGSAnalysis.NGSTestID = NGSTest.NGSTestID 
              WHERE NGSAnalysis.Pedigree IN (3302,3303,3304)

Do you know which statements are the slowest? Can you get the query plan(s)? Most likely indexes will be what's needed. Not sure why you think GROUP BY and UNIONs are not readable, I'd expect anyone with permissions to edit this kind of code should understand those basic functions, or at least have the wherewithal to look them up. — Joe Shark
– Joe Shark, Commented Oct 23, 2020 at 12:14
"The query needs to remain readable (and therefore editable) so can't use things like Distinct, Group By or contain any Unions." - Why are those things not readable? They're tools in the SQL language. — WSC
– WSC, Commented Oct 23, 2020 at 12:18
It MS Access, you don't have a lot of options for performance — Nick.Mc
– Nick.Mc, Commented Oct 23, 2020 at 12:27
So this query brings back the status of a tick box on the subform, if the SQl used to populate the subform has any of these things in, the results become non editable on the subform, which removes some of the needed functionality (ability to check and uncheck the tick box) This explains some more allenbrowne.com/ser-61.html — Erin Peat
– Erin Peat, Commented Oct 23, 2020 at 12:43

Randall Porter · Accepted Answer · 2020-12-30 20:09:46Z

1

So the exist clause you have there isn't tied to the outer query which would run similar to just added 1 = 1 to the where clause. I took your where clause and converted it. It should look something like this...

WHERE EXISTS (
    SELECT referralid 
    FROM referral
    WHERE grouptypeid = 14 AND ngstest.referralid = referral.referralid)
AND EXISTS (
    SELECT ngsanalysis.ngstestid
    FROM ngsanalysis
    WHERE ngsanalysis.pedigree IN (3302,3303,3304) AND ngstest.ngstestid = ngsanalysis.ngstestid 
)
AND status.statusid = 1202218800

Adding exists will speed it up a bit, but the the bulk of the slowness is the left joins. Access does not handle the left joins as well as SQL Server does. Change all your joins to inner joins and you will see the query runs very fast. This is obviously not ideal since some relationships are optional. What I have done to get around this is add a default record that replaces a null relationship.

Here is what that looks like for you: In the checker table you could add a record that represents a null value. So put a record into the checker table with check1id of -1 or 0. Then default check1id, check2id, check3id on ngstest to -1 or 0. You will need to do that type of thing for all tables you need to left join on.

answered Dec 30, 2020 at 20:09

Randall Porter

435 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erin Peat Over a year ago

Thank you so much! I will try this : )

Collectives™ on Stack Overflow

Speed up SQL query performance with nested queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related