Loading DataGrid with Performance Issues

[John Richardson]

I've been bothered for some time about my DataGrid not populating my rows
very quickly. I have about 10K rows loading into the grid.

I create a datatable dt with 2 columns, an ID and a display. The ID is a
member of the keys array.
I then create a DataView dv over the table, and sort it by Display and ID
column (in case of duplicate Display).
I then set my DataGrid.DataSource = dv;

I then load the datatable with my rows, and this is what I find is very
interesting, and any explanation would be very appreciated:

When the form loads, I call a method LoadList(). In this method, I test to
see if the DataGrid.DataSource is null, and then I set it if TRUE.
If the DataGrid.DataSource has already been set, then I leave it alone.
I retrieve the datatable from the source, and load the table.

I can also call LoadList from a user request to refresh the grid.

At the Form.Load the rows are added very quickly to my datatable.
At the user request, the rows are added at a factor of speed over 1000x
slower, and the speed decreases as n, the number of rows, increases.

I then removed my test to see if the DataGrid.DataSource is null, and now I
RESET the datasource each time the LoadList() method is called. So, I
recreate my DataTable, Styles each time. And, my speed is lightning fast
again. Apart from resetting the datasource, all the code is the same.

I have a feeling that it could be related to either sorting, or some kind of
event handling firing for each row in the grid each time the grid is added
to. It has to be something related to the size of n... but I don't know
what it could be. Anyone with any experience with this?

 

