每组检索n行

#1 楼

让我们从基本场景开始。

如果我想从表中获取一些行，我有两个主要选择：排序函数；或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.ProductID, 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.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
ORDER BY TransactionDate DESC;

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

因此，这项工作适用于单个产品。但是，我们需要考虑如果需要跨多个产品执行此操作会发生什么情况。

迭代程序员将考虑遍历感兴趣的产品并多次调用此查询的想法，并且我们实际上可以摆脱这种形式的查询-不使用游标，而是使用APPLY。我正在使用OUTER APPLY，假设如果没有事务，我们可能要返回NULL的产品。

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM 
Production.Product p
OUTER APPLY (
    SELECT TOP (5) h.TransactionID, h.ProductID, 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操作并进行搜索（之前有2次读取）。这将对Product进行4次读取，对TransactionHistory进行360次读取。



使用ROW_NUMBER()，方法是在PARTITION BY子句中使用OVER，以便我们重新开始编号每个产品。然后可以像以前一样对其进行过滤。该计划最终大为不同。逻辑读取在TransactionHistory上大约降低了15％，并进行了完整的索引扫描以取出行。

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, 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, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

值得注意的是，该计划具有昂贵的Sort运算符。合并联接似乎未维护TransactionHistory中的行顺序，必须使用数据才能找到行号。读取的次数更少，但是这种阻塞的排序可能会让人感到痛苦。使用APPLY，嵌套循环将在读取几次后非常快速地返回第一行，但是对于Sort而言，ROW_NUMBER()只会在完成大部分工作之后才返回行。

有趣的是，如果ROW_NUMBER()查询使用INNER JOIN而不是LEFT JOIN，则会出现不同的计划。



该计划使用嵌套循环，就像APPLY一样。但是没有Top运算符，因此它提取每种产品的所有交易记录，并使用比以前更多的读取-492对TransactionHistory进行读取。没有充分的理由不选择此处的“合并联接”选项，因此我认为该计划被认为是“足够好”。仍然-它不会阻塞，这很好-只是不如APPLY好。

在两种情况下，我用于PARTITION BY的ROW_NUMBER()列都是h.ProductID，因为我想给QO连接到产品表之前产生RowNum值的选项。如果我使用p.ProductID，我们会看到与INNER JOIN变体相同的形状平面图。

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, 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, ProductID, TransactionID, 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.ProductID, p.DaysToManufacture, h.TransactionID, h.TransactionDate, 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, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5 * DaysToManufacture;

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM 
Production.Product p
OUTER APPLY (
    SELECT TOP (5 * p.DaysToManufacture) h.TransactionID, h.ProductID, 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上的读取较少，但是在现实生活中，我怀疑我们会选择该列。 ;）

一种避免阻塞的方法是提出一个方案，该方案处理联接右侧（该方案中）的ROW_NUMBER()位。我们可以通过在CTE外部进行联接来说服这种情况发生。

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

这里的计划看起来更简单-它没有阻塞，但是存在隐患。



请注意Compute Scalar正在从Product表中提取数据。这正在计算5 * p.DaysToManufacture值。此值未传递到从TransactionHistory表中提取数据的分支中，而是在合并联接中使用。作为残差。



因此，合并联接正在消耗所有行，不仅是第一个但很多需要的行，而且所有行然后都在执行剩余支票。随着交易数量的增加，这很危险。我不喜欢这种情况-合并联接中的残留谓词可以快速升级。我更喜欢APPLY/TOP场景的另一个原因。在特殊情况下，它恰好是一行，对于问题3，我们显然可以使用相同的查询，但是使用1而不是5。但是我们还有一个额外的选择，那就是使用常规聚合。

SELECT ProductID, MAX(TransactionDate)
FROM Production.TransactionHistory
GROUP BY ProductID;

这样的查询将是一个有用的开始，我们可以轻松地对其进行修改以使其为了平局决胜的目的（使用连接，然后将其分解）也要列出TransactionID，但是我们要么查看整个索引，要么我们逐个产品地研究产品，但实际上，在

但是我要指出的是，我们正在这里查看一个特定的场景。对于真实数据，以及可能不理想的索引策略，里程可能会有很大差异。尽管我们已经看到APPLY在这里很强，但在某些情况下它可能会变慢。不过，它很少会阻塞，因为它倾向于使用嵌套循环，许多人（包括我自己）都觉得它很吸引人。

我没有在这里尝试探讨并行性，也没有很深入地研究问题3，由于连接和拆分的复杂性，我认为这是人们很少想要的特例。这里主要要考虑的是这两个选项都非常强大。

我更喜欢APPLY。很明显，它很好地使用了Top运算符，并且很少引起阻塞。

#2 楼

在SQL Server 2005及更高版本中，执行此操作的典型方法是使用CTE和窗口功能。对于每个组的前n个，您可以简单地将ROW_NUMBER()与PARTITION子句一起使用，并在外部查询中对其进行过滤。因此，例如，可以按以下方式显示每个客户最近的5个订单：

DECLARE @top INT;
SET @top = 5;

;WITH grp AS 
(
   SELECT CustomerID, OrderID, OrderDate,
     rn = ROW_NUMBER() OVER
     (PARTITION BY CustomerID ORDER BY OrderDate DESC)
   FROM dbo.Orders
)
SELECT CustomerID, OrderID, OrderDate
  FROM grp
  WHERE rn <= @top
  ORDER BY CustomerID, OrderDate DESC;

也可以使用CROSS APPLY来做到这一点：

DECLARE @top INT;
SET @top = 5;

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
CROSS APPLY 
(
    SELECT TOP (@top) OrderID, OrderDate 
    FROM dbo.Orders AS o
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) AS o
ORDER BY c.CustomerID, o.OrderDate DESC;

使用附加选项Paul指定，说客户表有一个列，指示每个客户要包含多少行：

;WITH grp AS 
(
   SELECT CustomerID, OrderID, OrderDate,
     rn = ROW_NUMBER() OVER
     (PARTITION BY CustomerID ORDER BY OrderDate DESC)
   FROM dbo.Orders
)
SELECT c.CustomerID, grp.OrderID, grp.OrderDate
  FROM grp 
  INNER JOIN dbo.Customers AS c
  ON grp.CustomerID = c.CustomerID
  AND grp.rn <= c.Number_of_Recent_Orders_to_Show
  ORDER BY c.CustomerID, grp.OrderDate DESC;

再一次，使用CROSS APPLY并合并添加的选项，即客户的行数由某列决定在客户表中：

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
CROSS APPLY 
(
    SELECT TOP (c.Number_of_Recent_Orders_to_Show) OrderID, OrderDate 
    FROM dbo.Orders AS o
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) AS o
ORDER BY c.CustomerID, o.OrderDate DESC;

请注意，它们的执行情况会因数据分布和支持索引的可用性而有所不同，因此优化性能和获得最佳计划的确取决于本地因素。
就我个人而言，我更喜欢CTE和窗口解决方案，而不是CROSS APPLY / TOP，因为它们更好地分离了逻辑，并且对我来说更直观。总的来说（在这种情况下和我的一般经验），CTE方法都会产生更有效的计划（下面的示例），但这不应被视为普遍真理-您应始终测试您的方案，尤其是在索引已更改或数据明显偏斜。

AdventureWorks示例-没有任何变化



列出TransactionHistory表中针对每种产品的五个最近的交易日期和ID

-- CTE / OVER()

;WITH History AS
(
  SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC)
  FROM Production.Product AS p
  INNER JOIN Production.TransactionHistory AS t
  ON p.ProductID = t.ProductID
  WHERE p.Name >= N'M' AND p.Name < N'S'
)
SELECT ProductID, Name, TransactionID, TransactionDate
FROM History 
WHERE rn <= 5;

-- CROSS APPLY

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

运行时指标中这两个的比较：

CTE / OVER()计划：

CROSS APPLY计划：

CTE计划看起来更复杂，但实际上效率更高。很少关注估算的成本百分比数字，而是关注更重要的实际观察结果，例如读取次数少并且持续时间短得多。我也没有并行地运行这些，这没有什么区别。运行时指标和CTE计划（CROSS APPLY计划保持不变）：




相同，但每个产品都有n历史行，其中n是DaysToManufacture产品属性的五倍。


此处需要进行很小的更改。对于CTE，我们可以在内部查询中添加一列，然后在外部查询中进行过滤；对于CROSS APPLY，我们可以在相关的TOP内执行计算。您可能认为这会给CROSS APPLY解决方案带来一些效率，但是在这种情况下不会发生这种情况。查询：

-- CTE / OVER()

;WITH History AS
(
  SELECT p.ProductID, p.Name, p.DaysToManufacture, t.TransactionID, t.TransactionDate,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC)
  FROM Production.Product AS p
  INNER JOIN Production.TransactionHistory AS t
  ON p.ProductID = t.ProductID
  WHERE p.Name >= N'M' AND p.Name < N'S'
)
SELECT ProductID, Name, TransactionID, TransactionDate
FROM History 
WHERE rn <= (5 * DaysToManufacture);

