Can someone help to optimize SQL Query

Question

SELECT RC.Name AS RiskCategory,
( SELECT 
        SUM(CASE WHEN IA.ImpactLevel = 'High' THEN 1 ELSE 0 END)
        FROM
        Rpt_ImpactAssess IA
        JOIN 
        Rpt_Risk R 
        ON
        IA.FKRiskID  =  R.RiskID
        WHERE
        R.RiskID IN
        (
          SELECT FKRiskID    
          FROM 
          Rpt_Impact 
          WHERE FKItemID =38
        )
        AND
        R.RiskCatrogry = RC.Name        
    )AS High_Impact_Risks

From RM_RiskCategories RC
WHERE
RC.Name <> 'All'
GROUP BY 
RC.Name 
ORder By 
RC.Name DESC 

Answer 1

Thanks for your help and answers. With your help I am able to figure out optemise my query as follow:

SELECT RC.Name AS RiskCategory, SUM(CASE WHEN IA.ImpactLevel = 'High' THEN 1 ELSE 0 END) AS High_Impact_Risks From RM_RiskCategories RC LEFT JOIN Rpt_Risk R ON R.RiskCatrogry = RC.Name LEFT JOIN Rpt_ImpactAssess IA ON IA.FKRiskID = R.RiskID AND R.RiskID IN ( SELECT FKRiskID
FROM Rpt_Impact WHERE FKItemID =38 ) WHERE RC.Name <> 'All' GROUP BY RC.Name ORder By RC.Name DESC

Answer 2

I'm fairly certain this produces the same result, try it:

SELECT RC.Name AS RiskCategory,
       SUM(CASE WHEN IA.ImpactLevel = 'High' 
                THEN 1 
                ELSE 0 
           END)
FROM RM_RiskCategories RC
LEFT JOIN Rpt_Risk R ON R.RiskCatrogry = RC.Name 
LEFT JOIN Rpt_ImpactAssess IA ON IA.FKRiskID = R.RiskID
WHERE RC.Name <> 'All' 
  AND ( R.RiskID IS NULL 
     OR R.RiskID IN (SELECT FKRiskID    
                     FROM Rpt_Impact 
                     WHERE FKItemID = 38) )
GROUP BY RC.Name 
ORDER BY RC.Name DESC 

Since you need all the categories (a point I initially missed) your original query might actually be a pretty good way of doing it - I'm really not a fan of using LEFT JOIN ... WHERE pk IS NULL OR pk = something like I have had to above - so you definitely need to benchmark the above to see if it is actually any better.

You could turn that subquery into a JOIN, but I'm not sure there would be any performance gain. Still might be worth a test, remove the subquery from the WHERE clause, and add another LEFT JOIN:

LEFT JOIN Rpt_ImpactAssess IA ON IA.FKRiskID = R.RiskID
WHERE ... AND (R.RiskID IS NULL OR RI.FKItemID = 38)

Answer 3

Check if this gives you the expected result:

SELECT RC.Name AS RiskCategory,
SUM(CASE WHEN IA.ImpactLevel = 'High' THEN 1 ELSE 0 END) AS High_Impact_Risks
From RM_RiskCategories RC LEFT JOIN Rpt_Risk R
ON R.RiskCatrogry = RC.Name
INNER JOIN Rpt_ImpactAssess IA
ON IA.FKRiskID  =  R.RiskID
AND R.RiskID IN
    (
      SELECT FKRiskID    
      FROM 
      Rpt_Impact 
      WHERE FKItemID =38
    )
WHERE
RC.Name <> 'All'
GROUP BY 
RC.Name 
ORder By 
RC.Name DESC

Some additional things that I would suggest you to check and add if they do not exist are:

1. Clustered index on Rpt_Impact(FKItemID)
2. Clustered index on Rpt_Risk(RiskID)
3. Clustered index on RM_RiskCategories(Name)
4. Non Clustered Index on Rpt_Risk(RiskCatrogry)
5. Clustered index on Rpt_ImpactAccess(FKRiskID)
6. Non Clustered index on Rpt_ImpactAccess(FKRiskID) INCLUDE(ImpactLevel)

Answer 4

try this,sorry for syntax if any.it will be definitely faster then that sub-query .also try creating relevant index.also knowing this that,which table return how much row when join then accordingly you can break them in CTR.sometime it help.Also you have very correctly use sum instead of count

SELECT RC.Name AS RiskCategory,SUM(CASE WHEN IA.ImpactLevel = 'High' THEN 1 ELSE 0 END)High_Impact_Risks
From RM_RiskCategories RC
inner join Rpt_Risk R on  R.RiskCatrogry = RC.Name 
inner join Rpt_ImpactAssess IA on IA.FKRiskID  =  R.RiskID
inner join dbo.Rpt_Impact R1 on R.RiskID=R1.FKRiskID and R1.FKItemID =38
WHERE
RC.Name <> 'All'
GROUP BY 
RC.Name 
ORder By 
RC.Name DESC