how to return number of records efficiently in SQL?

Question

Hello everyone,

I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise. I am using the following code to return a part of data for a query to implement paging (i.e. page up/down to show a part of result at each page, like Google search result paging) on my web application (I use pageCount as number of results showed on each page, and startPos as the start number of result). For example, pageCount 10 means show 10 results for each page, startPos = 0 means the first page, startPos = 1 means the 2nd page, etc.

My question is how to get the total number of results efficiently in my scenario? My major concern is how to implement paging (i.e. touch only a part of result) and at the same time retrieve the total number of results?

SELECT *
 FROM   (SELECT
    t.foo, t.goo, ROW_NUMBER() OVER (order by t.zoo DESC ) AS rowNum
   FROM
    dbo.mycorp  t

   WHERE
    (t.foo LIKE '%'+@search+'%'
    or t.foo  LIKE '%'+@search+'%'
    )
    ) tt
    WHERE  tt.rowNum between @startPos and  @pageCount + @startPos-1

thanks in advance, George

Answer 1

This will mean a redundant column, with the same value for every row:

WITH cte AS (
    SELECT t.foo, 
           t.goo, 
           ROW_NUMBER() OVER (ORDER BY t.zoo DESC ) AS rowNum,
           (SELECT COUNT(*)
              FROM dbo.mycorp
             WHERE t.foo LIKE '%'+@search+'%'
                OR t.foo  LIKE '%'+@search+'%') AS total_count
      FROM dbo.mycorp  t
     WHERE t.foo LIKE '%'+@search+'%'
        OR t.foo  LIKE '%'+@search+'%')
SELECT c.foo,
       c.goo,
       c.total_count
  FROM cte c
 WHERE c.rowNum BETWEEN @startPos 
                    AND @pageCount + @startPos-1

A more efficient means of searching text is to use SQL Server's Full Text Search (FTS) functionality.

Answer 2

I would think about using the multiple recordsets if your data access technology supports it (I know ADO.NET does support this).

Include the following after your query

SELECT COUNT(*) AS TotalRecordCount
 FROM   dbo.mycorp  t 
   WHERE 
    (t.foo LIKE '%'+@search+'%' 
    or t.foo  LIKE '%'+@search+'%' 
    ) 
    ) tt

In your data access layer, you would need to switch to the next recordset after processing the search results in order to retrieve the total count. Using ADO.NET, this would require calling dataReader.NextResult().

The most important thing is to measure performance. I have found that in some cases, using a separate select statement rather than a sub-query can result in faster performance as the Query optimizer is able to better optimize the query. Either way, you will need suitable indexing on the search column.