-- CROSS APPLY

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

运行时结果：
OVER() 计划：

单线程CTE / OVER()计划：

CROSS APPLY计划：



相同，在特殊情况下，每个产品只需要一个历史记录行（TransactionDate的单个最新条目，领带再次在TransactionID处中断



同样，在此处进行较小的更改在CTE解决方案中，我们将TransactionID添加到OVER()子句中，并将外部过滤器更改为rn = 1。对于CROSS APPLY，我们进行了更改将TOP替换为TOP (1)，然后将TransactionID添加到内部ORDER BY中。

-- CTE / OVER()

;WITH History AS
(
  SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC, TransactionID DESC)
  FROM Production.Product AS p
  INNER JOIN Production.TransactionHistory AS t
  ON p.ProductID = t.ProductID
  WHERE p.Name >= N'M' AND p.Name < N'S'
)
SELECT ProductID, Name, TransactionID, TransactionDate
FROM History 
WHERE rn = 1;

-- CROSS APPLY

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

运行时结果：

并行CTE / OVER()计划：

单个线程的CTE / OVER（）计划：

CROSS APPLY计划：

窗口函数并不总是最好的选择（请访问COUNT(*) OVER()），并且它们不是解决每组n行问题的仅有两种方法，而是在这种特定情况下-给定模式，现有索引和数据分布-CTE在所有有意义的帐户中表现都更好。

