CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005paginationcommon-table-expression
Larry Grady
Larry Grady

Reputation: 519

CTE Paging, with Count

I have a sproc that I'm running that I'm trying to use pagination but also return back the entire product count. It's "working" but the problem is that when I take out the totalCount CTE and remove it from the final SELECT my sproc runs in ~50ms. When I keep it in there my query takes 7-8 seconds. It's a large query if I grab all records but the idea is to grab just the page I need but I need to return total records. Is there a more efficient way to do this?

/** MAIN SEARCH BLOCK **/

WITH search_results AS
(

SELECT DISTINCT     
    ROW_NUMBER() OVER (ORDER BY @sortExpression) as rowNumber,
            products.pid,
            brands.bid,
            brands.brand_name,
            products.model,
            products.name,
            products.searchedprice
            products.status

FROM products WITH (NOLOCK)
            JOIN brands WITH (NOLOCK) ON products.bid=brands.bid

WHERE   
    (
           CONTAINS(products.*,@inflectionSearchText)
           OR
           CONTAINS(brands.Brand_Name,@inflectionSearchText)
    )
    AND
      products.productstatus=1
    AND 
      brands.brandstatus=1
    AND
      products.searchedprice BETWEEN @fromprice TO @toprice

),
totalCount AS 
(SELECT COUNT(*) Total FROM search_results)

SELECT * 
FROM search_results, totalCount
WHERE 
     rowNumber BETWEEN ((@pageNumber-1)*@pageSize)+1 
         AND ((@pageNumber-1)*@pageSize)+@pageSize

ORDER BY products.model

Upvotes: 1

Views: 5789

Answers (1)

plalx
plalx

Reputation: 43718

You can use the following approach to retrieve the count without performing a sub-select.

SELECT some_field, COUNT(*) OVER() AS total
FROM some_table

SQLFIDDLE

Upvotes: 2

Related Questions