How can we optimize the records coming in gridview?

I wanna bind atleast 30,000 records in a gridview from a sqlserver table. I want to bind only 50 records at a time with grid view. The grid has also paging and when I click next page then next 50 records should be shown and another next page's click another 50 records.

How can I optimize this thing. I want that If 30,000 records are binding then grid should be work as binded 50 records.

Thanks开发者_开发技巧,

There are many ways of doing so as others suggested. I would like to add some more to that

a) Handling from the backend (This is how I am doing in my current project)

--Usage   : USP_DataListing 0,12
ALTER PROCEDURE [dbo].[USP_DataListing] 
    -- Add the parameters for the stored procedure here
    (@StartIndex INT, @ItemsToDisplay INT)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @EndIndex INT
    SET @StartIndex = @StartIndex * @ItemsToDisplay
    SET @EndIndex = @StartIndex + @ItemsToDisplay

    BEGIN
        SELECT 
            SlNo
            ,Column(s)
            FROM (
        SELECT  
         ROW_NUMBER() OVER (ORDER BY GETDATE() DESC) AS SlNo
                ,i.Column(s)                
            FROM dbo.tblName i)X
            WHERE X.SlNo BETWEEN @StartIndex+1 AND @EndIndex
    END

2) If you are using dotnet version 3.0+ you can take help of linq's Take() and Skip()methods

Bind the GridView to an ObjectDataSource, and enable paging on both.

On the SQL side, use a CTE to select the rows you need. For example:

;WITH MyCTE ([row], [Item]) AS (
    SELECT ROW_NUMBER() OVER (ORDER BY Id) [row], [Item]
        FROM SourceTable
    )
    SELECT [row], [Item]
        FROM MyCTE 
        WHERE [row] BETWEEN @startrow AND @startrow + @pagesize - 1

In case it helps, I give a complete high-performance example of how to do this in my book, using async I/O and including code: Ultra-Fast ASP.NET.

You can bind the GridView to a LinqDataSource control. As far as I know when it handles paging it requests only the visible rows from database, not the whole table. There is good article on how to bind GridView to LinqDataSource here.

It is more efficient to handle the paging at the SQL server. If you just have the gridview handle the paging, or even a datasource running locally, it will load all 30,000 records to the front end from the database, then retrieve the records for the page.