Excel Data Binding Performance

I just updated my Excel VSTO code to use data binding (via ListObject) to a DataSet. Prior to this, I was putting data into a 2-dimensional array and setting it to a range. Now the screen update is significantly slower. We often times will have large DataSets. In one example, I had a DataSet with 10,000 rows, and 20 columns (with floating point data). With data binding, it took close to 1.5 minutes (90 seconds) for the screen to refresh. When I set the range directly with a 2-dimensional array, it took less than 5 seconds.

While I fully expect it to take longer with data binding, I really didn't expect it to be that much worse. Am I forced to choose between the convenience of data binding vs. performance Any suggestions

Thanks much!

-- Julie

Answer this question

Excel Data Binding Performance

mr_humphrey
Can anyone else share their performance experience using ListObject data binding to a DataSet

If I cannot resolve this, I will go back to setting the data using a 2-dimensional array. What I would still want is for the data that I assign to the range to be an Excel List, but without the data binding. Can someone tell me how to do that

Thanks in advance!
CFutino
Did you try turning off the screen updating I find that improves performance significantly when lots of rows are being affected.

this.Application.ScreenUpdating = false;

...<do your databinding>...

this.Application.ScreenUpdating = true;

The downside to this is that the app can appear to be hung even though it is not since it doesn't seem to be doing anything during that period
neil mosafi
Hi Julie,
How you set a 2-dimensional array to a range
HelpMePleaseeee
Thanks for all of the feedback.

Adding ScreenUpdating=false didn't help in this case. It took the same amount of time. It's not like the screen is updating incrementally -- it just sits blank for a long amount of time and then when it updates the screen, that's very quick.

At this point, I've decided to use a use a ListObject without data binding. I would still love to hear more ideas or to understand what is going on that is making the data binding so slow.

Thanks again!
nassir _dub
If you don't need 2-way data binding you should call
ListObject.Disconnect after the datasource is set.

2-way data binding is much more expensive than 1-way databinding.
Bobby Scott
See this post for examples in VB and C# for using the array method.
adarsh.mathur

Hi Julie,

To achieve 2-way data binding, the ListObject has to do a number of steps that are time intensive before it displays the data. One of these is to set the ID property of every cell in the first column and the first row of the ListObject. This is done before we populate the list with data and is a very time intensive operation.

int numberOfRows = this.List1.ListRows.Count;

for (int counter = 1; counter <= numberOfRows; counter++)

{

Excel.Range cell = (Excel.Range)(this.List1.Range[counter, 1]);

cell.ID = counter.ToString();

}

Above is what that ID setting code looks like. As you can see, the larger the number of rows in the data, the bigger the hit on data binding setup time.

Hope this helps understand the less than ideal performance.

Thanks

Mohit Gupta.
Ralley
I ran into the same thing about turning off screen updating and stopped using it. It gives the impression that the application is locked up.

The biggest performance increase I ever got was to place all the data into an array, set the proper range attributes and feed it all at once to the sheet. For me the individual calls to cells and rows, were the real overhead and once that bottleneck was removed...the performance difference was tenfold!
Excel Data Binding Performance

Answer this question