如果我想从表中获取一些行,我有两个主要选项：排名函数;或TOP.

首先,让我们从Production.TransactionHistory中考虑特定ProductID的整个集合：

SELECT h.TransactionID,h.ProductID,h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800;

这将返回418行,并且计划显示它检查表中的每一行以查找此行 – 一个不受限制的聚簇索引扫描,其中谓词提供过滤器. 797读到这里,这是丑陋的.

所以,让我们公平对待它,并创建一个更有用的索引.我们的条件要求在ProductID上进行相等匹配,然后通过TransactionDate搜索最新的.我们还需要返回TransactionID,所以让我们一起来：CREATE INDEX ix_FindingMostRecent ON Production.TransactionHistory(ProductID,TransactionDate)INCLUDE(TransactionID);.

完成这项工作之后,我们的计划发生了重大变化,并将读数降低到了3个.所以我们已经将事情改进了250倍左右……

现在我们已经把比赛场地拉平了,让我们来看看顶级选项 – 排名功能和TOP.

WITH Numbered AS
(
SELECT h.TransactionID,h.TransactionDate,ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
)
SELECT TransactionID,ProductID,TransactionDate
FROM Numbered
WHERE RowNum <= 5;

SELECT TOP (5) h.TransactionID,h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
ORDER BY TransactionDate DESC;

您会注意到第二个(TOP)查询比查询和计划中的第一个(TOP)查询简单得多.但非常重要的是,他们都使用TOP来限制实际从索引中拉出的行数.成本只是估计而且值得忽略,但你可以看到两个计划中有很多相似之处,ROW_NUMBER()版本做了很少的额外工作来分配数字并相应地过滤,而且两个查询最终只做2读取他们的工作.查询优化器当然认识到在ROW_NUMBER()字段上进行过滤的想法,意识到它可以使用Top运算符来忽略不需要的行.这两个查询都足够好了 – TOP并不是更好,值得更改代码,但它更简单,对初学者来说可能更清晰.

所以这项工作涉及单一产品.但是,如果我们需要跨多个产品执行此操作,我们需要考虑会发生什么.

迭代程序员将考虑循环遍历感兴趣的产品,并多次调用此查询的想法,我们实际上可以通过这种形式编写查询 – 不使用游标,而是使用APPLY.我正在使用OUTER APPLY,如果没有任何事务,我们可能想要返回带有NULL的Product.

SELECT p.Name,p.ProductID,t.TransactionID,t.TransactionDate
FROM 
Production.Product p
OUTER APPLY (
    SELECT TOP (5) h.TransactionID,h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

这个计划是迭代程序员的方法 – 嵌套循环,为每个产品执行Top操作和Seek(我们之前有过的2次读取).这样可以对Product进行4次读取,对TransactionHistory进行360次读取.

使用ROW_NUMBER(),方法是在OVER子句中使用PARTITION BY,以便我们重新开始每个Product的编号.然后可以像以前一样过滤.该计划最终完全不同. TransactionHistory的逻辑读取率降低约15％,完整的索引扫描会将行输出.

WITH Numbered AS
(
SELECT p.Name,h.TransactionID,ROW_NUMBER() OVER (PARTITION BY h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name,TransactionID,TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

但值得注意的是,这个计划有一个昂贵的Sort运算符. Merge Join似乎不保持TransactionHistory中的行顺序,必须使用数据才能找到rownumbers.它的读取次数较少,但这种阻塞排序可能会让人感到痛苦.使用APPLY,嵌套循环将在几次读取后非常快速地返回第一行,但是使用Sort,ROW_NUMBER()将仅在大部分工作完成后返回行.

有趣的是,如果ROW_NUMBER()查询使用INNER JOIN而不是LEFT JOIN,则会出现另一个计划.

该计划使用嵌套循环,就像APPLY一样.但是没有Top运算符,因此它为每个产品提取所有事务,并使用比以前更多的读取 – 对TransactionHistory进行492次读取.没有充分的理由不在这里选择合并加入选项,所以我猜这个计划被认为是’足够好’.仍然 – 它不会阻止,这很好 – 只是不如APPLY.

在两种情况下,我用于ROW_NUMBER()的PARTITION BY列都是h.ProductID,因为我想在加入Product表之前为QO提供生成RowNum值的选项.如果我使用p.ProductID,我们会看到与INNER JOIN变体相同的形状计划.

WITH Numbered AS
(
SELECT p.Name,ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name,TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

但是Join运算符说’Left Outer Join’而不是’Inner Join’.对于TransactionHistory表,读取次数仍然不到500次.

无论如何 – 回到手头的问题……

我们已经回答了问题1,您可以选择两个选项.就个人而言,我喜欢APPLY选项.

要扩展它以使用变量数(问题2),只需要相应地更改5.哦,我添加了另一个索引,因此在Production.Product.Name上有一个包含DaysToManufacture列的索引.

WITH Numbered AS
(
SELECT p.Name,p.DaysToManufacture,TransactionDate
FROM Numbered n
WHERE RowNum <= 5 * DaysToManufacture;

SELECT p.Name,t.TransactionDate
FROM 
Production.Product p
OUTER APPLY (
    SELECT TOP (5 * p.DaysToManufacture) h.TransactionID,h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

这两个计划几乎与以前的计划相同！

再次,忽略估计的成本 – 但我仍然喜欢TOP场景,因为它更简单,并且该计划没有阻塞运算符.由于DaysToManufacture中的零数量较多,因此在TransactionHistory上的读取较少,但在现实生活中,我怀疑我们是否会选择该列.