条件聚合性能

Question

让我们有以下数据

 IF OBJECT_ID('dbo.LogTable', 'U') IS NOT NULL  DROP TABLE dbo.LogTable

 SELECT TOP 100000 DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0) datesent 
 INTO [LogTable]
 FROM    sys.sysobjects
 CROSS JOIN sys.all_columns

我想计算行数，去年的行数和过去十年的行数。可以通过使用条件聚合查询或使用子查询来实现，如下所示：

-- conditional aggregation query
SELECT
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-1,GETDATE())
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-10,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable


-- subqueries
SELECT
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-1,GETDATE())
) last_year_cnt,
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-10,GETDATE())
) last_ten_year_cnt

如果你执行查询并查看查询计划，你会看到像这样的内容：

显然，第一个解决方案有更好的查询计划、成本估算，甚至SQL命令看起来更加简明和漂亮。但是，如果你使用 SET STATISTICS TIME ON 测量查询的CPU时间，我得到以下结果（我已经进行了几次测量，结果大致相同）。

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 41 ms.

(1 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 26 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

因此，第二种解决方案的性能略微优于（或与）使用条件聚合的解决方案。如果我们在 datesent 属性上创建索引，差异会变得更加明显。

CREATE INDEX ix_logtable_datesent ON dbo.LogTable(DateSent)

那么第二种解决方案开始使用Index Seek而不是Table Scan，在我的电脑上，查询CPU时间性能下降到16ms。

我的问题有两个：（1）为什么条件聚合解决方案在没有索引的情况下不能至少优于子查询解决方案，（2）是否可以为条件聚合解决方案创建“索引”（或重写条件聚合查询），以避免扫描，或者如果我们关注性能，则条件聚合通常不适用？

附注：我可以说，这种情况对于条件聚合来说相当乐观，因为我们选择了始终导致使用扫描的所有行数。如果不需要所有行数，则具有子查询的索引解决方案没有扫描，而具有条件聚合的解决方案仍然必须执行扫描。

编辑

Vladimir Baranov基本上回答了第一个问题（非常感谢）。然而，第二个问题仍然存在。我可以在StackOverflow上看到使用条件聚合解决方案的答案非常频繁，它们吸引了很多注意力，被认为是最优雅和清晰的解决方案（有时被提议为最有效的解决方案）。因此，我将略微概括问题：

你能给我一个例子，条件聚合明显优于子查询解决方案吗？

为简单起见，假设不存在物理访问（数据在缓冲区高速缓存中），因为今天的数据库服务器仍然将大部分数据保留在内存中。

- Radim Bača

---

这里有一篇精彩的文章，深入探讨了您的问题。似乎在WHERE列上建立索引后，单独的子查询优于条件聚合。 - Tim Biegeleisen

但这并不意味着条件聚合是邪恶的；在某些情况下，它们是必需的，以获得所需的答案。此外，您的性能将取决于表的大小，是否可以容忍添加某个索引等因素。 - Tim Biegeleisen

@TimBiegeleisen 这是一篇不错的博客文章，但它并没有解释其中的区别。我甚至提出了更多问题，因为他也调查了IO，并发现子查询解决方案有几倍的IO（这也是这种情况）。那么条件聚合的减速来自哪里呢？ - Radim Bača

可能是由于COUNT(*)和SUM之间的差异，其中SQL Server只计算行数，而对于所有行，SQL Server添加了1或0。 - Steve Ford

Answer 1

简要说明

• 子查询方法的性能取决于数据分布。
• 条件聚合的性能不取决于数据分布。

子查询方法可能比条件聚合更快或更慢，这取决于数据分布。

如果表具有适当的索引，则子查询可能会从中受益，因为索引只允许扫描表的相关部分而不是全表扫描。具有适当索引对条件聚合方法的好处不大，因为它仍将扫描整个索引。唯一的好处是如果索引比表窄，则引擎需要读取的页面较少。

了解这些信息后，您可以决定选择哪种方法。

---

第一次测试

我创建了一个包含500万行的较大测试表。该表上没有索引。

我使用SQL Sentry Plan Explorer测量了IO和CPU统计信息。我在这些测试中使用了SQL Server 2014 SP1-CU7（12.0.4459.0）Express 64位版。

事实上，您原始的查询的行为与您描述的一样，即尽管读取次数高达3倍，但子查询仍然更快。

在没有索引的表上尝试几次后，我重写了您的条件聚合并添加了变量来保存DATEADD表达式的值。

总体时间变得显着更快。

然后我将SUM替换为COUNT，它又变得稍微快了一些。

最后，条件聚合的速度与子查询的速度几乎相同。

预热缓存 (CPU=375)

SELECT -- warm cache
    COUNT(*) AS all_cnt
FROM LogTable
OPTION (RECOMPILE);

子查询（CPU=1031）

SELECT -- subqueries
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-1,GETDATE())
) last_year_cnt,
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-10,GETDATE())
) last_ten_year_cnt
OPTION (RECOMPILE);

