绑定到 DataGridView.Datasource 时加载数据表很慢 [英] Loading DataTable Slow When Bound to DataGridView.Datasource

问题描述

我找遍了所有，都找不到这个.我正在开发一个 Winforms UI，该 UI 正在拉出我需要在 DataGridView 中显示的大量行.我已经阅读了所有关于限制行数和分页的内容，我绝对没有好的方法可以做到这一点.基本上，我正在处理我在 Codeplex 上编写的 SQL Server 2008 扩展事件管理器的 TargetDataViewer 控件.

I've searched all over and I can't figure this one out. I am working on a Winforms UI that is pulling large volumes of rows that I need to display in a DataGridView. I have already read all about limiting row counts and paging and there is absolutely no good way for me to do this. Basically I am working on the TargetDataViewer control of the Extended Events Manager for SQL Server 2008 that I wrote on Codeplex.

http://extendedeventmanager.codeplex.com/

我只能根据特定目标及其呈现数据的方式做些什么.我想要做的是将已从目标读取的数据流式传输到 DataGridView 中，类似于 Profiler 或 SQL Server Management Studio 在数据流入时显示数据的方式.我重写了很多代码，并让 BackgroundWorker 拉取数据并将其处理为DataTable.如果不设置DataGridView.DataSource = DataTable，几分钟就可以加载300K+行数据到DataTable中，真的跑的很快.一旦我将 DataTable 添加到 DataSource，它就会变慢到几乎停止(而不是几分钟，同样的 300K 行可能需要 1/2 小时).

I am limited to what I can do based on the specific target and how it presents data. What I am trying to do is stream the data that has been read from a target into the DataGridView similar to how Profiler, or SQL Server Management Studio display data as it streams in. I rewrote a lot of code, and have a BackgroundWorker pulling data and processing it into a DataTable. If I don't set the DataGridView.DataSource = DataTable, I can load 300K+ rows of data into the DataTable in a few minutes, it really runs fast. As soon as I add the DataTable to the DataSource it slow to almost a halt (instead of a few minutes the same 300K rows can take a 1/2 hr).

我知道问题不在于我的处理代码，而是特定于绑定到 DataGridView.DataSource，我有时间代码来证明这一点.我不知道如何解决这个问题.对于性能，我可以在加载数据后后期将控件绑定到 DataTable，但这是一种非常糟糕的用户体验.我看到很多人在加载数据时抱怨 DataGridView 性能影响，所以这可能只是我遇到的一个限制?有什么想法吗?

I know that the problem isn't my processing code, it is specific to being bound to the DataGridView.DataSource, and I have timing code to prove this. I can't figure out how to get around this. For Performance I can late bind the control to the DataTable after the data is loaded, but that is a really crappy user experience. I see lots of people complaining about the DataGridView performance impact when loading data so this may just be a limitation I am stuck with? Any ideas?

推荐答案

想想当你用 DataReader 中的一行填充未绑定的 DataTable 时会发生什么:A DataRow 被创建，从 DataReader 填充，并添加到 Rows 集合.然后，当您创建绑定时，DataGridView 从表中提取数据并在屏幕上构建视图.

Think about what happens when you populate an unbound DataTable with one row from a DataReader: A DataRow gets created, populated from the DataReader, and added to the Rows collection. Then, when you create the binding, the DataGridView pulls data from the table and builds the view on the screen.

当您填充与 DataGridView 的绑定已启用的 DataTable 时会发生什么?一团糟的事件处理.每次更改绑定属性时，都会引发 property-changed 事件并且绑定控件处理它.这不是发生 300,000 次，而是每列发生 300,000 次.

What happens when you populate a DataTable whose binding to the DataGridView is enabled? A whole mess of event handling. Every time you change a bound property, the property-changed event gets raised and the bound control handles it. That's not happening 300,000 times, it's happening 300,000 times for each column.

如果把这个关掉，只偶尔更新绑定的控件怎么办?看看这个方法:

What if you turn this off, and only update the bound control occasionally? Look at this method:

private void PopulateDataTable()
{
    int rowCount = 10000;

    bindingSource1.RaiseListChangedEvents = false;
    for (int i = 0; i < rowCount; i++)
    {
        DataRow r = DT.NewRow();
        for (int j = 0; j < ColumnCount; j++)
        {
            r[j] = "Column" + (j + 1);
        }
        DT.Rows.Add(r);

        if (i % 500 == 0)
        {
            bindingSource1.RaiseListChangedEvents = true;
            bindingSource1.ResetBindings(false);
            Application.DoEvents();
            bindingSource1.RaiseListChangedEvents = false;
        }
    }
    bindingSource1.RaiseListChangedEvents = true
}

必须调用ResetBindings来强制更新绑定控件.这需要时间，因为您无法避免构建 DataGridViewRow 对象的成本，但取出事件是一项重大改进.在我的机器上，如果我填充绑定到 DataGridView 的 10 列、10000 行 DataTable，它需要 2900 毫秒.如果我一直关闭数据绑定，则需要 155 毫秒.如果我每 500 行重置一次绑定，则需要 840 毫秒.

You have to call ResetBindings to force the update of the bound control. This takes time, because you can't get around the cost of building the DataGridViewRow objects, but taking out the events is a significant improvement. On my machine, if I populate a 10-column, 10000 row DataTable that's bound to a DataGridView, it takes 2900 milliseconds. If I leave data-binding turned off the entire time, it takes 155 milliseconds. If I reset the bindings every 500 rows, it takes 840 milliseconds.

当然，如果我要填充 300,000 行表，我不会每 500 行重置一次绑定；我可能会在 500 行标记处执行一次，然后将其关闭，直到操作完成.但即使你这样做，你也需要经常调用 Application.DoEvents，这样 UI 才能响应事件.

Of course, if I were populating 300,000 row table, I wouldn't reset the bindings every 500 rows; I'd probably do it once at the 500-row mark and then turn it off until the operation completes. But even if you do this, you need to call Application.DoEvents every so often, so that the UI can respond to events.

编辑

别介意Application.DoEvents;如果您在后台任务中填充表格，则不需要这样做.

Never mind that bit about Application.DoEvents; you don't need to do that if you're populating the table in a background task.

但您确实需要确保在 BackgroundWorker 的 ProgressChanged 事件处理程序中重置绑定，而不是在 DoWork代码>方法.如果您在另一个线程上填充数据源时实际上让用户编辑绑定DataGridView中的数据，那么您将体验到一个痛苦的世界.

But you do need to make sure that you're resetting the bindings in the BackgroundWorker's ProgressChanged event handler, and not in the DoWork method. And you're going to experience a world of hurt if you actually let the user edit data in the bound DataGridView while you're populating its data source on another thread.

这篇关于绑定到 DataGridView.Datasource 时加载数据表很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！