[Last Call] Learn how to a build a cloud-first strategyRegister Now


Using SQL for Gridview Paging vs Caching

Posted on 2009-12-22
Medium Priority
Last Modified: 2012-05-08
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

Question by:nightshadz
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 400 total points
ID: 26110382
>> 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.

Expert Comment

ID: 26110383
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.
LVL 10

Accepted Solution

anv earned 1200 total points
ID: 26110453
LVL 14

Assisted Solution

by:Dhanasekaran Sengodan
Dhanasekaran Sengodan earned 400 total points
ID: 26111353
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


Author Closing Comment

ID: 31669306
Excellent responses!  Thanks guys!

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question