[Nicholas Paldino [.NET/C# MVP]]

John,

You are pretty much right in your guess. What you want to do is set the
data source to null, or create a new instance of the table/view, and then
bind to that.

The reason for the slowdown is that every row that is modified in some
way will have to be refreshed in the grid somehow (every change triggers a
refresh of the view). On top of that, the sorting might be affected, as you
suspect.

So, just create a new table/view, and then attach that to the data
source. Don't bother trying to update the original when you know you are
going to bulk load the data (if you were changing a handful of values, I
would say this is fine, but since you are essentially changing everything in
the table, it's a bad idea).

Hope this helps.

 

[John Richardson]

Thanks for the reply, but I am still a bit confused or maybe just annoyed.
Isn't there a way to achieve the same result without re-assigning a new
datatable? There seems to be a wealth of methods/properties for indicating
that event handling should be suspended. The following is my preparation
before loading the grid, and I guess I'm surprised that it isn't enough:

CurrencyManager c = this.BindingContext[this.DataSource] as CurrencyManager;
if (c != null)
c.SuspendBinding();
this.SuspendLayout
this.Visible = fa;se
dt.BeginLoadData
dt.Clear
....Loading here

and the display is still being updated after each row is being added?!
haha. What I find confusing, I guess, is that the datasource is already set
to the grid when I load it, so regardless of whether or not there were
already rows in the grid or not should not seemingly affect the loading of
the table? And if it's the sorting, then I understand it even less.

Although I already know the solution, I really hate it when I don't
understand why. I guess there is some kind of optimizations happening under
the hood that have a negative impact in this case. Oh well.

John,

You are pretty much right in your guess. What you want to do is set
the data source to null, or create a new instance of the table/view, and
then bind to that.

The reason for the slowdown is that every row that is modified in some
way will have to be refreshed in the grid somehow (every change triggers a
refresh of the view). On top of that, the sorting might be affected, as
you suspect.

So, just create a new table/view, and then attach that to the data
source. Don't bother trying to update the original when you know you are
going to bulk load the data (if you were changing a handful of values, I
would say this is fine, but since you are essentially changing everything
in the table, it's a bad idea).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I've been bothered for some time about my DataGrid not populating my rows
very quickly. I have about 10K rows loading into the grid.

I create a datatable dt with 2 columns, an ID and a display. The ID is a
member of the keys array.
I then create a DataView dv over the table, and sort it by Display and ID
column (in case of duplicate Display).
I then set my DataGrid.DataSource = dv;

I then load the datatable with my rows, and this is what I find is very
interesting, and any explanation would be very appreciated:

When the form loads, I call a method LoadList(). In this method, I test
to see if the DataGrid.DataSource is null, and then I set it if TRUE.
If the DataGrid.DataSource has already been set, then I leave it alone.
I retrieve the datatable from the source, and load the table.

I can also call LoadList from a user request to refresh the grid.

At the Form.Load the rows are added very quickly to my datatable.
At the user request, the rows are added at a factor of speed over 1000x
slower, and the speed decreases as n, the number of rows, increases.

I then removed my test to see if the DataGrid.DataSource is null, and now
I RESET the datasource each time the LoadList() method is called. So, I
recreate my DataTable, Styles each time. And, my speed is lightning fast
again. Apart from resetting the datasource, all the code is the same.

I have a feeling that it could be related to either sorting, or some kind
of event handling firing for each row in the grid each time the grid is
added to. It has to be something related to the size of n... but I
don't know what it could be. Anyone with any experience with this?

 

[Nicholas Paldino [.NET/C# MVP]]

John,

There might be, but honestly, it isn't worth it. What do you achieve by
NOT assigning a new data table? You end up with a bunch of extra code to
perform virtually the same operation (or at the least, which has pretty much
the same effect).

Suspending the binding, the layout, setting the visibility of the grid
to false... It's all excessive.

If you are insistent on not changing the data source, then check out the
BeginInit and EndInit methods on the data grid. They may help, as they are
the implementations to ISupportInitialize, which is meant to help with the
batch setting of properties.

If you are using .NET 2.0, then this documentation from the
SuspendBinding method should help as well:

This method prevents changes from being pushed into the data source but does
not prevent changes in the data source from affecting the bound controls.
Controls that use complex data binding, such as the DataGridView control,
update their values based on change events such as the ListChanged event.
Calling this method will not prevent these events from occurring. If you
need to suspend change events, you can bind your controls to a BindingSource
object and set the RaiseListChangedEvents property to false.

One would think that BeginLoadData and EndLoadData on the data table
would help here, but it doesn't prevent the change events from firing, so
they don't help.

Just curious, why do you not want to just bind to a new table?

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Thanks for the reply, but I am still a bit confused or maybe just annoyed.
Isn't there a way to achieve the same result without re-assigning a new
datatable? There seems to be a wealth of methods/properties for
indicating that event handling should be suspended. The following is my
preparation before loading the grid, and I guess I'm surprised that it
isn't enough:

CurrencyManager c = this.BindingContext[this.DataSource] as
CurrencyManager;
if (c != null)
c.SuspendBinding();
this.SuspendLayout
this.Visible = fa;se
dt.BeginLoadData
dt.Clear
...Loading here

and the display is still being updated after each row is being added?!
haha. What I find confusing, I guess, is that the datasource is already
set to the grid when I load it, so regardless of whether or not there were
already rows in the grid or not should not seemingly affect the loading of
the table? And if it's the sorting, then I understand it even less.

Although I already know the solution, I really hate it when I don't
understand why. I guess there is some kind of optimizations happening
under the hood that have a negative impact in this case. Oh well.

John,

You are pretty much right in your guess. What you want to do is set
the data source to null, or create a new instance of the table/view, and
then bind to that.

The reason for the slowdown is that every row that is modified in some
way will have to be refreshed in the grid somehow (every change triggers
a refresh of the view). On top of that, the sorting might be affected,
as you suspect.

So, just create a new table/view, and then attach that to the data
source. Don't bother trying to update the original when you know you are
going to bulk load the data (if you were changing a handful of values, I
would say this is fine, but since you are essentially changing everything
in the table, it's a bad idea).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I've been bothered for some time about my DataGrid not populating my
rows very quickly. I have about 10K rows loading into the grid.

I create a datatable dt with 2 columns, an ID and a display. The ID is
a member of the keys array.
I then create a DataView dv over the table, and sort it by Display and
ID column (in case of duplicate Display).
I then set my DataGrid.DataSource = dv;

I then load the datatable with my rows, and this is what I find is very
interesting, and any explanation would be very appreciated:

When the form loads, I call a method LoadList(). In this method, I test
to see if the DataGrid.DataSource is null, and then I set it if TRUE.
If the DataGrid.DataSource has already been set, then I leave it alone.
I retrieve the datatable from the source, and load the table.

I can also call LoadList from a user request to refresh the grid.

At the Form.Load the rows are added very quickly to my datatable.
At the user request, the rows are added at a factor of speed over 1000x
slower, and the speed decreases as n, the number of rows, increases.

I then removed my test to see if the DataGrid.DataSource is null, and
now I RESET the datasource each time the LoadList() method is called.
So, I recreate my DataTable, Styles each time. And, my speed is
lightning fast again. Apart from resetting the datasource, all the code
is the same.

I have a feeling that it could be related to either sorting, or some
kind of event handling firing for each row in the grid each time the
grid is added to. It has to be something related to the size of n...
but I don't know what it could be. Anyone with any experience with
this?

 

[John Richardson]

I actually don't care about creating a new DataTable, and I've already
switched it that way (much to the relief of my users, no doubt). The reason
it was done this way is historical... I used to use the Infragistics grid,
but it was old and buggy with certain things, so I replaced it with the
DataGrid. With the Inf. grid, resetting the datasource would reset the
layout (column widths, specifically), which wasn't desirable, and
clearing/reloading the Datatable was fast. The slowness is really
unexpected with the MS datagrid.

I agree with the code being excessive. TBH, some of it is debugging code to
figure out how to get it to load faster. I'll probably remove it now.

As for sending ListChanged events, because I set the datasource BEFORE
loading the grid in both cases, I don't quite see how having a new datatable
or a cleared datatable would make such a dramatic difference in loading
times (ie: they should both be slow!?). I'm going to chalk it up to a
gotcha, and remember it for the next time.

Thanks alot for the feedback, though.

John,

There might be, but honestly, it isn't worth it. What do you achieve
by NOT assigning a new data table? You end up with a bunch of extra code
to perform virtually the same operation (or at the least, which has pretty
much the same effect).

Suspending the binding, the layout, setting the visibility of the grid
to false... It's all excessive.

If you are insistent on not changing the data source, then check out
the BeginInit and EndInit methods on the data grid. They may help, as
they are the implementations to ISupportInitialize, which is meant to help
with the batch setting of properties.

If you are using .NET 2.0, then this documentation from the
SuspendBinding method should help as well:

This method prevents changes from being pushed into the data source but
does not prevent changes in the data source from affecting the bound
controls. Controls that use complex data binding, such as the DataGridView
control, update their values based on change events such as the
ListChanged event. Calling this method will not prevent these events from
occurring. If you need to suspend change events, you can bind your
controls to a BindingSource object and set the RaiseListChangedEvents
property to false.

One would think that BeginLoadData and EndLoadData on the data table
would help here, but it doesn't prevent the change events from firing, so
they don't help.

Just curious, why do you not want to just bind to a new table?

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Thanks for the reply, but I am still a bit confused or maybe just
annoyed. Isn't there a way to achieve the same result without
re-assigning a new datatable? There seems to be a wealth of
methods/properties for indicating that event handling should be
suspended. The following is my preparation before loading the grid, and
I guess I'm surprised that it isn't enough:

CurrencyManager c = this.BindingContext[this.DataSource] as
CurrencyManager;
if (c != null)
c.SuspendBinding();
this.SuspendLayout
this.Visible = fa;se
dt.BeginLoadData
dt.Clear
...Loading here

and the display is still being updated after each row is being added?!
haha. What I find confusing, I guess, is that the datasource is already
set to the grid when I load it, so regardless of whether or not there
were already rows in the grid or not should not seemingly affect the
loading of the table? And if it's the sorting, then I understand it even
less.

Although I already know the solution, I really hate it when I don't
understand why. I guess there is some kind of optimizations happening
under the hood that have a negative impact in this case. Oh well.

John,

You are pretty much right in your guess. What you want to do is set
the data source to null, or create a new instance of the table/view, and
then bind to that.

The reason for the slowdown is that every row that is modified in
some way will have to be refreshed in the grid somehow (every change
triggers a refresh of the view). On top of that, the sorting might be
affected, as you suspect.

So, just create a new table/view, and then attach that to the data
source. Don't bother trying to update the original when you know you
are going to bulk load the data (if you were changing a handful of
values, I would say this is fine, but since you are essentially changing
everything in the table, it's a bad idea).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I've been bothered for some time about my DataGrid not populating my
rows very quickly. I have about 10K rows loading into the grid.

I create a datatable dt with 2 columns, an ID and a display. The ID is
a member of the keys array.
I then create a DataView dv over the table, and sort it by Display and
ID column (in case of duplicate Display).
I then set my DataGrid.DataSource = dv;

I then load the datatable with my rows, and this is what I find is very
interesting, and any explanation would be very appreciated:

When the form loads, I call a method LoadList(). In this method, I
test to see if the DataGrid.DataSource is null, and then I set it if
TRUE.
If the DataGrid.DataSource has already been set, then I leave it alone.
I retrieve the datatable from the source, and load the table.

I can also call LoadList from a user request to refresh the grid.

At the Form.Load the rows are added very quickly to my datatable.
At the user request, the rows are added at a factor of speed over 1000x
slower, and the speed decreases as n, the number of rows, increases.

I then removed my test to see if the DataGrid.DataSource is null, and
now I RESET the datasource each time the LoadList() method is called.
So, I recreate my DataTable, Styles each time. And, my speed is
lightning fast again. Apart from resetting the datasource, all the
code is the same.

I have a feeling that it could be related to either sorting, or some
kind of event handling firing for each row in the grid each time the
grid is added to. It has to be something related to the size of n...
but I don't know what it could be. Anyone with any experience with
this?

 

[Nicholas Paldino [.NET/C# MVP]]

John,

While I can sympathize with your experience with the Infragistics grid,
the correlation you make is like saying all red objects are apples (the
point being, that most apples are red, but the inverse does not apply).

My assumption with you setting the data source was that you would
actually add a completely filled new table as your data source, preventing
the need for excessive updates to the grid upon each addition of a row to
the grid.

However, this doesn't make much of a difference. Clearing a table
actually requires a bit of overhead. It has to cycle through all the rows,
and set the properties of each row to dissociate it from the data table. It
also performs an operation where it cycles through every column, for every
row (producing an operation that takes R * C, without counting the
additional R operations). This can take a little bit of time, depending on
how large the data set is. For your initial example of 10K rows and 2
columns, thats 30K iterations just to clear.

Then, you have to take the time to re-load the data. If you use a new
table, you eliminate the cost from before. However, the overhead of the
events firing and the grid picking up on each change exists (for 10K
records, that's a lot of updates).

Compare that to just the initial rendering operation that you perform if
you load the data table separately (still R operations, but less than R * N
(where N is a constant cost of firing the change event which the data grid
picks up and acts on)), and then you set the data source to that table, it's
a big difference.

Hopefully, this is a little more along the lines of what you were
looking for.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I actually don't care about creating a new DataTable, and I've already
switched it that way (much to the relief of my users, no doubt). The
reason it was done this way is historical... I used to use the Infragistics
grid, but it was old and buggy with certain things, so I replaced it with
the DataGrid. With the Inf. grid, resetting the datasource would reset the
layout (column widths, specifically), which wasn't desirable, and
clearing/reloading the Datatable was fast. The slowness is really
unexpected with the MS datagrid.

I agree with the code being excessive. TBH, some of it is debugging code
to figure out how to get it to load faster. I'll probably remove it now.

As for sending ListChanged events, because I set the datasource BEFORE
loading the grid in both cases, I don't quite see how having a new
datatable or a cleared datatable would make such a dramatic difference in
loading times (ie: they should both be slow!?). I'm going to chalk it up
to a gotcha, and remember it for the next time.

Thanks alot for the feedback, though.

John,

There might be, but honestly, it isn't worth it. What do you achieve
by NOT assigning a new data table? You end up with a bunch of extra code
to perform virtually the same operation (or at the least, which has
pretty much the same effect).

Suspending the binding, the layout, setting the visibility of the grid
to false... It's all excessive.

If you are insistent on not changing the data source, then check out
the BeginInit and EndInit methods on the data grid. They may help, as
they are the implementations to ISupportInitialize, which is meant to
help with the batch setting of properties.

If you are using .NET 2.0, then this documentation from the
SuspendBinding method should help as well:

This method prevents changes from being pushed into the data source but
does not prevent changes in the data source from affecting the bound
controls. Controls that use complex data binding, such as the
DataGridView control, update their values based on change events such as
the ListChanged event. Calling this method will not prevent these events
from occurring. If you need to suspend change events, you can bind your
controls to a BindingSource object and set the RaiseListChangedEvents
property to false.

One would think that BeginLoadData and EndLoadData on the data table
would help here, but it doesn't prevent the change events from firing, so
they don't help.

Just curious, why do you not want to just bind to a new table?

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Thanks for the reply, but I am still a bit confused or maybe just
annoyed. Isn't there a way to achieve the same result without
re-assigning a new datatable? There seems to be a wealth of
methods/properties for indicating that event handling should be
suspended. The following is my preparation before loading the grid, and
I guess I'm surprised that it isn't enough:

CurrencyManager c = this.BindingContext[this.DataSource] as
CurrencyManager;
if (c != null)
c.SuspendBinding();
this.SuspendLayout
this.Visible = fa;se
dt.BeginLoadData
dt.Clear
...Loading here

and the display is still being updated after each row is being added?!
haha. What I find confusing, I guess, is that the datasource is already
set to the grid when I load it, so regardless of whether or not there
were already rows in the grid or not should not seemingly affect the
loading of the table? And if it's the sorting, then I understand it
even less.

Although I already know the solution, I really hate it when I don't
understand why. I guess there is some kind of optimizations happening
under the hood that have a negative impact in this case. Oh well.



in message John,

You are pretty much right in your guess. What you want to do is set
the data source to null, or create a new instance of the table/view,
and then bind to that.

The reason for the slowdown is that every row that is modified in
some way will have to be refreshed in the grid somehow (every change
triggers a refresh of the view). On top of that, the sorting might be
affected, as you suspect.

So, just create a new table/view, and then attach that to the data
source. Don't bother trying to update the original when you know you
are going to bulk load the data (if you were changing a handful of
values, I would say this is fine, but since you are essentially
changing everything in the table, it's a bad idea).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I've been bothered for some time about my DataGrid not populating my
rows very quickly. I have about 10K rows loading into the grid.

I create a datatable dt with 2 columns, an ID and a display. The ID
is a member of the keys array.
I then create a DataView dv over the table, and sort it by Display and
ID column (in case of duplicate Display).
I then set my DataGrid.DataSource = dv;

I then load the datatable with my rows, and this is what I find is
very interesting, and any explanation would be very appreciated:

When the form loads, I call a method LoadList(). In this method, I
test to see if the DataGrid.DataSource is null, and then I set it if
TRUE.
If the DataGrid.DataSource has already been set, then I leave it
alone.
I retrieve the datatable from the source, and load the table.

I can also call LoadList from a user request to refresh the grid.

At the Form.Load the rows are added very quickly to my datatable.
At the user request, the rows are added at a factor of speed over
1000x slower, and the speed decreases as n, the number of rows,
increases.

I then removed my test to see if the DataGrid.DataSource is null, and
now I RESET the datasource each time the LoadList() method is called.
So, I recreate my DataTable, Styles each time. And, my speed is
lightning fast again. Apart from resetting the datasource, all the
code is the same.

I have a feeling that it could be related to either sorting, or some
kind of event handling firing for each row in the grid each time the
grid is added to. It has to be something related to the size of n...
but I don't know what it could be. Anyone with any experience with
this?