将大数据集加载到 c# GridView 中的性能问题 [英] Performance Issues loading large data set into c# GridView

问题描述

好的，

一直在我的 GridView 中测试相对较小的数据集，并且一切正常.但是，我现在已经转入适当的 UAT 并尝试将 17,000 条记录加载到我的网格中，这基本上使我的网络应用程序陷入停顿.

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.

基本上，用户登录，并在验证后加载所有数据网格，其中一个包含 17k 记录.直到所有内容加载完毕，最终用户都在登录页面上左手操作.所以我需要修复它.

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.

网格的代码是:

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();
    }
 }

有没有办法增加加载时间?分页不是一种选择，因为我正在使用 JQuery 处理这个问题.

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

推荐答案

在一个查询中加载 17,000 条记录会让您丧命.我强烈建议对您的 gridview 进行分页.

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.
}

BindGrid() 方法将调用您的两个存储过程(一个获取完整的行数，另一个获取与当前页面相关的结果)

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)

附加阅读

这篇关于将大数据集加载到 c# GridView 中的性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！