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.