单个SQL查询的几个计数:寻求效率

Question

我只想听听你对查询效率的看法。我正在通过一个很大的表(几百万条记录(计算值。这是MSSQL，但我认为它应该适用于任何数据库引擎。我现在正在做的是：

SELECT Task,
COUNT(*) as Total,
SUM(CASE WHEN Status = 'Active' THEN 1 ELSE 0 END) AS Active,
SUM(CASE WHEN Status = 'Active' AND AppType = 'MOBILE' THEN 1 ELSE 0 END) AS ActiveMobile,
SUM(CASE WHEN Status = 'Active' AND AppType = 'WEB' THEN 1 ELSE 0 END) AS ActiveWeb,
SUM(CASE WHEN Status = 'OnHold' THEN 1 ELSE 0 END) AS onHold,
SUM(CASE WHEN Status = 'onHold' AND AppType = 'MOBILE' THEN 1 ELSE 0 END) AS onHoldMobile,
SUM(CASE WHEN Status = 'onHold' AND AppType = 'WEB' THEN 1 ELSE 0 END) AS onHoldWeb,
SUM(CASE WHEN Status = 'Active' OR Status = 'onHold' THEN 1 ELSE 0 END) AS ActiveAndOnHold,
SUM(CASE WHEN (Status = 'Active' OR Status = 'onHold') AND AppType = 'MOBILE' THEN 1 ELSE 0 END) AS ActiveAndOnHoldMobile,
SUM(CASE WHEN (Status = 'Active' OR Status = 'onHold') AND AppType = 'WEB' THEN 1 ELSE 0 END) AS ActiveAndOnHoldWeb
FROM events
GROUP BY Task;

我意识到我正在重新计算同一件事，我应该能够添加部分结果，但老实说，如果不多次遍历表，我就不知道如何做到这一点。

实际的查询有大约20多个具有相同数据组合的SUM。查询运行需要一段时间(大约两个小时(。我想知道是否有更好的方法。

欢迎提出任何建议。

感谢

Answer 1

您可以保留中间标志。我怀疑这会加快您的查询速度，但它会使维护更简单：

SELECT Task, COUNT(*) as Total,
SUM(is_active) AS Active,
SUM(is_active * is_mobile) AS ActiveMobile,
. . .
FROM events e CROSS APPLY
(VALUES (CASE WHEN Status = 'Active' THEN 1 ELSE 0 END),
(CASE WHEN Status = 'OnHold' THEN 1 ELSE 0 END),
(CASE WHEN AppType = 'WEB' THEN 1 ELSE 0 END),
(CASE WHEN AppType = 'MOBILE' THEN 1 ELSE 0 END),
. . .
) v(is_active, is_onhold, is_web, is_mobile)
GROUP BY Task;

如果比较实际上比简单的字符串相等更麻烦，这可能会影响性能。

Answer 2

确保您的查询具有完全覆盖的索引。这会带来巨大的不同。确保您的查询计划使用多个核心。如果查看查询计划，请查找聚集操作符。这与上面的maxdop建议有关。请考虑使用采用行计数的详细信息详细信息子查询。然后是另一个查询，它将您的字段分解为最终结果。

select F1, sum(case when A = 1 then ItemCount else 0 end) SummaryField
from 
(
select F1, A, count(*) ItemCount
from T1
group by F1, A
) T2
group by F1 

如果所有其他操作都失败了，请考虑将聚合结果缓存在一个表中，该表只包含聚合查询的结果集。原始聚合查询可以每两小时运行一次。但是查询缓存表将包含更少的记录，并且查询起来会容易得多。