Performance Issues loading large data set into c# GridView

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

Method 2

You need to institute database paging.

This will involve creating custom sorting, custom filtering, and custom paging, but it will dramatically increase the performance of your code, as you’ll be retrieving only a pages-worth of data at a time from the DB, instead of all 17,000 rows at a time.

I implemented this in a banking application that was designed to show, sort, and filter hundreds of thousands of loans. The answer is too complicated to give a simple example for, but start by researching database paging. Use LINQ, it’ll give you simple Take and Skip methods to implement the paging easiest.

Method 3

You should look into caching the query on the web server, especially if it’s infrequently updated. That way, all the clients can simply surf off the cache instead of hitting the database mercilessly.

See this SO question

Method 4

Really, loading a 17k record once is not useful even user can not see the whole 17k records at once. I would suggest you to use pagination along with DataTable.Merge function which helps to load records in chunks and append the new fetched data into previous one. I just did a quick test and found a solution. Give this a try.

Method 5

There is not solution pagination is slow and will call database again and again,
So you need to
put

<style>.body{display:none;}</style>

start of the page
and at the end of the page

<style>.body{display:block;}</style>

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0