How can I optimize these T-SQL queries?

Question

I have four sql variables that were calculated in the exact same way, except one condition on each query :

  C.CenterID = @CenterID
  C.CenterNo IN (SELECT CenterNo from Registry.Vol1_5)
  C.CenterNo IN (SELECT CenterNo from Registry.Vol6_10)
  C.CenterNo IN (SELECT CenterNo from Registry.Vol11)

My code is here:

DECLARE @MyCenterAllComplications INT;
DECLARE @Total5AllComplications INT;
DECLARE @Total6_10AllComplications INT;
DECLARE @Total11AllComplications INT;   


SET @MyCenterAllComplications = (SELECT count(*) FROM ECLS.Runs R

 INNER JOIN Registry.Patients P on R.PatientId = P.PatientId
 INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
 INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId

where 1 = 1

 AND NOT R.RunId IN (select RD.RunId from ECLS.RunDetails RD 
  INNER JOIN ECLS.ModeCodes MC ON RD.Mode = MC.Code AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
  )
 AND C.CenterID = @CenterID
 AND R.SupportType = @SupportType
 AND NOT R.CompletedBy IS NULL
 AND NOT R.TimeOn IS NULL
 AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
 AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30
); 

SET @Total5AllComplications = (SELECT count(*) FROM ECLS.Runs R

 INNER JOIN Registry.Patients P on R.PatientId = P.PatientId
 INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
 INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId

where 1 = 1

 AND NOT R.RunId IN (select RD.RunId from ECLS.RunDetails RD 
  INNER JOIN ECLS.ModeCodes MC ON RD.Mode = MC.Code AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
  )
 AND C.CenterNo IN (SELECT CenterNo from Registry.Vol1_5)
 AND R.SupportType = @SupportType
 AND NOT R.CompletedBy IS NULL
 AND NOT R.TimeOn IS NULL
 AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
 AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30
);

SET @Total6_10AllComplications = (SELECT count(*) FROM ECLS.Runs R

 INNER JOIN Registry.Patients P on R.PatientId = P.PatientId
 INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
 INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId

where 1 = 1

 AND NOT R.RunId IN (select RD.RunId from ECLS.RunDetails RD 
  INNER JOIN ECLS.ModeCodes MC ON RD.Mode = MC.Code AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
  )
 AND C.CenterNo IN (SELECT CenterNo from Registry.Vol6_10)
 AND R.SupportType = @SupportType
 AND NOT R.CompletedBy IS NULL
 AND NOT R.TimeOn IS NULL
 AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
 AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30
);

 SET @Total11AllComplications = (SELECT count(*) FROM ECLS.Runs R

 INNER JOIN Registry.Patients P on R.PatientId = P.PatientId
 INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
 INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId

where 1 = 1

 AND NOT R.RunId IN (select RD.RunId from ECLS.RunDetails RD 
  INNER JOIN ECLS.ModeCodes MC ON RD.Mode = MC.Code AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
  )
 AND C.CenterNo IN (SELECT CenterNo from Registry.Vol11)
 AND R.SupportType = @SupportType
 AND NOT R.CompletedBy IS NULL
 AND NOT R.TimeOn IS NULL
 AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
 AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30
);

How can I calculate all these variables

@MyCenterAllComplications,
@Total5AllComplications,
@Total6_10AllComplications,
@Total11AllComplications

At the same time, by using one query? Thank you in advance for any help.

Answer 1

SELECT count(cID.ID)
     , count(Registry.Vol1_5.CenterNo)
     , count(Registry.Vol6_10.CenterNo)
     , count(on Registry.Vol11.CenterNo)
FROM ECLS.Runs R
INNER JOIN Registry.Patients P 
        on R.PatientId = P.PatientId
       AND R.SupportType = @SupportType
       AND NOT R.CompletedBy IS NULL
       AND NOT R.TimeOn IS NULL
       AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
       AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30
       AND NOT R.RunId IN (select RD.RunId 
                           from ECLS.RunDetails RD 
                           INNER JOIN ECLS.ModeCodes MC 
                            ON RD.Mode = MC.Code 
                           AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
                          )
INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId 
LEFT JOIN (select @CenterID as ID
          ) cID
             on cID.ID                   = C.CenterNo 
LEFT JOIN Registry.Vol1_5
            on Registry.Vol1_5.CenterNo  = C.CenterNo 
LEFT JOIN Registry.Vol6_10
            on Registry.Vol6_10.CenterNo = C.CenterNo 
LEFT JOIN Registry.Vol11
            on Registry.Vol11.CenterNo   = C.CenterNo 

Answer 2

You can combine all these queries into 1 using conditional aggregation with CASE EXPRESSION :

  SELECT @MyCenterAllComplications = SUM(MyCenterAllComplications) ,
         @Total5AllComplications = SUM(Total5AllComplications),
  .......
FROM(
    SELECT CASE WHEN C.CenterID = @CenterID THEN 1 ELSE 0 END as MyCenterAllComplications , 
           CASE WHEN C.CenterNo IN (SELECT CenterNo from Registry.Vol1_5) THEN 1 ELSE 0 END as Total5AllComplications  ,
       ....
    FROM ECLS.Runs R
     INNER JOIN Registry.Patients P on R.PatientId = P.PatientId
     INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
     INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId
    where 1 = 1
     AND NOT R.RunId IN (select RD.RunId from ECLS.RunDetails RD 
      INNER JOIN ECLS.ModeCodes MC ON RD.Mode = MC.Code AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
      )
     AND R.SupportType = @SupportType
     AND NOT R.CompletedBy IS NULL
     AND NOT R.TimeOn IS NULL
     AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
     AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30)

Further than that, you should post the explain plan of this query and tables structure so we can tell you if there are any indexes missing from your tables.

Answer 3

It is not sufficient just know your query, it need to know how to is structured your database.

A general response could be: read the query execution plan and check if an index is required: https://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

To view the execution plan, click the Execution plan tab in the results pane. The graphical execution plan output in SQL Server Management Studio is read from right to left and from top to bottom. Each query in the batch that is analyzed is displayed, including the cost of each query as a percentage of the total cost of the batch.