装载大量数据的性能问题设置到C＃的GridView

问题描述

好吧，

在测试相对较小的数据集到我的GridView控件，所有一直很好。不过，我现在已经搬进了正确的UAT并试图至17,000记录加载到我的网格，这就从根本上带来了我的web应用程序嘎然而止。

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.

在code的网格是：

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.

推荐答案

加载17000条记录在一个查询是什么杀了你。我强烈建议你分页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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！