Using SQL for Gridview Paging vs Caching

Posted on 2009-12-22
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
    >> 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.
    LVL 9

    Expert Comment

    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

    LVL 14

    Assisted Solution

    by:Dhanasekaran Sengodan
    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

    Excellent responses!  Thanks guys!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now