AdventureWorks示例-可以灵活地添加索引
但是，如果添加支持索引，类似于Paul在评论中提到的索引，但是在第2列和第3列订购了DESC：

CREATE UNIQUE NONCLUSTERED INDEX UQ3 ON Production.TransactionHistory 
  (ProductID, TransactionDate DESC, TransactionID DESC);

实际上，您会得到更有利的计划，并且在所有这三种情况下，指标都将转向CROSS APPLY方法：

如果这是我的生产环境，那么我可能会对这种情况下的持续时间感到满意，并且不会为进一步优化而费心。

在SQL Server 2000中，这一切都比较难看不支持APPLY或OVER()子句。

#3 楼

在没有窗口功能或CROSS APPLY的DBMS（如MySQL）中，执行此操作的方法是使用标准SQL（89）。较慢的方法是使用聚合的三角形交叉连接。更快的方法（但仍然可能不及使用交叉应用或row_number函数有效）是我所说的“穷人的CROSS APPLY”。将此查询与其他查询进行比较将很有趣：假设：Orders (CustomerID, OrderDate)具有UNIQUE约束：

DECLARE @top INT;
SET @top = 5;

SELECT o.CustomerID, o.OrderID, o.OrderDate
  FROM dbo.Customers AS c
    JOIN dbo.Orders AS o
      ON  o.CustomerID = c.CustomerID
      AND o.OrderID IN
          ( SELECT TOP (@top) oi.OrderID
            FROM dbo.Orders AS oi
            WHERE oi.CustomerID = c.CustomerID
            ORDER BY oi.OrderDate DESC
          )
  ORDER BY CustomerID, OrderDate DESC ;

对于以下问题，每个组的自定义顶部行：

SELECT o.CustomerID, o.OrderID, o.OrderDate
  FROM dbo.Customers AS c
    JOIN dbo.Orders AS o
      ON  o.CustomerID = c.CustomerID
      AND o.OrderID IN
          ( SELECT TOP (c.Number_of_Recent_Orders_to_Show) oi.OrderID
            FROM dbo.Orders AS oi
            WHERE oi.CustomerID = c.CustomerID
            ORDER BY oi.OrderDate DESC
          )
  ORDER BY CustomerID, OrderDate DESC ;

注意：在MySQL中，可以使用AND o.OrderID IN (SELECT TOP(@top) oi.OrderID ...)代替AND o.OrderDate >= (SELECT oi.OrderDate ... LIMIT 1 OFFSET (@top - 1))。 SQL Server在2012版本中添加了FETCH / OFFSET语法。使用IN (TOP...)调整了此处的查询，以与早期版本一起使用。

#4 楼

我采用了一种略有不同的方法，主要是看该技术与其他技术的比较，因为选择不错，对吗？

测试

为什么我们不这样做首先看一下各种方法如何相互叠加。我进行了三组测试：


第一组无需数据库修改即可运行
第二组在创建索引以支持针对TransactionDate的基于Production.TransactionHistory的查询后运行。 br />第三组假设略有不同。由于所有三个测试都针对相同的产品列表运行，因此如果我们缓存该列表怎么办？我的方法使用内存缓存，而其他方法使用等效的临时表。为第二组测试创建的支持索引对于该组测试仍然存在。

其他测试详细信息：


这些测试是针对SQL上的AdventureWorks2012运行的Server 2012 SP2（开发人员版）。对于每个测试，我都标记了我从中查询的答案以及该查询的特定查询。
我使用了“查询选项”中的“执行后丢弃结果”选项。结果。
请注意，对于前两组测试，我的方法RowCounts似乎是“关闭”的。这是由于我的方法是对CROSS APPLY所做操作的手动实现：它对Production.Product运行初始查询并返回161行，然后将其用于对Production.TransactionHistory的查询。因此，我的条目的RowCount值总是比其他条目多161。在第三组测试（带缓存）中，所有方法的行数均相同。
我使用SQL Server Profiler来捕获统计信息，而不是依赖执行计划。亚伦（Aaron）和米凯尔（Mikael）在展示他们的查询计划方面已经做得很出色，并且无需复制该信息。我的方法的目的是将查询简化为一个简单的形式，以至于实际上并不重要。使用Profiler的另一个原因是，但稍后会提到。
我选择使用Name >= N'M' AND Name < N'S'，而不是使用Name LIKE N'[M-R]%'构造，而SQL Server对待它们的方式相同。

结果

没有支持索引

这实际上是开箱即用的AdventureWorks2012。在所有情况下，我的方法显然都比其他方法好，但从来没有比排名前1或2的方法好。

测试1

Aaron的CTE显然是

测试2

亚伦的CTE（再次）和Mikael的第二种apply row_number()方法紧随其后。

测试3

再次获得亚伦CTE奖。