原始条件聚合 (CPU=1641)

SELECT -- conditional original
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-1,GETDATE())
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-10,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

使用变量进行条件聚合 (CPU=1078)

DECLARE @VarYear1 datetime = DATEADD(year,-1,GETDATE());
DECLARE @VarYear10 datetime = DATEADD(year,-10,GETDATE());

SELECT -- conditional variables
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > @VarYear1
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > @VarYear10
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

使用变量和COUNT进行条件聚合，而不是SUM (CPU=1062)

SELECT -- conditional variable, count, not sum
    COUNT(*) AS all_cnt,
    COUNT(CASE WHEN datesent > @VarYear1
             THEN 1 ELSE NULL END) AS last_year_cnt,
    COUNT(CASE WHEN datesent > @VarYear10
             THEN 1 ELSE NULL END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

根据这些结果，我的猜测是CASE为每一行调用了DATEADD函数，而WHERE聪明地只计算了一次。此外，COUNT要比SUM稍微高效一点。

最后，条件聚合仅比子查询慢一点（1062 vs 1031），可能是因为WHERE本身更加高效，而且WHERE过滤掉了相当多的行，所以COUNT需要处理的行数较少。

---

在实际工作中，我会使用条件聚合，因为我认为读取的次数更重要。如果您的表很小，可以适应并保留在缓冲池中，则任何查询都对终端用户快速。但是，如果表比可用内存大，则从磁盘读取将显着减慢子查询的速度。

---

第二个测试

另一方面，尽早过滤行也很重要。

这是测试的一个小变化，它演示了这一点。在这里，我将阈值设置为GETDATE() +100年，以确保没有行符合筛选条件。

预热缓存 (CPU=344)

SELECT -- warm cache
    COUNT(*) AS all_cnt
FROM LogTable
OPTION (RECOMPILE);

子查询 (CPU=500)

SELECT -- subqueries
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,100,GETDATE())
) last_year_cnt
OPTION (RECOMPILE);

原始条件聚合（CPU=937）

SELECT -- conditional original
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,100,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

使用变量进行条件聚合 (CPU=750)

DECLARE @VarYear100 datetime = DATEADD(year,100,GETDATE());

SELECT -- conditional variables
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > @VarYear100
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

使用变量和COUNT进行条件聚合，而非SUM（CPU = 750）

