Slow query in SQL Server with LEFT OUTER JOIN

Question

I have the query below. When I use LEFT OUTER JOIN my query is very slow. It took more than 1 minute for the query to complete. Is there a way I can improve query speed while still using LEFT OUTER JOIN?

SELECT RISK.RA_HAZ_ID,
                    RISK.RA_CTRL_ID,
                    RISK.RA_HAZ_HAZARD_NAME,
                    RISK.RA_CTRL_CONTROL_NAME,
                    RISK.RA_CTRL_CONTROL_ID,
                    RISK.RA_IS_ESCALATED,
                    RISK.RA_IS_CONSEQUENCE,
                    RISK.RA_CTRL_OUTCOME,
                    RISK.RA_HAZ_IS_ALARP,
                    RISK.RA_HAZ_CONSEQUENCE,
                    RISK.RA_STEP_ORDER,
                    RISK.RA_STEP_NAME,
                    RISK.RA_HAZ_TYPE_NAME,
                    RISK.RA_CTRL_IS_REQUIRED,
                    RISK.RA_CTRL_IS_PRE_SELECTED,
                    RISK.RA_CTRL_IS_SELECTED,
                    RISK.RA_HAZ_SORT_ORDER,
                    RISK.RA_HAZ_COMMENT,
                    RISK.RA_CONTROL_COMMENT,
                    RISK.INIT_RISK,
                    RISK.RES_RISK,
                    RISK.RA_MATRIX_ID,
                    RISK.RES_RISK_LEVEL,
                    RISK.INIT_RISK_LEVEL,
                    MAX(RISK.RA_CTRL_ASSIGNED_FULL_NAME) AS RA_CTRL_ASSIGNED_FULL_NAME
FROM PRINT_RISK_ASSESSMENT_DETAILS RISK
LEFT OUTER JOIN PRINT_JSA_REFERENCE JSA ON JSA.OWNING_ACTIVITY_ID = RISK.OWNING_ACTIVITY_ID
WHERE JSA.ACTIVITY_ID = 10977 OR RISK.OWNING_ACTIVITY_ID = 10977
GROUP BY RISK.RA_HAZ_ID,
            RISK.RA_CTRL_ID,
            RISK.RA_HAZ_HAZARD_NAME,
            RISK.RA_CTRL_CONTROL_NAME,
            RISK.RA_CTRL_CONTROL_ID,
            RISK.RA_IS_ESCALATED,
            RISK.RA_IS_CONSEQUENCE,
            RISK.RA_CTRL_OUTCOME,
            RISK.RA_HAZ_IS_ALARP,
            RISK.RA_HAZ_CONSEQUENCE,
            RISK.RA_STEP_ORDER,
            RISK.RA_STEP_NAME,
            RISK.RA_HAZ_TYPE_NAME,
            RISK.RA_CTRL_IS_REQUIRED,
            RISK.RA_CTRL_IS_PRE_SELECTED,
            RISK.RA_CTRL_IS_SELECTED,
            RISK.RA_HAZ_SORT_ORDER,
            RISK.RA_HAZ_COMMENT,
            RISK.RA_CONTROL_COMMENT,
            RISK.INIT_RISK,
            RISK.RES_RISK,
            RISK.RA_MATRIX_ID,
            RISK.RES_RISK_LEVEL,
            RISK.INIT_RISK_LEVEL
  ORDER BY RISK.RA_STEP_ORDER,
            RISK.RA_HAZ_SORT_ORDER,
            RISK.RA_HAZ_HAZARD_NAME,
            RISK.RA_HAZ_TYPE_NAME DESC,
            RISK.RA_CTRL_IS_REQUIRED DESC,
            RISK.RA_CTRL_IS_PRE_SELECTED DESC,
            RISK.RA_CTRL_CONTROL_NAME

I want to improve query speed while still using LEFT OUTER JOIN

That *huge* `GROUP BY` clause doesn't look right, if I am honest. — Thom A, Oct 26 '22 at 12:28
Try reading through [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055), particularly through part about "If you're asking about query performance" and then edit this post so we can actually help you. — Aarlaneth, Oct 26 '22 at 12:33
[How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) Before asking about SQL performance, research it & research asking about it. [ask] [Help] PS [mre] — philipxy, Oct 26 '22 at 13:10
[How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) [When and why are database joins expensive?](https://stackoverflow.com/q/173726/3404097) [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) [Asking query performance questions](https://dba.meta.stackexchange.com/q/3034/43932) — philipxy, Oct 26 '22 at 13:10

score 0 · Answer 1 · answered Oct 26 '22 at 12:42

You can first see if you have indexes over important fields JSA.ACTIVITY_ID, JSA.OWNING_ACTIVITY_ID and RISK.OWNING_ACTIVITY_ID. If not, consider creating it.

Since you are grouping over the entiry table, if RISK has an important amount of row, it will be slow.

Slow query in SQL Server with LEFT OUTER JOIN

1 Answers1