使用GROUP BY ALL的缺点？

Yosi Dahari 发表于 Dev

Yosi Dahari |

我一直认为子句ALL上下文中的关键字GROUP BY非常有用且有意义。使用它时，我没有遇到任何性能问题。

MSDN文档指出它将为EOL（自2005年起），建议不要在生产代码中使用它，但在SQL Server最新版本之前仍将支持它。

不使用会产生性能影响或其他实际原因吗？

Bogdan Sahlean

您可以更换

SELECT AggregateFunction(AggColumn) 
FROM ... 
WHERE Predicate GROUP BY ALL GrpColumn

与

SELECT AggregateFunction(CASE WHEN Predicate THEN AggColumn END) 
FROM ... 
GROUP BY GrpColumn

例：

SELECT  p.ProductID, MAX(p.UnitPrice) AS MAX_UnitPrice
FROM (
    SELECT 1, 100 UNION ALL
    SELECT 1, 10.5 UNION ALL
    SELECT 1, 10 UNION ALL
    SELECT 2, 55 UNION ALL
    SELECT 2, 99
) p(ProductID, UnitPrice)
WHERE p.ProductID = 1
GROUP BY ALL p.ProductID;

SELECT  p.ProductID, 
        MAX(CASE WHEN p.ProductID = 1 THEN p.UnitPrice END) AS MAX_UnitPrice
FROM (
    SELECT 1, 100 UNION ALL
    SELECT 1, 10.5 UNION ALL
    SELECT 1, 10 UNION ALL
    SELECT 2, 55 UNION ALL
    SELECT 2, 99
) p(ProductID, UnitPrice)
GROUP BY ALL p.ProductID;

编辑1：

SET NOCOUNT ON;
SET STATISTICS IO ON;

PRINT 'Test #1: GROUP BY ALL'
SELECT  p.ProductModelID, 
        MAX(p.ListPrice) AS MAX_ListPrice
FROM    Production.Product p
WHERE   p.Color = 'Red'
GROUP BY ALL p.ProductModelID

PRINT 'Test #2: GROUP BY + MAX(CASE WHEN)'
SELECT  p.ProductModelID, 
        MAX(CASE WHEN p.Color = 'Red' THEN p.ListPrice END) AS MAX_ListPrice
FROM    Production.Product p
GROUP BY p.ProductModelID

统计IO输出：

Test #1: GROUP BY ALL
Table 'Product'. Scan count 2, logical reads 30

Test #2: GROUP BY + MAX(CASE WHEN)
Table 'Product'. Scan count 1, logical reads 15

实际执行计划：在此处输入图片说明

从性能的角度来看，从该测试（我使用Adventure Works 2008 R2）中，我们可以看到GROUP BY ALL强制SQL Server两次读取相同的数据：

执行计划有两个索引扫描运算符，
STATISTICS IO ON的输出还显示2次扫描和数字。GROUP BY ALL的逻辑读取次数（在我的测试中）大于num。非GROUP BY ALL解决方案的逻辑读取数。同样，创建索引不会改变这种情况：2个操作员“扫描和/或寻找”GROUP BY ALL 与1个操作员“扫描或寻找” GROUP BY + MAX(CASE WHEN)。