SELECT -- conditional variable, count, not sum
    COUNT(*) AS all_cnt,
    COUNT(CASE WHEN datesent > @VarYear100
             THEN 1 ELSE NULL END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

下面是一个带有子查询的计划。您可以看到，在第二个子查询中，0行进入了Stream Aggregate，所有这些行都在Table Scan步骤中被过滤掉了。

因此，子查询再次更快。

第三个测试

这里我更改了上一个测试的过滤条件：所有的>都被替换成了<。结果，条件COUNT计算了所有行，而不是没有行。惊奇吧！条件聚合查询仍然需要750毫秒，而子查询变成了813毫秒，而不是500毫秒。

这里是子查询的计划：

您能给我举一个条件聚合明显优于子查询解决方案的例子吗？

在这里。子查询方法的性能取决于数据分布。条件聚合的性能不取决于数据分布。

子查询方法可能比条件聚合更快或更慢，这取决于数据分布。

知道这一点，您可以决定选择哪种方法。

---

额外细节

如果您将鼠标悬停在Table Scan运算符上，您可以看到不同版本的Actual Data Size。

1. 简单的COUNT(*)：

2. 条件聚合：

3. 第二个测试中的子查询：

4. 测试3中的子查询：

现在清楚了，性能差异很可能是由计划中数据流量的差异引起的。

对于简单的COUNT(*)，不存在输出列表（不需要列值），因此数据大小最小（43MB）。

在条件聚合的情况下，这个数量在测试2和3之间并没有发生改变，始终为72MB。输出列表有一个列datesent。

对于子查询，这个数量会根据数据分布而改变。

- Vladimir Baranov

---

不错的见解。你尝试过对datesent进行索引吗？还是你的结果纯粹没有使用它？ - Radim Bača

在所描述的测试中没有索引。 - Vladimir Baranov

好的，看起来如果你足够关注如何使用条件聚合，那么你就可以实现类似子查询解决方案的性能。然而，如果有索引参与，子查询解决方案会占据主导地位。 - Radim Bača

@RadimBača，你和我的测试使用了一个相当小的表格，适合放入内存中。如果你的表格非常大，引擎必须从磁盘读取页面，那么额外的扫描或查找表格可能会非常昂贵。这也取决于数据分布。如果你有一个索引，并且WHERE条件过滤掉了大部分行，则查询将非常高效。如果WHERE条件保留了大部分行进行处理，则再次从磁盘读取它们可能会很昂贵。 - Vladimir Baranov

谢谢您回答第一个问题。我已经将第二个问题概括了，但是让我们坚持一个适合内存的解决方案。 - Radim Bača

非常出色的答案。我只会稍微修改结论。“子查询方法的性能取决于数据分布和索引的存在。”同样，在条件聚合的情况下可以提到索引。我认为索引的使用对结论很重要，因为条件聚合不能使用任何索引。 - Radim Bača

Answer 2

下面是我的一个例子，在其中针对大型表进行的子查询非常缓慢（约为40-50秒），我得到了使用FILTER（条件聚合）重写查询的建议，这将其加速到了1秒钟。我很惊讶。

现在我总是使用FILTER条件聚合，因为你只需要在大表上联接一次，所有检索都通过FILTER完成。在大表上进行子选择是个坏主意。

线程：Postgres中使用Inner Selects遇到的SQL性能问题

我需要一个制表报告，如下所示：

例如（首先是简单的平面内容，然后是复杂的制表内容）：

RecallID | RecallDate | Event |..| WalkAlone | WalkWithPartner |..| ExerciseAtGym
256      | 10-01-19   | Exrcs |..| NULL      | NULL            |..| yes
256      | 10-01-19   | Walk  |..| yes       | NULL            |..| NULL
256      | 10-01-19   | Eat   |..| NULL      | NULL            |..| NULL
257      | 10-01-19   | Exrcs |..| NULL      | NULL            |..| yes

我的SQL使用了内部选择器来生成基于答案的表格列，代码如下：

select 
-- Easy flat stuff first
r.id as recallid, r.recall_date as recalldate, ... ,

-- Example of Tabulated Columns:
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l 
where l.id=aq.answer_choice_id and aq.question_id=13 
and aq.id=ans.activity_question_id and aq.activity_id=27 and ans.event_id=e.id) 
     as transportationotherintensity,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=66 and l.id=aq.answer_choice_id and aq.question_id=14
and aq.id=ans.activity_question_id and ans.event_id=e.id) 
     as commutework,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=67 and l.id=aq.answer_choice_id and aq.question_id=14 and aq.id=ans.activity_question_id and ans.event_id=e.id) 
     as commuteschool,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=95 and l.id=aq.answer_choice_id and aq.question_id=14 and aq.id=ans.activity_question_id and ans.event_id=e.id) 
     as dropoffpickup,

性能非常差。Gordon Linoff 建议在大表 ANSWERS_T 上执行一次性联接，并在所有的选定项上适当使用 FILTER。这使得查询速度提高到了1秒。

select ans.event_id,
       max(l.description) filter (where aq.question_id = 13 and aq.activity_id = 27) as transportationotherintensity
       max(l.description) filter (where l.id = 66 and aq.question_id = 14 and aq.activity_id = 67) as commutework,
       . . .
from activity_questions_t aq join
     lookup_t l 
     on l.id = aq.answer_choice_id join
     answers_t ans
     on aq.id = ans.activity_question_id
group by ans.event_id

- gene b.