How to calculate RowTotal of CTE that run in less

Question

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.

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

And it show execution plan as

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

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.

Answer 1

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.

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 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.

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.

P.S. If you already haven't checked your execution plan for index problems, do it.

Answer 2

Since MAX(RowNumber) will always be equal to the total number of rows, try just having:

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

As your first CTE.