Loading DataTable Slow When Bound to DataGridView.Datasource

Question

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/

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

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?

Answer 1

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.

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
}

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.

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.

Edit

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

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.