SQL中的CASE语句和JOIN语句哪个更有效率？

Question

在我的MS SQL Server数据库中，我正在基于一个列中的各种不同代码提取交易数据。为了更有效率，以下哪种方法更好：

• 针对每个WHERE子句中的代码，重复多次连接相同的表
• 在整个表上执行多个CASE语句（如下所示）
• 在整个表上执行多个CASE语句，但通过WHERE SubsidCde IN ('AA'，'BA'，'BB'等)限制它

我们每秒运行的查询非常多，即使我尝试了这三种方法，也没有得到明确的结果。

SELECT
    SUM(CASE WHEN Subsid_Cde = 'AA' THEN Trans_Amt END),0) [AA],
    SUM(CASE WHEN Subsid_Cde = 'BA' THEN Trans_Amt END),0) [BA],
    SUM(CASE WHEN Subsid_Cde = 'BB' THEN Trans_Amt END),0) [BB]
FROM
    Transactions

--  There are 8 more rows like this, using a different code for each line

- kd7iwp

---

请参阅 http://sqlblog.com/blogs/linchi_shea/archive/2011/04/04/performance-impact-the-cost-of-doing-small-lookups-in-a-large-batch.aspx - Zephyr was a Friend of Mine

Answer 1

如果您正在对Subsid_Cde字段的所有可能（或大多数）值求和，则使用CASE语句更快，因为它在汇总总和时不会多次扫描表。如果您只需要查找可能的Subsid_Cde字段的一小部分，则单独的选择/连接（以及Subsid_Cde索引）将更快。

您需要学习阅读执行计划，然后您就能够自己找出这些问题。

此外，作为替代方案，您可以在PIVOT子句中将Subsid_Cde进行GROUP BY处理（搜索PIVOT MS SQL SERVER 2005）。

- Andriy Volkov

Answer 2

3是你最好的选择。它易于阅读，以后修改也很容易，并且应该使用您定义并希望使用的索引（但仍需检查）。

--1有时您必须加入同一张表。但这不是其中之一，每次需要包含新的Subsid_Cde时都加入会使SQL变得难以阅读，而实际上并没有获得任何好处。

--2交易表往往会变得非常大，因此您永远不想扫描整个表。 因此，#2肯定行不通，除非您在查询中使用的代码无论如何都会将所有行返回给您。

- hythlodayr

Answer 3

使用此代码：

SELECT  (
        SELECT  SUM(Trans_Amt)
        FROM    Transactions
        WHERE   Subsid_Cde = 'AA'
        ) AS sum_aa,
        (
        SELECT  SUM(Trans_Amt)
        FROM    Transactions
        WHERE   Subsid_Cde = 'BB'
        ) AS sum_bb

在编写程序时，不要使用外部的FROM或WHERE子句。

在SQL Server 2005+中，可以使用以下代码：

SELECT  [AA], [BB]
FROM    (
        SELECT  trans_amt, subsid_cde
        FROM    transactions
        ) q
PIVOT   (
        SUM(trans_amt)
        FOR subsid_cde IN (['AA'], ['BB'])
        )

- Quassnoi

Answer 4

当你执行这样的任务时，有一些非常重要的考虑因素。

• CASE的限制
• CASE带来的所有权

CASE是一个表达式而不是语句。

代码将为每个WHEN重新评估“所有相关内容”。

在您的CASE实例中，它正在针对原始表进行查找，并且不会超出一个WHEN进行评估，因此它不会重新查询，而是会根据您的索引执行一次扫描或查找并返回结果，并为每个CASE执行该操作，但它必须为JOIN执行该操作，您的结果编码到case中意味着系统不必搜索值以检查。

最后一点非常重要，因为它对业务和你作为编码人员都有一些重大影响。

有一篇非常好的文章是由Aaron Bertrand撰写的，详细介绍了“Case的不可告人的秘密”。 https://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression

总结来说，它可能会为每个WHEN重新查询，这听起来并不太糟糕，但如果你的SQL查询受到增长因子的影响很差，你的CASE解决方案将无法扩展，如果你使用任何在调用时重新评估的函数 [RAND()是罪魁祸首]，它不仅在某些情况下产生不一致的结果，而且如果你的查询具有涉及N的幂的“大O符号”（这是一种非常技术化的方式，表示使用排序、联合、去重或其他形式的被动排序调用或更糟糕的是参与过时的计划而没有最新的统计数据），那么它对输出的影响可能会很大。这意味着CASE可能会使已经遭受指数迭代问题的怪物查询时间加倍、三倍或者像我喜欢称之为“(N)WHEN”。

您的CASE需要进行“技术维护”（每当实施新代码时，您都需要重新编码以考虑这一点），而JOIN需要进行“用户维护”（用户更改查找表，您的查询将更新，无需您承担任何责任）。

“技术维护”会让您陷入业务错误后果的风险中。

您总是希望编写解决方案，以最小化您对数据吞吐量的所有权，因为您不想在法庭上谈论您涉及的业务逻辑，而想待在家里和家人一起度假，而不用接到关于他们刚添加的“CC”并要求在您的报告中进行求和的电话。

在CASE的情况下，您已将自己编码成执行业务逻辑的局面，而业务应该在一个查找表中进行决策，这个查找表应该是JOIN。

简而言之，当业务方推出新代码时，您的报告看不到该输出，这将导致您的生产受损，特别是如果您度假期间发现自己是唯一知道您的CASE存在的人，您可能不会认为它是一个负担，但您没有考虑长远，因为记忆在长期内会变得模糊，您可能会发现自己在搜索您的CASE并在未来添加它，这将使您感到非常不爽。

为了节省4秒钟，您正在个人上给自己带来不便。

为了速度而牺牲自己并不是良好的编程实践。

- Izalias