使用LEFT OUTER JOIN的SQL Server中的慢速查询

Question

我有下面的查询。当我使用LEFT OUTER JOIN时，我的查询速度非常慢。查询花了1分钟多的时间才完成。有没有一种方法可以提高查询速度，同时仍然使用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

我想在使用LEFT OUTER JOIN 的同时提高查询速度

Answer 1

您可以首先查看重要字段JSA.ACTIVITY_ID、JSA.OWNING_ACTIVITY_ID和RISK.OWNING_ACTIVITY_ID是否有索引。如果没有，请考虑创建它。

由于您在整张表上进行分组，因此如果RISK有大量的行，则速度会很慢。