结论
当TransactionDate上没有支持指标时，我的方法比做标准CROSS APPLY更好，但是，使用CTE方法显然是可行的方法。

带有支持索引（无缓存）

对于这组测试，我在TransactionHistory.TransactionDate上添加了明显的索引，因为所有查询在该字段上排序。我说“显而易见”，因为大多数其他答案也都同意这一点。而且由于查询都需要最新日期，因此应对TransactionDate字段进行排序DESC，因此我只是抓住了Mikael答案底部的CREATE INDEX语句，并添加了一个明确的FILLFACTOR：

CREATE INDEX [IX_TransactionHistoryX]
    ON Production.TransactionHistory (ProductID ASC, TransactionDate DESC)
    WITH (FILLFACTOR = 100);

一旦建立此索引，结果就会发生很大变化。

测试1

这次，至少在逻辑读取方面，这是我的方法。 CROSS APPLY方法以前在测试1中表现最差，在持续时间方面胜出，甚至在逻辑读取方面胜过CTE方法。

测试2

这是Mikael的第一个方法apply row_number()方法是看Reads的赢家，而以前它是表现最差的一种。现在，在查看Reads时，我的方法排在第二位。实际上，除了CTE方法外，其余的在读取方面都相当接近。

测试3

这里CTE仍然是赢家，但现在与创建索引之前存在的巨大差异相比，其他方法之间的差异几乎不明显。

结论
我的方法的适用性现在更加明显，尽管它的适应性较差没有适当的索引。

具有支持索引和缓存

对于这组测试，我使用了缓存，因为，为什么不呢？我的方法允许使用其他方法无法访问的内存中缓存。为了公平起见，我创建了以下临时表，用于替代所有三个测试中其他方法中的所有引用的Product.Product。 DaysToManufacture字段仅在测试编号2中使用，但在SQL脚本中使用同一表更易于保持一致，并且在此处使用它也无害。

CREATE TABLE #Products
(
    ProductID INT NOT NULL PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    DaysToManufacture INT NOT NULL
);

INSERT INTO #Products (ProductID, Name, DaysToManufacture)
    SELECT  p.ProductID, p.Name, p.DaysToManufacture
    FROM    Production.Product p
    WHERE   p.Name >= N'M' AND p.Name < N'S'
    AND    EXISTS (
                    SELECT  *
                    FROM    Production.TransactionHistory th
                    WHERE   th.ProductID = p.ProductID
                );

ALTER TABLE #Products REBUILD WITH (FILLFACTOR = 100);

测试1

所有方法似乎都从缓存中同样受益，而我的方法仍然领先。

测试2

现在我们看到的是阵容上的差异，因为我的方法勉强领先，仅比Mikael的第一个apply row_number()方法好2读，而没有缓存，我的方法落后4读。

测试3
请参阅底部的更新（在行下方）。在这里，我们再次看到了一些区别。与Aaron的CROSS APPLY方法相比，我的方法的“参数化”风格现在几乎没有2次读取领先（没有缓存，它们是相等的）。但是真正奇怪的是，我们第一次看到一种受缓存不利影响的方法：Aaron的CTE方法（以前是测试3的最佳方法）。但是，我不会在不适当的地方获得认可，并且由于没有缓存，Aaron的CTE方法仍然比我的缓存方法要快，因此针对这种特殊情况的最佳方法似乎是Aaron的CTE方法。 br />
结论请参见底部的更新（在此行下方）。
重复使用辅助查询的结果的情况通常（但并非总是）受益于缓存这些结果。但是，当缓存是一种好处时，使用内存进行缓存比使用临时表更具优势。

方法

通常

我将从“详细”查询（例如，获取ProductID和DaysToManufacture）中的“标头”查询（即，获取Name，在某些情况下还基于TransactionID，获取TransactionDate）。其概念是执行非常简单的查询，并且不允许优化器在加入查询时感到困惑。显然，这并不总是有利的，因为它也不允许优化器进行优化。但是，正如我们在结果中看到的那样，根据查询的类型，此方法确实有其优点。

该方法的各种风格之间的区别是：


常量：提交任何可替换的值作为内联常量而不是参数。这将在所有三个测试中引用ProductID，并在测试2中引用返回的行数，因为这是“ DaysToManufacture产品属性的五倍”的函数。此子方法意味着每个ProductID都会获得自己的执行计划，如果ProductID的数据分布差异很大，这将是有益的。但是，如果数据分布几乎没有变化，则生成附加计划的成本可能就不值得。
参数化：至少将ProductID提交为@ProductID，以允许执行计划缓存和重用。还有一个额外的测试选项，也可以将要返回测试2的可变行数作为参数。
优化未知：当将ProductID引用为@ProductID时，如果数据分布差异很大，则可以缓存一个对其他ProductID值有负面影响的计划，因此，最好知道使用此查询提示是否有帮助。
缓存产品：与其每次都查询完全相同的列表，而不是每次查询Production.Product表，运行一次查询（当我们在查询时，过滤掉甚至不在ProductID表中的所有TransactionHistory，以免浪费在那里的任何资源）并缓存该列表。该列表应包含DaysToManufacture字段。使用此选项，第一次执行时在逻辑读取上的初始命中率会稍高，但之后仅查询TransactionHistory表。

