CODEWITHSUNDEEP
sqlvbams-access
Erin Peat
Erin Peat

Reputation: 1

Speed up SQL query performance with nested queries

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)

Upvotes: 0

Views: 277

Answers (1)

Randall Porter
Randall Porter

Reputation: 43

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.

Upvotes: 1

Related Questions