How can I optimize these T-SQL queries?

Question

I have four sql variables that were calculated in exactly the same way, except for one condition for 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 using a single 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)

In addition, you must publish a plan explaining this structure of queries and tables so that we can tell you if there are any indexes in your tables.

Answer 3

, , .

: , : https://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

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