特别是

好的，但是，嗯，怎么可能不使用CURSOR并将所有结果集转储到临时表或表变量中而将所有子查询作为单独的查询发布？显然，执行CURSOR / Temp Table方法将在读取和写入中反映出非常明显的效果。好吧，通过使用SQLCLR :)。通过创建SQLCLR存储过程，我能够打开一个结果集，并从本质上将每个子查询的结果作为连续结果集（而不是多个结果集）流式传输到该结果集。在产品信息之外（即ProductID，Name和DaysToManufacture），没有任何子查询结果必须存储在任何位置（内存或磁盘），并且仅作为SQLCLR存储过程的主要结果集传递。这使我可以做一个简单的查询来获取产品信息，然后循环浏览它，对TransactionHistory发出非常简单的查询。

，这就是为什么我不得不使用SQL Server Profiler来捕获统计信息的原因。通过设置“包括实际执行计划”查询选项或发出SET STATISTICS XML ON;，SQLCLR存储过程未返回执行计划。对于产品信息缓存，我使用了readonly static通用列表（即下面的代码中的_GlobalProducts）。似乎添加到集合中并没有违反readonly选项，因此，即使程序集具有PERMISSON_SET的SAFE :)，即使该代码是违反直觉的，此代码也可以使用。

生成的查询

此SQLCLR存储过程产生的查询如下：

产品信息

测试编号1和3（无缓存）

 SELECT prod1.ProductID, prod1.Name, 1 AS [DaysToManufacture]
FROM   Production.Product prod1
WHERE  prod1.Name LIKE N'[M-R]%';
 

测试编号2（无缓存）

 ;WITH cte AS
(
    SELECT prod1.ProductID
    FROM   Production.Product prod1 WITH (INDEX(AK_Product_Name))
    WHERE  prod1.Name LIKE N'[M-R]%'
)
SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
FROM   Production.Product prod2
INNER JOIN cte
        ON cte.ProductID = prod2.ProductID;
 

测试编号1、2和3（缓存）

 ;WITH cte AS
(
    SELECT prod1.ProductID
    FROM   Production.Product prod1 WITH (INDEX(AK_Product_Name))
    WHERE  prod1.Name LIKE N'[M-R]%'
    AND    EXISTS (
                SELECT *
                FROM Production.TransactionHistory th
                WHERE th.ProductID = prod1.ProductID
                  )
)
SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
FROM   Production.Product prod2
INNER JOIN cte
        ON cte.ProductID = prod2.ProductID;
 

交易信息

测试编号1和2（常量）

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

测试编号1和2（参数化）

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

测试编号1和2（参数化+未知优化）

 SELECT TOP (5) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC
OPTION (OPTIMIZE FOR (@ProductID UNKNOWN));
 

测试编号2（均已参数化）

 SELECT TOP (@RowsToReturn) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC
;
 

测试编号2（同时参数化+未知）

 SELECT TOP (@RowsToReturn) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC
OPTION (OPTIMIZE FOR (@ProductID UNKNOWN));
 

测试编号3（常量）

 SELECT TOP (1) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = 977
ORDER BY th.TransactionDate DESC, th.TransactionID DESC;
 

测试编号3（参数化）

 SELECT TOP (1) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC, th.TransactionID DESC
;
 

测试编号3（参数化+优化）：

 SELECT TOP (1) th.TransactionID, th.TransactionDate
FROM   Production.TransactionHistory th
WHERE  th.ProductID = @ProductID
ORDER BY th.TransactionDate DESC, th.TransactionID DESC
OPTION (OPTIMIZE FOR (@ProductID UNKNOWN));
 

