在SQL Server 2005及以上版本中:
SELECT oo.*
FROM (
SELECT DISTINCT ProductId
FROM Orders
) od
CROSS APPLY
(
SELECT TOP 1 ProductID, Date, CustomerID
FROM Orders oi
WHERE oi.ProductID = od.ProductID
ORDER BY
Date DESC
) oo
名义上,查询计划包含嵌套循环
。
但是,外部循环将使用索引扫描
与流聚合
,而内部循环将包含对ProductID
的索引查找
和Top
。
实际上,第二个操作几乎是免费的,因为在内部循环中使用的索引页面很可能已经驻留在缓存中,因为它刚刚被用于外部循环。
以下是在1,000,000
行(其中有100
个DISTINCT
ProductID
)上的测试结果:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 100)
Table 'Orders'. Scan count 103, logical reads 6020, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 125 ms.
虽然这只是一个简单的SELECT DISTINCT
查询的结果:
SELECT od.*
FROM (
SELECT DISTINCT ProductId
FROM Orders
) od
还有统计数据:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 100)
Table 'Orders'. Scan count 3, logical reads 5648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 125 ms.
正如我们所看到的,性能相同,而CROSS APPLY
只需要额外的400
个逻辑读取
(很可能永远不会是物理读取
)。
不知道如何进一步改进这个查询。
此查询的好处是可以很好地并行化。您可能会注意到CPU
时间是经过时间
的两倍:这是由于在我旧的Core Duo
上并行处理。
4核CPU
将在半小时内完成此查询。
使用窗口函数的解决方案无法并行化:
SELECT od.*
FROM (
SELECT ProductId, Date, CustomerID, ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Date DESC) AS rn
FROM Orders
) od
WHERE rn = 1
这里是统计数据:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(строк обработано: 100)
Table 'Orders'. Scan count 1, logical reads 5123, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 415 ms.