Using SQL for Gridview Paging vs Caching

I came across an article which uses a stored procedure to return a specific result set for paging in a GridView (see code)

Instead of returning 10,000 rows of data, the stored proc will return only 20 of 10,000 rows (which you then bind to the GridView) assuming your GridView is set up to display only 20 rows per page.

I'm curious what advantages in efficiency this has over caching all 10,000 rows?  I read that the built-in GridView paging becomes inefficent when dealing with large result sets.  Would it be ideal to use SQL for paging rather than using caching and assigning tens of thousands of rows to the GridView when you only indend on displaying 20.  Is this true?
CREATE PROCEDURE [usp_GetProducts] 
@startRowIndex int,
@maximumRows int, 
@totalRows int OUTPUT


DECLARE @first_id int, @startRow int

SET @startRowIndex =  (@startRowIndex - 1)  * @maximumRows

IF @startRowIndex = 0 
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = ProductID FROM Products ORDER BY ProductID

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT ProductID, ProductName FROM Products WHERE 
ProductID >= @first_id 

-- GEt the total rows 

SELECT @totalRows = COUNT(ProductID) FROM Products

Open in new window

Who is Participating?
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
>> I'm curious what advantages in efficiency this has over caching all 10,000 rows?  

Yes.. It is efficient to cache 20 records instead of 10000 records because

* Ideally an user would be viewing only 1 to 5 pages in a gridview ie. merely 100 rows by navigating through several pages..
* Caching 10000 rows for using only 100 rows is a memory intensive operation for the application thereby reducing the application performance.
* Loading 20 rows would be much faster compared to caching 10000 rows and then displaying 20 rows in the first page..

In this case, caching 10000 rows is not optimal..
Eventhough the user is going to navigate through all pages, it would hit the database more no. of times but a less resource intensive operation thereby both your database and application resources are utilized efficiently..

Hope this clarifies.
Because unwanted rows are return by the sql server to application when application only display some of then like 20 or whatever.  so why should that much extra row are transfer to application that also effect the network traffic and slow down to load in datagridview or any other control.
It's best practice to return only those number of row that are currently required or display to user.
Dhanasekaran SengodanConnect With a Mentor Commented:
use these query to show 20 from 10000 records and its also total record count

declare @PageIndex int
declare @PageSize int
set @PageIndex = 1
set @PageSize = 10

;with pagination as (SELECT a.*, row_number() over (ORDER BY ProductID desc) as rowNo from (SELECT ProductID, ProductName FROM Products ) a)
select *, (select count(*) from pagination) as totalResults FROM pagination
WHERE rowNo between ((@PageIndex - 1) * @PageSize) + 1 and @PageIndex * @PageSize

nightshadzAuthor Commented:
Excellent responses!  Thanks guys!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.