在子查询中包含 NOT LIKE 或当前行引用时，SQL 查询速度极慢

Question

我在 SQL Server 2008 R2 中有以下查询:

SELECT   
    DateName(month, DateAdd(month, [sfq].[fore_quart_month], -1)) AS [Month],
    [sfq].[fore_quart_so_rev] AS [Sales Orders Revenue],
    [sfq].[fore_quart_so_mar] AS [Sales Orders Margin],
    [sfq].[fore_quart_mac_rev] AS [MAC Revenue],
    [sfq].[fore_quart_mac_mar] AS [MAC Margin],
    [sfq].[fore_quart_total_rev] AS [TOTAL Revenue],
    [sfq].[fore_quart_total_mar] AS [TOTAL Margin],
    (SELECT SUM([FORE].[Revenue])
     FROM [SO_Opportunity][SO]
     LEFT JOIN [SO_Type] ON [SO].[SO_Type_RecID] = [SO_Type].[SO_Type_RecID]
     LEFT JOIN [SO_Opportunity_Audit][soa] ON [so].[Opportunity_RecID] = [soa].[Opportunity_RecId]
     LEFT JOIN [SO_Opportunity_Audit_Value][soav] ON [soa].[SO_Opportunity_Audit_RecId] = [soav].[SO_Opportunity_audit_recid]
     LEFT JOIN [SO_Forecast_dtl] [FORE] ON [SO].[Opportunity_RecID] = [FORE].[Opportunity_RecID]
     WHERE ([SO_Type].[Description] NOT LIKE '%MAC%' AND [SO_Type].[Description] NOT LIKE '%Maint%')
       AND YEAR([soa].[last_Updated_utc]) = @p_year AND MONTH([soa].[last_updated_utc]) = [sfq].[fore_quart_month]
       AND [soav].[audit_value] LIKE '%Closed - Won%' AND [soav].[audit_token] = 'new_value'
       AND [so].[SO_Opp_Status_RecID] = 7) AS [Rev]
FROM 
    [authmanager2].[dbo].[sales_forecast_quarterly][sfq]
WHERE 
    [sfq].[fore_quart_year] = @p_year AND [sfq].[fore_quart_loc] = 'w'
ORDER BY 
    [sfq].[fore_quart_month]

问题是，当在子查询中包含 NOT LIKE 过滤器和 [sfq].[fore_quart_month] 引用时，它运行得非常慢(几分钟)，但是如果我删除 NOT LIKE 过滤器或者如果我硬设置值而不是使用 [sfq].[fore_quart_month] (这显然意味着每个计算都将使用错误的月份，除了我硬编码的月份) ，然后查询会在不到一秒的时间内运行。

有什么建议吗？

Answer 1

两端带有通配符的LIKE 查询非常慢。示例:%MAC%

如果您确实需要对其进行搜索，请考虑创建一个持久化的计算 bool 字段并对其进行搜索。像这样的东西:

ALTER TABLE SO_Type
ADD IsMac AS CASE WHEN [Description] LIKE '%MAC%' THEN 1 ELSE 0 END PERSISTED
GO

或

作为替代方案，每当插入数据时设置 ISMac