如何计算CTE的RowTotal，在更短的时间运行(How to calculate RowTota

我有一个具有以下结构的存储过程

WITH ItemsContact (
    IsCostVariantItem
    ,ItemID
    ,AttributeSetID
    ,ItemTypeID
    ,HidePrice
    ,HideInRSSFeed
    ,HideToAnonymous
    ,IsOutOfStock
    ,AddedOn
    ,BaseImage
    ,AlternateText
    ,SKU
    ,[Name]
    ,DownloadableID
    ,[Description]
    ,ShortDescription
    ,[Weight]
    ,Quantity
    ,Price
    ,ListPrice
    ,IsFeatured
    ,IsSpecial
    ,ViewCount
    ,SoldItem
    ,TotalDiscount
    ,RatedValue
    ,RowNumber
    )
AS (
    SELECT ------,
        ROW_NUMBER() OVER (
            ORDER BY i.[ItemID] DESC
            ) AS RowNumber
    FROM -------
    )
    ,rowTotal (RowTotal)
AS (
    SELECT MAX(RowNumber)
    FROM ItemsContact
    )
SELECT CONVERT(INT, r.RowTotal) AS RowTotal
    ,c.*
FROM ItemsContact c
    ,rowTotal r
WHERE RowNumber >= @offset
    AND RowNumber <= (@offset + @limit - 1)
ORDER BY ItemID

当我执行此我有一个从执行计划中

SQL Server Execution Times:
   CPU time = 344 ms,  elapsed time = 362 ms.

现在我删除第二个CTE即rowTotal

WITH ItemsContact (
    IsCostVariantItem
    ,ItemID
    ,AttributeSetID
    ,ItemTypeID
    ,HidePrice
    ,HideInRSSFeed
    ,HideToAnonymous
    ,IsOutOfStock
    ,AddedOn
    ,BaseImage
    ,AlternateText
    ,SKU
    ,[Name]
    ,DownloadableID
    ,[Description]
    ,ShortDescription
    ,[Weight]
    ,Quantity
    ,Price
    ,ListPrice
    ,IsFeatured
    ,IsSpecial
    ,ViewCount
    ,SoldItem
    ,TotalDiscount
    ,RatedValue
    ,RowNumber
    )
AS (
    SELECT ------,
        ROW_NUMBER() OVER (
            ORDER BY i.[ItemID] DESC
            ) AS RowNumber
    FROM -------
    )
SELECT c.*
FROM ItemsContact c
    ,rowTotal r
WHERE RowNumber >= @offset
    AND RowNumber <= (@offset + @limit - 1)
ORDER BY ItemID

它显示执行计划以

SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 61 ms.

我的第一个代码来计算rowtotal做工精细，但它需要更多的time.My问题是，为什么MAX(RowNumber)花了这么长的时间，我怎么能提前优化这个code.Thanks的任何帮助。

Answer 1:

由于MAX(RowNumber)将始终等于行的总数，尝试只是有：

SELECT ------,
    ROW_NUMBER() OVER (
        ORDER BY i.[ItemID] DESC
        ) AS RowNumber,
    COUNT(*) OVER () as RowTotal
FROM -------

作为你的第一个CTE。

Answer 2:

你有编码的方式，你的SQL有语法错误。 另一件事是，如果你删除rowTotal从你的第二个查询，它根本就不会工作，因为它仍然有它的参考。 因此，我不知道这些第二执行时间是从。

不过，如果我使用代码块作为模板，删除错误，该查询的执行计划应该是很简单的：你应该有你的一个（集群） 索引扫描 -------表和排序操作 ，具有一定的顺其它运营商（为一个排名ROW_NUMBER函数序列投影，一些联接运算符等嵌套循环等）。 聚集索引扫描，排序应该是最处理器密集型操作。

SQL服务器应该在这里计算行号的每一行，找到最大的，并从输入变量计算出的两个行号之间的约束造成的。 Obvously存在使用该查询生成一个分页功能，并有很多关于分页在SQL Server上如此，所以寻找它，你可以找到很多相关的信息。

如果没有建立在这个查询已知层，你应该改变它。 它采用额外unnecesarry列max(row_number(ID))是恒定的，通过所有行（38K？）和逻辑上它只有一个标量值。 相反，你应该返回count(*)作为@Damien_The_Unbeliever在其解决方案的建议，但它从结果中分离出来。 这样，您将简化查询，并有替代是这样的：

SELECT
  N,
  *
FROM 
  YourTable 
  CROSS apply(
    SELECT N = ROW_NUMBER() OVER (ORDER BY ItemID DESC)
  ) x
WHERE N BETWEEN @offset AND @offset + @limit - 1
ORDER BY ItemID

它应该很容易得到的结果数在接下来的查询。 如果你有一个非常大的表，你可以指望用行的大致数目这种方法 。

PS如果你已经不检查索引问题，你执行计划，做到这一点。

文章来源: How to calculate RowTotal of CTE that run in less time