由于这个问题不断引起人们的关注,我将在这里进行总结,这样新来的人就不必经历历史了:
JOIN table t ON t.member = @value1 OR t.member = @value2 -- this is slow as hell
JOIN table t ON t.member = COALESCE(@value1, @value2) -- this is blazing fast
-- Note that here if @value1 has a value, @value2 is NULL, and vice versa
我意识到这可能不是每个人的问题,但是通过强调ON子句的敏感性,它可以帮助您朝正确的方向看。无论如何,原始文本都在这里供将来的人类学家使用:
原始文本
考虑以下简单查询(仅涉及3个表)
SELECT
l.sku_id AS ProductId,
l.is_primary AS IsPrimary,
v1.category_name AS Category1,
v2.category_name AS Category2,
v3.category_name AS Category3,
v4.category_name AS Category4,
v5.category_name AS Category5
FROM category c4
JOIN category_voc v4 ON v4.category_id = c4.category_id and v4.language_code = 'en'
JOIN category c3 ON c3.category_id = c4.parent_category_id
JOIN category_voc v3 ON v3.category_id = c3.category_id and v3.language_code = 'en'
JOIN category c2 ON c2.category_id = c3.category_id
JOIN category_voc v2 ON v2.category_id = c2.category_id and v2.language_code = 'en'
JOIN category c1 ON c1.category_id = c2.parent_category_id
JOIN category_voc v1 ON v1.category_id = c1.category_id and v1.language_code = 'en'
LEFT OUTER JOIN category c5 ON c5.parent_category_id = c4.category_id
LEFT OUTER JOIN category_voc v5 ON v5.category_id = c5.category_id and v5.language_code = @lang
JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
(
l.category_id = c4.category_id OR
l.category_id = c5.category_id
)
WHERE c4.[level] = 4 AND c4.version_id = 5
这是一个非常简单的查询,唯一令人困惑的部分是最后一个类别联接,之所以这样,是因为类别级别5可能存在或可能不存在。在查询结束时,我正在寻找每个产品ID(SKU ID)的类别信息,这就是很大的表category_link出现的地方。最后,表#Ids只是一个包含10'000 ID的临时表。 br />
执行后,我得到以下实际执行计划:
如您所见,几乎90%的时间都花在了嵌套循环(内部联接)。以下是有关这些嵌套循环的更多信息:
请注意,表名不完全匹配,因为我编辑了查询表名以提高可读性,但是很容易匹配(ads_alt_category =类别)。有什么方法可以优化此查询?还要注意,在生产环境中,临时表#Ids不存在,它是传递给存储过程的具有相同10,000个ID的表值参数。
其他信息:
category_id和parent_category_id上的类别索引
category_id,language_code上的category_voc索引
sku_id,category_id上的category_link索引
编辑(已解决)
正如公认的答案所指出的那样,问题出在category_link JOIN中的OR子句。但是,接受的答案中建议的代码非常慢,甚至比原始代码还慢。更快,更清洁的解决方案就是简单地用以下命令替换当前的JOIN条件:
JOIN category_link l on l.sku_id IN (SELECT value FROM @p1) AND l.category_id = COALESCE(c5.category_id, c4.category_id)
此分钟调整是最快的解决方案,已针对双联接进行了测试从公认的答案中提取出来,并按照Valverij的建议进行交叉测试。
#1 楼
问题似乎出在代码的这一部分:连接条件中的or
总是可疑的。一种建议是将其分成两个联接:JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
(
l.category_id = c4.category_id OR
l.category_id = c5.category_id
)
然后,您必须修改查询的其余部分来处理此问题。 。 。例如
coalesce(l1.sku_id, l2.sku_id)
子句中的select
。评论
通过对特定联接进行大量过滤,我还将测试将JOIN更改为CROSS APPLY,并将IN切换为APPLY的WHERE子句中的EXISTS。
–valverij
13年4月17日在19:52
谢谢戈登,我会在早上测试这第一件事。 @Valverij,我对交叉申请并不熟悉,您能否在适当的答案中更详细地描述您的解决方案,所以如果事实证明是最快的方案,我可以投票吗?
–路易斯·法拉罗
13年4月17日在20:23
我接受这个答案,因为这是第一个向我指出问题的答案。但是,建议的解决方案非常慢,甚至比原始代码还慢。但是,知道OR子句是问题所在,只需将其替换为ON l.category_id = ISNULL(c5.category_id,c4.category_id即可解决问题。
–路易斯·法拉罗
13年4月18日在9:34
@LuisFerrao。 。 。感谢您提供其他信息。知道coalesce()将优化器推向正确的方向很有用。
–戈登·利诺夫(Gordon Linoff)
13年4月18日在13:28
#2 楼
正如另一位用户提到的,此联接可能是原因:JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
(
l.category_id = c4.category_id OR
l.category_id = c5.category_id
)
除了将其拆分为多个联接外,您还可以尝试
CROSS APPLY
CROSS APPLY (
SELECT [some column(s)]
FROM category_link x
WHERE EXISTS(SELECT value FROM #Ids WHERE value = x.sku_id)
AND (x.category_id = c4.category_id OR x.category_id = c5.category_id)
) l
从上面的MSDN链接:
表值函数起作用作为右输入,外部表表达式用作左输入。从左输入开始为每一行评估右输入,并将产生的行合并为最终输出。
基本上,
APPLY
就像一个子查询,它首先过滤掉右边的记录,然后将其应用到您的其余查询中。本文很好地解释了它的含义和使用时间:http://explainextended.com/2009/07/ 16 / inner-join-vs-cross-apply /
但是要注意,
CROSS APPLY
的性能并不总是比INNER JOIN
快。在许多情况下,它可能大致相同。不过,在极少数情况下,我实际上看到它的速度较慢(再次,这完全取决于您的表结构和查询本身)。作为一般经验法则,如果我发现自己加入一个带有太多条件语句的表,那么我倾向于使用
APPLY
还有趣的一点:
OUTER APPLY
的作用类似于LEFT JOIN
另外,请注意我的选择使用
EXISTS
而不是IN
。在子查询上执行IN
时,请记住,即使找到了值,它也会返回整个结果集。但是,使用EXISTS
,它将在找到匹配项时立即停止子查询。 评论
我彻底测试了此解决方案。在编写时,它的运行速度很慢,但是您忘记应用开始创建消息时所使用的建议。用x.cat = ISNULL(c5.cat,c4.cat)代替AND x.cat = c4.cat或x.cat = c5.cat并摆脱了IN子句,这使它成为第二快的解决方案,值得upvote,因为它非常有用。
–路易斯·法拉罗
13年4月18日在9:38
谢谢。实际上不应该在IN行(无法决定使用IN还是坚持使用OR),我将其删除。
–valverij
13年4月18日在12:14
评论
我们需要查看其余的查询计划。只是一句话:由于有许多依赖连接,基数估计错误很有可能出现。最常见的是,基数低估了查询性能。
执行计划是否对索引提出建议?另外,不要忘记您可以在临时表上设置主键和索引(更多信息在这里)
@rbarry如果尝试了当前解决方案后仍无所求,我会改善这个问题
如何使用UNION复制查询并摆脱OR