代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class ObligatoryClassName
{
    private class ProductInfo
    {
        public int ProductID;
        public string Name;
        public int DaysToManufacture;

        public ProductInfo(int ProductID, string Name, int DaysToManufacture)
        {
            this.ProductID = ProductID;
            this.Name = Name;
            this.DaysToManufacture = DaysToManufacture;

            return;
        }
    }

    private static readonly List<ProductInfo> _GlobalProducts = new List<ProductInfo>();

    private static void PopulateGlobalProducts(SqlBoolean PrintQuery)
    {
        if (_GlobalProducts.Count > 0)
        {
            if (PrintQuery.IsTrue)
            {
                SqlContext.Pipe.Send(String.Concat("I already haz ", _GlobalProducts.Count,
                            " entries :)"));
            }

            return;
        }

        SqlConnection _Connection = new SqlConnection("Context Connection = true;");
        SqlCommand _Command = new SqlCommand();
        _Command.CommandType = CommandType.Text;
        _Command.Connection = _Connection;
        _Command.CommandText = @"
   ;WITH cte AS
   (
     SELECT prod1.ProductID
     FROM   Production.Product prod1 WITH (INDEX(AK_Product_Name))
     WHERE  prod1.Name LIKE N'[M-R]%'
     AND    EXISTS (
                     SELECT *
                     FROM Production.TransactionHistory th
                     WHERE th.ProductID = prod1.ProductID
                   )
   )
   SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
   FROM   Production.Product prod2
   INNER JOIN cte
           ON cte.ProductID = prod2.ProductID;
";

        SqlDataReader _Reader = null;

        try
        {
            _Connection.Open();

            _Reader = _Command.ExecuteReader();

            while (_Reader.Read())
            {
                _GlobalProducts.Add(new ProductInfo(_Reader.GetInt32(0), _Reader.GetString(1),
                                                    _Reader.GetInt32(2)));
            }
        }
        catch
        {
            throw;
        }
        finally
        {
            if (_Reader != null && !_Reader.IsClosed)
            {
                _Reader.Close();
            }

            if (_Connection != null && _Connection.State != ConnectionState.Closed)
            {
                _Connection.Close();
            }

            if (PrintQuery.IsTrue)
            {
                SqlContext.Pipe.Send(_Command.CommandText);
            }
        }

        return;
    }


    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetTopRowsPerGroup(SqlByte TestNumber,
        SqlByte ParameterizeProductID, SqlBoolean OptimizeForUnknown,
        SqlBoolean UseSequentialAccess, SqlBoolean CacheProducts, SqlBoolean PrintQueries)
    {
        SqlConnection _Connection = new SqlConnection("Context Connection = true;");
        SqlCommand _Command = new SqlCommand();
        _Command.CommandType = CommandType.Text;
        _Command.Connection = _Connection;

        List<ProductInfo> _Products = null;
        SqlDataReader _Reader = null;

        int _RowsToGet = 5; // default value is for Test Number 1
        string _OrderByTransactionID = "";
        string _OptimizeForUnknown = "";
        CommandBehavior _CmdBehavior = CommandBehavior.Default;

        if (OptimizeForUnknown.IsTrue)
        {
            _OptimizeForUnknown = "OPTION (OPTIMIZE FOR (@ProductID UNKNOWN))";
        }

        if (UseSequentialAccess.IsTrue)
        {
            _CmdBehavior = CommandBehavior.SequentialAccess;
        }

        if (CacheProducts.IsTrue)
        {
            PopulateGlobalProducts(PrintQueries);
        }
        else
        {
            _Products = new List<ProductInfo>();
        }


        if (TestNumber.Value == 2)
        {
            _Command.CommandText = @"
   ;WITH cte AS
   (
     SELECT prod1.ProductID
     FROM   Production.Product prod1 WITH (INDEX(AK_Product_Name))
     WHERE  prod1.Name LIKE N'[M-R]%'
   )
   SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
   FROM   Production.Product prod2
   INNER JOIN cte
           ON cte.ProductID = prod2.ProductID;
";
        }
        else
        {
            _Command.CommandText = @"
     SELECT prod1.ProductID, prod1.Name, 1 AS [DaysToManufacture]
     FROM   Production.Product prod1
     WHERE  prod1.Name LIKE N'[M-R]%';
";
            if (TestNumber.Value == 3)
            {
                _RowsToGet = 1;
                _OrderByTransactionID = ", th.TransactionID DESC";
            }
        }

        try
        {
            _Connection.Open();

            // Populate Product list for this run if not using the Product Cache
            if (!CacheProducts.IsTrue)
            {
                _Reader = _Command.ExecuteReader(_CmdBehavior);

                while (_Reader.Read())
                {
                    _Products.Add(new ProductInfo(_Reader.GetInt32(0), _Reader.GetString(1),
                                                  _Reader.GetInt32(2)));
                }

                _Reader.Close();

                if (PrintQueries.IsTrue)
                {
                    SqlContext.Pipe.Send(_Command.CommandText);
                }
            }
            else
            {
                _Products = _GlobalProducts;
            }

            SqlDataRecord _ResultRow = new SqlDataRecord(
                new SqlMetaData[]{
                    new SqlMetaData("ProductID", SqlDbType.Int),
                    new SqlMetaData("Name", SqlDbType.NVarChar, 50),
                    new SqlMetaData("TransactionID", SqlDbType.Int),
                    new SqlMetaData("TransactionDate", SqlDbType.DateTime)
                });

            SqlParameter _ProductID = new SqlParameter("@ProductID", SqlDbType.Int);
            _Command.Parameters.Add(_ProductID);
            SqlParameter _RowsToReturn = new SqlParameter("@RowsToReturn", SqlDbType.Int);
            _Command.Parameters.Add(_RowsToReturn);

            SqlContext.Pipe.SendResultsStart(_ResultRow);

            for (int _Row = 0; _Row < _Products.Count; _Row++)
            {
                // Tests 1 and 3 use previously set static values for _RowsToGet
                if (TestNumber.Value == 2)
                {
                    if (_Products[_Row].DaysToManufacture == 0)
                    {
                        continue; // no use in issuing SELECT TOP (0) query
                    }

                    _RowsToGet = (5 * _Products[_Row].DaysToManufacture);
                }

                _ResultRow.SetInt32(0, _Products[_Row].ProductID);
                _ResultRow.SetString(1, _Products[_Row].Name);

                switch (ParameterizeProductID.Value)
                {
                    case 0x01:
                        _Command.CommandText = String.Format(@"
   SELECT TOP ({0}) th.TransactionID, th.TransactionDate
   FROM   Production.TransactionHistory th
   WHERE  th.ProductID = @ProductID
   ORDER BY th.TransactionDate DESC{2}
   {1};
", _RowsToGet, _OptimizeForUnknown, _OrderByTransactionID);

                        _ProductID.Value = _Products[_Row].ProductID;
                        break;
                    case 0x02:
                        _Command.CommandText = String.Format(@"
   SELECT TOP (@RowsToReturn) th.TransactionID, th.TransactionDate
   FROM   Production.TransactionHistory th
   WHERE  th.ProductID = @ProductID
   ORDER BY th.TransactionDate DESC
   {0};
", _OptimizeForUnknown);

                        _ProductID.Value = _Products[_Row].ProductID;
                        _RowsToReturn.Value = _RowsToGet;
                        break;
                    default:
                        _Command.CommandText = String.Format(@"
   SELECT TOP ({0}) th.TransactionID, th.TransactionDate
   FROM   Production.TransactionHistory th
   WHERE  th.ProductID = {1}
   ORDER BY th.TransactionDate DESC{2};
", _RowsToGet, _Products[_Row].ProductID, _OrderByTransactionID);
                        break;
                }


                _Reader = _Command.ExecuteReader(_CmdBehavior);

                while (_Reader.Read())
                {
                    _ResultRow.SetInt32(2, _Reader.GetInt32(0));
                    _ResultRow.SetDateTime(3, _Reader.GetDateTime(1));

                    SqlContext.Pipe.SendResultsRow(_ResultRow);
                }
                _Reader.Close();
            }

        }
        catch
        {
            throw;
        }
        finally
        {
            if (SqlContext.Pipe.IsSendingResults)
            {
                SqlContext.Pipe.SendResultsEnd();
            }

            if (_Reader != null && !_Reader.IsClosed)
            {
                _Reader.Close();
            }

            if (_Connection != null && _Connection.State != ConnectionState.Closed)
            {
                _Connection.Close();
            }

            if (PrintQueries.IsTrue)
            {
                SqlContext.Pipe.Send(_Command.CommandText);
            }
        }


    }
}

测试查询

没有足够的空间可以在此处发布测试，所以我将找到另一个位置。

结论

对于某些情况，SQLCLR可以用于处理在T-SQL中无法完成的查询的某些方面。并且可以使用内存代替临时表来进行缓存，但是应该谨慎谨慎地进行，因为内存不会自动释放回系统。尽管可以通过添加参数以定制正在执行的查询的更多方面，但使此方法比我在此显示的灵活性更灵活，但该方法也不会帮助临时查询。


UPDATE

其他测试
我的原始测试在TransactionHistory上包含支持索引，它使用以下定义：

ProductID ASC, TransactionDate DESC

我当时决定放弃在末尾加入TransactionId DESC，认为这可能有助于测试编号3（它指定了最新TransactionId的平局决胜-好吧，由于未明确说明，因此假设为“最新”），但是

但是，然后Aaron用了包含TransactionId DESC的支持指数进行了重新测试，发现CROSS APPLY方法是有效的。所有三个测试的获胜者。这与我的测试不同，后者表明CTE方法最适合测试编号3（当不使用缓存时，反映了Aaron的测试）。很明显，还有一个其他变体需要测试。 br />

DROP INDEX [IX_TransactionHistoryX] ON Production.TransactionHistory;

CREATE UNIQUE INDEX [UIX_TransactionHistoryX]
    ON Production.TransactionHistory (ProductID ASC, TransactionDate DESC, TransactionID DESC)
    WITH (FILLFACTOR = 100);

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

我重新运行了测试编号1，结果与预期的相同。然后，我重新运行3号测试，结果的确发生了变化：


以上结果是针对标准非缓存测试的。这次，不仅TransactionId击败了CTE（正如Aaron的测试所示），而且SQLCLR proc以30次读取率居首位（woo hoo）。


以上结果用于启用缓存的测试。这次CTE的性能没有下降，尽管CROSS APPLY仍然胜过它。但是，现在SQLCLR proc领先23次Reads（再次呼呼）。

带走


有多种选择。最好尝试几种，因为它们各有所长。此处进行的测试显示，在所有测试中，表现最佳和表现最差的阅读器和持续时间之间的差异都很小（带有支持指数）；读取的变化约为350，持续时间为55 ms。尽管SQLCLR proc确实在1次测试中均获胜（就读而言），但仅保存一些读通常不值得使用SQLCLR路由的维护成本。但是在AdventureWorks2012中，CROSS APPLY表只有504行，而Product表只有113,443行。当行数增加时，这些方法之间的性能差异可能会变得更加明显。
虽然这个问题是特定于获取一组特定的行的，但不应忽视的是，性能中的最大因素是索引编制，而不是特定的SQL。在确定哪种方法真正最佳之前，需要有一个好的索引。
这里发现的最重要的教训不是有关CROSS APPLY，CTE，SQLCLR，而是关于TESTING。不要假设从多个人那里获得想法，并尽可能多地测试场景。

#5 楼

APPLY TOP或ROW_NUMBER()？

简短回顾一下这些差异，并实际上保持简短，我只展示方案2的计划，并在Production.TransactionHistory上添加了索引。

create index IX_TransactionHistoryX on 
  Production.TransactionHistory(ProductID, TransactionDate)

row_number()查询：。

with C as
(
  select T.TransactionID,
         T.TransactionDate,
         P.DaysToManufacture,
         row_number() over(partition by P.ProductID order by T.TransactionDate desc) as rn
  from Production.Product as P
    inner join Production.TransactionHistory as T
      on P.ProductID = T.ProductID
  where P.Name >= N'M' and
        P.Name < N'S'
)
select C.TransactionID,
       C.TransactionDate
from C
where C.rn <= 5 * C.DaysToManufacture;

apply top版本：

select T.TransactionID, 
       T.TransactionDate
from Production.Product as P
  cross apply (
              select top(cast(5 * P.DaysToManufacture as bigint))
                T.TransactionID,
                T.TransactionDate
              from Production.TransactionHistory as T
              where P.ProductID = T.ProductID
              order by T.TransactionDate desc
              ) as T
where P.Name >= N'M' and
      P.Name < N'S';

它们之间的主要区别在于，嵌套循环下面的顶部表达式上的apply top过滤器连接row_number版本过滤器加入之后。这意味着从Production.TransactionHistory读取的数据比实际需要的更多。

如果只有一种方法可以在联接之前将负责枚举行的运算符向下推到较低的分支，则row_number的版本可能会更好。

因此输入apply row_number()版本。

select T.TransactionID, 
       T.TransactionDate
from Production.Product as P
  cross apply (
              select T.TransactionID,
                     T.TransactionDate
              from (
                   select T.TransactionID,
                          T.TransactionDate,
                          row_number() over(order by T.TransactionDate desc) as rn
                   from Production.TransactionHistory as T
                   where P.ProductID = T.ProductID
                   ) as T
              where T.rn <= cast(5 * P.DaysToManufacture as bigint)
              ) as T
where P.Name >= N'M' and
      P.Name < N'S';

您可以看到apply row_number()与apply top只是稍微复杂一点。执行时间大约相同或稍慢。

那么，为什么我要花一个比我们现有的更好的答案呢？好吧，您还可以在现实世界中尝试一件事，但读取结果实际上有所不同。我没有解释的一个*。

 APPLY - ROW_NUMBER
(961 row(s) affected)
Table 'TransactionHistory'. Scan count 115, logical reads 230, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

APPLY - TOP
(961 row(s) affected)
Table 'TransactionHistory'. Scan count 115, logical reads 268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 

在某些情况下，我不妨抛出第二个row_number()版本，这可能是要走的路。在某些特定情况下，可能是因为您期望实际上需要Production.TransactionHistory中的大多数行，因为在这里您获得了Production.Product和枚举的Production.TransactionHistory之间的合并联接。 br />
要在没有排序运算符的情况下获得上述形状，还必须按TransactionDate降序更改支持索引以排序。

with C as
(
  select T.TransactionID,
         T.TransactionDate,
         T.ProductID,
         row_number() over(partition by T.ProductID order by T.TransactionDate desc) as rn
  from Production.TransactionHistory as T
)
select C.TransactionID,
       C.TransactionDate
from C
 inner join Production.Product as P
      on P.ProductID = C.ProductID
where P.Name >= N'M' and
      P.Name < N'S' and
      C.rn <= 5 * P.DaysToManufacture;

*编辑：额外的逻辑读取是由于apply-top所使用的嵌套循环预取。您可以使用未记录的TF 8744（和/或更高版本的9115）禁用此功能，以获取相同数量的逻辑读取。在正确的情况下，预取可能是应用程序替代方案的优势。 -保罗·怀特

#6 楼

我通常结合使用CTE和窗口功能。您可以使用以下类似方法实现此答案：

;WITH GiveMeCounts
AS (
    SELECT CustomerID
        ,OrderDate
        ,TotalAmt

        ,ROW_NUMBER() OVER (
            PARTITION BY CustomerID ORDER BY 
            --You can change the following field or sort order to whatever you'd like to order by.
            TotalAmt desc
            ) AS MySeqNum
    )
SELECT CustomerID, OrderDate, TotalAmt
FROM GiveMeCounts
--Set n per group here
where MySeqNum <= 10

对于额外的贷方部分，其中不同的组可能希望返回不同数量的行，则可以使用单独的表。可以说使用诸如州这样的地理标准：

+-------+-----------+
| State | MaxSeqnum |
+-------+-----------+
| AK    |        10 |
| NY    |         5 |
| NC    |        23 |
+-------+-----------+

为了实现此目标，值可能会有所不同，您需要将CTE加入到State表中，类似于this：

SELECT [CustomerID]
    ,[OrderDate]
    ,[TotalAmt]
    ,[State]
FROM GiveMeCounts gmc
INNER JOIN StateTable st ON gmc.[State] = st.[State]
    AND gmc.MySeqNum <= st.MaxSeqNum