Performane improvement in getting paged data for asp.net grid

Question

I am encountering a performance problem in the development of current UI. The problem, I suppose, is however general.

I have a page with a simple asp.net grid. Grid will display data from a table based on certain search criteria. Moreover, grid has fixed page size (say 10). There is pager at the bottom which can be used to navigate b/w pages. On the back end, whenever search button is pressed a stored procedure is called which returns the desired data.

Stored procedure has parameters like currentpageIndex, pagesize, other search criteria, etc. Here is a pseudo code for sp:

-- SP begins
-- calculate the page index range to return required using current page index and page size
-- query the table in a CTE and do all filtering. Also calculate row numbers so that
-- correct record range can be returned. 
-- use the cte to return the correct record based on the row number calculated in CTE
-- SP ends

I have following problems/queries in this approach

1. When Db table size is large (say 10 million records), performance degrades and this approach becomes impractical.
2. Is using table variable or a temporary table more useful?
3. Is there any other efficient way to get paged data from database?

Hi Dan, the article provided a new insight for calculation of total rows. Really helpful. Thanks.

But still is there better way than using CTE when data is large?

Update: I have found few other performant approaches for efficiently getting paged records.

Answer 1

There's a good article on SqlServerCentral called SQL Server 2005 Paging – The Holy Grail that shows a few techniques for server-side paging. You will need to register to view it, though.

I know for really large result sets then software like Google will simply estimate how many rows will be returned, bypassing the need to get a count of all the rows returned.

Sorry, if I can't give more help.