sql服务器-sql SUM或COUNT与CASE-这将更有效

Question

如果我有一个SQL Server 2008 R2数据库表，其主键为subject_id（INT）AND category_id（INT。。。

考虑到以下两个问题，哪一个更有效？我尝试使用客户端统计数据和执行计划输出，但每次运行查询时，统计数据似乎都会有所不同，并且没有指出明显的赢家

查询1:

SELECT SUM( CASE WHEN ( category_id = 1 AND ( bit_mask & 4 ) = 4 ) THEN 1
                 WHEN ( category_id = 2 AND ( bit_mask & 8 ) = 8 ) THEN 1
                 ELSE 0 END )
FROM data_tbl
WHERE subject_id = 12;

查询2:

SELECT COUNT(*)
  FROM data_tbl
 WHERE subject_id = 12
   AND 1 = ( CASE WHEN ( category_id = 1 AND ( bit_mask & 4 ) = 4 ) THEN 1
                  WHEN ( category_id = 2 AND ( bit_mask & 8 ) = 8 ) THEN 1
                  ELSE 0 END );

如果某位SQL大师能够看一眼，立即判断出哪个查询更高效（或者它们是一样的吗？）请告诉我。

提前感谢！

Answer 1

两者都没有。这应该优于两者：

SELECT COUNT(*)
FROM data_tbl
WHERE subject_id = 12
AND (
  (category_id = 1 AND ( bit_mask & 4 ) = 4 ) 
  OR 
  (category_id = 2 AND ( bit_mask & 8 ) = 8 )
)

原因是该查询为优化器提供了使用索引或其他方法处理逻辑的更好机会。您的查询将计数逻辑隐藏在优化器无法访问的计算中。

Answer 2

和往常一样，它主要取决于基数和选择性。我的猜测是，第二个有更好的机会更快。将执行计划与您的真实数据进行核对以确定。

然而，最快的变体可能是这个：

SELECT SUM( CASE WHEN ( category_id = 1 AND ( bit_mask & 4 ) = 4 ) THEN 1
                 WHEN ( category_id = 2 AND ( bit_mask & 8 ) = 8 ) THEN 1
                 ELSE 0 END )
FROM data_tbl
WHERE subject_id = 12
AND category_id in (1, 2);

同样，这只是一个猜测，UNION ALL（每个category_id一个SELECT）可能很容易胜过它。

Answer 3

OK-感谢大家的回复-我测试了提供的建议，以下是查询分析器报告的结果：

查询1:

SELECT SUM( CASE WHEN ( category_id = 1 AND ( bit_mask & 4 ) = 4 ) THEN 1
                 WHEN ( category_id = 2 AND ( bit_mask & 8 ) = 8 ) THEN 1
                 ELSE 0 END )
 FROM data_tbl
WHERE subject_id = 12
  AND category_id in ( 1, 2);

10次试验后的平均"总执行时间"：10.8

查询2:

SELECT COUNT(*)
 FROM data_tbl
WHERE subject_id = 12
  AND ( ( category_id = 1 AND ( bit_mask & 4 ) = 4 ) OR 
        ( category_id = 2 AND ( bit_mask & 8 ) = 8 ) );

10次试验后的平均"总执行时间"：11.6

奇怪的是，在这两种情况下，执行计划看起来完全相同。因此，看起来查询1将是一个与之匹配的查询。