Performance Issues loading large data set into c# GridView

Question

Ok,

been testing relatively small data sets into my GridView, and all has worked fine. However, i've now moved into proper UAT and have tried to load 17,000 records into my Grid, which has basically brought my web app to a grinding halt.

Basically, a user logs in, and upon validation all the data grids are loaded, one of which contains 17k records. Until everything loads the end user is left handing on the logon page. So i need to fix it.

The code for the Grids is:

DataTable dtValueDateCurrency = null;               
SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["Reporting"].ConnectionString);
using (conn)
{
    conn.Open();
    //Load all other grid data
    using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(TSQL1, conn))
    {
        dtValueDateSummary = new DataTable();
        sqlAdapter.Fill(dtValueDateSummary);
        grdValueDateSummary.DataSource = dtValueDateSummary;
        grdValueDateSummary.DataBind();
    }
 }

Is there a way to increase the load times? Pagination isn't an option, as i'm taking care of this with JQuery.

Answer 1

loading 17,000 records in one query is what's killing you. I highly suggest paging your gridview.

First you need to alter your Stored Procedure as follows.

ALTER PROCEDURE [dbo].[SomeTable_GetPagedResults] 
( 
        @StartRowIndex      int, 
        @MaximumRows        int 
) 

AS 
SET NOCOUNT ON 

Select 
    RowNum, 
    [ID], 
    [foo],
    [bar]
From 
    (Select 
        [ID], 
        [foo], 
        [bar], 
        Row_Number() Over(Order By [ID] Desc) As RowNum 
        From dbo.[SomeTable] t) 
As DerivedTableName 
Where RowNum Between @StartRowIndex And (@StartRowIndex + @MaximumRows) 

Now you have a pageable query.

You also want a query to get the complete row count.

ALTER PROCEDURE [dbo].[SomeTable_GetRowCount] 

AS 
SET NOCOUNT ON 

return (Select Count(ID) As TotalRecords From SomeTable) 

You'll bind your grid every time you change the page.

protected void gridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
  gridView1.PageIndex = e.NewPageIndex;
  BindGrid(); // this is whatever method you call to bind your data and execute your stored procedure.
}

And the BindGrid() method will call your two stored procedures (one to get the complete row count, and one to get the results pertaining to your current page)

Additional Reading