This is a migrated thread and some comments may be shown as answers.

Spreadsheet slows exponentially with repeated calculations

18 Answers 448 Views

Spreadsheet

This is a migrated thread and some comments may be shown as answers.

MikeWiese asked on 30 Aug 2016, 02:32 AM

Our application makes repeated calls to the spreadsheet control, sometimes numbering in the tens of thousands. The application logic sets cell values, then reads back the value of other, calculated cells.

Execution speed gets slower and slower over time. For example with a simple spreadsheet, I can run 500 iterations in roughly 10 seconds. The second block of 500 iterations takes 27 seconds and the 3rd block of 500 iterations takes 44 seconds. As a rough approximation, there is an exponential fall-off in performance, presumably due to memory-allocations internally.

The problem manifests itself in the cell SetValue() method. Initially, this method returns control to the calling program in less than 1ms. But after say, 1500 iterations, this method is taking 200 to 300ms.

In case it helps with reproducing the problem: this delay only occurs if SetValue() follows a call to the cell GetValue() method. If GetValue() does not get called, then repeated calls to the SetValue() method happen quickly, in <1ms

Am hoping there is an easy fix for this.

Regards,

Mike Wiese

Dunsborough

Western Australia

18 Answers, 1 is accepted

answered on 07 Sep 2016, 12:16 PM

SpreadProcessingPerformace.zip

Hello Mike Wiese,

We have tried to reproduce the performance issue with exponentially growing time consumption. However, in our test the time spent for each iteration seems to be constant and independent of the Worksheet data amount. I am attaching a sample console application that basically generates one long row/column with data and then with each iteration generates the next row/column by getting the previous row/column data. In both row and column iteration scenarios each iteration takes about 0.3 seconds.

Could you please try modifying this demo so that it replicates the exponential slowing you have mentioned? This way we would be able to reproduce the mentioned issue on our side and find the cause of it.

I am looking forward to your reply.

Regards,
Deyan
Telerik by Progress

Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.

answered on 09 Sep 2016, 07:40 AM

telerikworkbooktest.zip

Hi Deyan, I've attached a simple console app that loads an xlsx file then then loops through, calling SetValue() and GetValue() in a loop. It accumulates the time spent in the SetValue and the GetValue calls, and reports those values every 100 iterations, before resetting the stopwatches.

Here is a sample run from my machine:

Loading test excel file
File ExcelMineEconomicsModel.xlsx has been loaded
Reporting cumulative time every 100 iterations
Block 1: Spent 212ms in SetValue and 275ms in GetValue. Iterations = 100
Block 2: Spent 699ms in SetValue and 236ms in GetValue. Iterations = 200
Block 3: Spent 1202ms in SetValue and 254ms in GetValue. Iterations = 300
<snip...>
Block 48: Spent 22686ms in SetValue and 300ms in GetValue. Iterations = 4800
Block 49: Spent 27000ms in SetValue and 316ms in GetValue. Iterations = 4900
Block 50: Spent 23209ms in SetValue and 321ms in GetValue. Iterations = 5000
Test is completed

As you can see, the time spent in GetValue is pretty constant. Whereas the time spent in SetValue is about 100 times slower by then end. Interestingly, I did not see 100% processor utilisation on any of my logical processors - my machine did not seem to be particularly working hard.

Our application is based on CalcEnginePlus, and our users are used to running half a million iterations in a matter of 5-10 seconds. We are trying to add spreadsheet support, and now we see that by the end of the above test it's taking 23 seconds to run 100 iterations.

Regards,

Mike

Accepted

answered on 28 Oct 2016, 07:29 PM

spreadsheet-test-new-dlls.png

Mike and Nikolay:

I have been having this same issue and have been waiting for SP1 to get the fix. I have upgraded my project to SP1 and it seems somewhat better, but I think the problem is still there.

I downloaded the test project that you created (thanks very much!) and ran tests with the new dlls. I changed the reportingBlocksize to 1,000 and the total to 30,000. (I have attached a screenshot of the results). It looks to me like the increase is not as bad as it was, but by the end of the run, it's still 25-30 times slower. So, I just wanted to see if you are seeing a similar result. And I wonder if there is anything that I can do to "reset" this periodically so that it does not get so slow.

answered on 01 Nov 2016, 02:21 AM

