TL; DR

由于这个问题不断引起人们的关注,我将在这里进行总结,这样新来的人就不必经历历史了:

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的建议进行交叉测试。

评论

我们需要查看其余的查询计划。

只是一句话:由于有许多依赖连接,基数估计错误很有可能出现。最常见的是,基数低估了查询性能。

执行计划是否对索引提出建议?另外,不要忘记您可以在临时表上设置主键和索引(更多信息在这里)

@rbarry如果尝试了当前解决方案后仍无所求,我会改善这个问题

如何使用UNION复制查询并摆脱OR

#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