如何计算运行时间更少的CTE的RowTotal [英] How to calculate RowTotal of CTE that run in less time

问题描述

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

I have store procedure which have following structure

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

当我执行此命令时，我发现执行计划

when i execute this i have found from execution plan

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

现在我删除第二个cte，即rowTotal

Now i remove the second cte ie 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.

我的第一个用于计算行总数的代码很好，但是需要更多时间。我的问题是为什么 MAX（RowNumber）需要花费更长的时间，以及如何优化此代码。在此先感谢您的帮助。

My first code to calculate rowtotal work fine but it takes more time.My question is why MAX(RowNumber) take so longer time and how can i optimized this code.Thanks in advance for any help.

推荐答案

按照您的编码方式，您的SQL存在语法错误。另一件事是，如果您从第二个查询中删除了 rowTotal ，则它根本无法工作，因为它仍然具有对它的引用。因此，我不知道这些第二次执行时间是从哪里来的。

The way you have coded it, your SQL has syntax errors. Another thing is that if you removed rowTotal from your second query, it simply wouldn't work because it still has a reference to it. So I don't know where these second execution times are from.

但是，如果我使用代码块作为模板并删除错误，则此查询的执行计划应该非常简单：您应该有一个（聚集的）索引在您的 ------- 表和 sort运算符上扫描，以及其他一些运算符（排名的顺序投影ROW_NUMBER函数，一些连接运算符，例如嵌套循环等）。聚簇索引扫描和排序应该是最占用处理器资源的操作。

However, if I use code blocks as templates and remove errors, the execution plan for this query should be quite simple: you should have a (clustered) index scan on your ------- table and sort operator, along with some other operators (sequence projection for a ranking ROW_NUMBER function, some join operator like nested loop etc). Clustered index scan and sort should be most processor intensive operations.

SQL服务器应在此处计算每一行的行号，找出最大值并在两行之间约束结果根据输入变量计算得出的数字。显然，使用此查询构建了分页功能，因此在SQL Server上的SO中有很多关于分页的内容，因此寻找它并可以找到很多相关信息。

SQL server should here calculate row numbers for each row, find a maximum of it and constraint results between the two row numbers calculated from input variables. Obvously there is a paging functionality built using this query and there is a lot about paging in SQL Server on SO, so look for it and you can find a lot of related information.

如果在此查询上构建了一个已知图层，则应对其进行更改。它为 max（row_number（ID））使用附加的unnecesarry列，该列在所有行中都是恒定的（38k？），并且在逻辑上只有一个标量值。相反，您应按照解决方案中@Damien_The_Unbeliever的建议返回 count（*），但应将其与结果集分开。这样，您可以简化查询，而具有以下类似内容：

If there is a known layer built on this query, you should change it. It uses additional unnecesarry column for max(row_number(ID)) that is constant through all rows (38k?) and logically has just a scalar value in it. Instead you should return count(*) as @Damien_The_Unbeliever suggested in its solution, but separate it from the resultset. This way you would simplify the query and have instead something like this:

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

订购应该很容易下一个查询中的结果计数。并且，如果您有一个非常大的表，则可以使用此方法。

It should be easy to get the result count in the next query. AND if you have a really big table, you can count approximate number of rows using this method.

PS如果您尚未检查执行计划中的索引问题，请执行此操作。

这篇关于如何计算运行时间更少的CTE的RowTotal的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！