Hi Mark, I'm seeing exactly the same as you.

I just got a chance to try it out this morning, rebuilding the test app against the 2016.3.1021 dlls. The performance is certainly much improved from before, but something is still leaking/increasing. With reportingBlocksize set to 100 and 500 iterations (the same setting as the above 9-Sep post), I now get

Loading test excel file
File ExcelMineEconomicsModel.xlsx has been loaded
Reporting cumulative time every 100 iterations
Block 1: Spent 43ms in SetValue and 367ms in GetValue. Iterations = 100
Block 2: Spent 25ms in SetValue and 279ms in GetValue. Iterations = 200
Block 3: Spent 29ms in SetValue and 270ms in GetValue. Iterations = 300
...
Block 48: Spent 104ms in SetValue and 236ms in GetValue. Iterations = 4800
Block 49: Spent 103ms in SetValue and 230ms in GetValue. Iterations = 4900
Block 50: Spent 106ms in SetValue and 236ms in GetValue. Iterations = 5000
Test is completed

So a smaller but still steady increase.

Your settings of reportingBlocksize = 1000 and 30000 iterations tease out this effect in a much more pronounced fashion:

Reporting cumulative time every 1000 iterations
Block 1: Spent 307ms in SetValue and 2807ms in GetValue. Iterations = 1000
Block 2: Spent 603ms in SetValue and 3083ms in GetValue. Iterations = 2000
Block 3: Spent 703ms in SetValue and 2630ms in GetValue. Iterations = 3000
...
Block 28: Spent 6374ms in SetValue and 2507ms in GetValue. Iterations = 28000
Block 29: Spent 7837ms in SetValue and 2563ms in GetValue. Iterations = 29000
Block 30: Spent 9029ms in SetValue and 2643ms in GetValue. Iterations = 30000
Test is completed

So for me, too, the performance is degrading by a factor of 25-30 times, just like you found.

Telerik people, do you see the same thing?

answered on 01 Nov 2016, 09:52 PM

Aylin:

Thanks for your response. I added a note to the bug report to add some comments. (I don't think that the bug description is exactly correct, because it seems to happen even with a very small spreadsheet without a lot of formulas).

https://feedback.telerik.com/Project/143/Feedback/Details/204782-spreadprocessing-setting-values-to-cells-referred-from-formulas-causes-performan

I am also concerned that this looks like a new issue, but it is not. We have been waiting for SP1 to get a fix, and now that it's out, we have discovered that while it is better, the problem still exists. So, I hope that this can be addressed soon and made available as a hotfix.

Thanks,

Mark

answered on 04 Nov 2016, 02:06 AM

Actually, the test spreadsheet DOES contain named ranges, even though we don't make explicit reference to them in the test program. As a test, I converted all the formulas in the spreadsheet to use explicit cell references instead of named ranges. At this point the results support what Aylin said; the test program now showed that the time spent in SetValue now stayed constant, instead of increasing.

So it sounds very promising that Telerik have identified a repro for this issue, and can characterise why it's occurring. Hopefully a hotfix is not far away.

For those of us out on the front lines it's not a practical workaround to ask our customers to rewrite their spreadsheets to get rid of named ranges. Some of these spreadsheets embody several man-years of development; they're non-trivial. And it's not practical to convert the named ranges to use relative cell references - I tried that using Excel's Name Manager and it just breaks everything in the spreadsheet.

answered on 04 Nov 2016, 09:56 PM

spreadsheet-test-results-20161104.png

Mike and Aylin:

Thanks for the suggestion Mike. I did what you suggested and added my spreadsheet into your test program. The results that I see from that test are different, in that the SetValue() time does not get slower as it goes, but it seems slow from the start. The time to do 1,000 iterations of SetValue is about 12 seconds, while the time to do 1,000 iterations of GetValue is about 20 milliseconds.

In my real program, I am building the spreadsheet dynamically, so it does get continuously slower as it gets bigger. On my larger spreadsheets, it gets painfully slow, about 10 minutes.

I realize that the SetValue function may be slower, but it is 600 times slower than GetValue, and the speed is related to the size of the spreadsheet, so hopefully this can be fixed. I am going to upload the modified test program in a ticket, in case it can be used for testing by Telerik.

I have attached a screenshot of my test results.

Thanks,

Mark