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

Spreadsheet slows exponentially with repeated calculations

18 Answers 346 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
MikeWiese
Top achievements
Rank 1
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

Sort by
0
T. Tsonev
Telerik team
answered on 01 Sep 2016, 04:59 AM
Hello,

Ideally we'd need to see the exact test case to make sure we're troubleshooting the right issue.

That said, the first thing I'd check is that you're executing your updates in a batch. The spreadsheet has a lot of housekeeping tasks and batch updates suspend these until you're done with your updates.

I hope this helps.

Regards,
T. Tsonev
Telerik by Progress
 
Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.
 
0
MikeWiese
Top achievements
Rank 1
answered on 05 Sep 2016, 04:11 AM

This is happening server-side. So 'batch' doesn't apply. 

Having said that, we are bracketing our batches of SetValue() calls with calls to SuspendLayoutUpdate() and ResumeLayoutUpdate().

0
Deyan
Telerik team
answered on 07 Sep 2016, 12:16 PM
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.
 
0
MikeWiese
Top achievements
Rank 1
answered on 09 Sep 2016, 07:40 AM

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

0
Accepted
Nikolay Demirev
Telerik team
answered on 10 Sep 2016, 08:13 AM
Hi Mike,

I have managed to isolate and fix the issue. The fix will be released with the first Internal Build following the Official R3 2016 release, and it will be included in the R3 2016 SP1 release.

Regards,
Nikolay Demirev
Telerik by Progress
Do you need help with upgrading your AJAX, WPF or WinForms project? Check the Telerik API Analyzer and share your thoughts.
0
Mark
Top achievements
Rank 1
answered on 28 Oct 2016, 07:29 PM

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.

 

0
MikeWiese
Top achievements
Rank 1
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?

0
Aylin
Telerik team
answered on 01 Nov 2016, 03:52 PM
Hello,

I could confirm that we see the same results our side and I logged it in our bug tracking system. You could use the following link to subscribe to the related feedback item: https://feedback.telerik.com/Project/143/Feedback/Details/204782-spreadprocessing-setting-values-to-cells-referred-from-formulas-causes-performan. In this way, you will be notified about its progress and related changes.

Regards,
Aylin
Telerik by Progress
Do you need help with upgrading your WPF project? Try the Telerik API Analyzer and share your thoughts!
0
Mark
Top achievements
Rank 1
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

0
Aylin
Telerik team
answered on 03 Nov 2016, 04:04 PM
Hi Marc,

We were able to isolate the cause of the performance issue to named ranges used in the document, that have absolute cell references. As a you could try to use relative cell references instead.

Meanwhile, we will investigate the problem some more and will get back here with the findings. I will mark this thread as requiring answer.

Regards,
Aylin
Telerik by Progress
Do you need help with upgrading your WPF project? Try the Telerik API Analyzer and share your thoughts!
0
Mark
Top achievements
Rank 1
answered on 03 Nov 2016, 04:11 PM

Aylin:

I am not using named ranges, and the test program that Mike created (TelerikWorkbookTest.zip attached to this thread) does not use named ranges, so it looks like there is something else going on.  Please try with the test program if you can, as it is very simple test case.

Thanks very much for you prompt attention to this issue.

Mark

0
MikeWiese
Top achievements
Rank 1
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.

0
Mark
Top achievements
Rank 1
answered on 04 Nov 2016, 03:12 AM

Mike and Aylin:

Sorry for the misinformation, I did not realize that your spreadsheet had named ranges.  The thing is that mine does not have any named ranges, however, I do have eight-tabs and I have a lot of formulas that point to a cell or cells on a different tab. 

Here is an example formula:  ='Historical Data'!H14*(1+D14)+E14+'Additional Assumptions'!H14

Maybe this causes the same issue as named ranges.

Thanks to everyone for working on this.

Mark

0
MikeWiese
Top achievements
Rank 1
answered on 04 Nov 2016, 03:26 AM
Hey Mark, sounds like you might have uncovered a different mechanism for the same thing. Any chance you could upload a version of that test code that reproduces the problem using a (cut-down) version of your spreadsheet? It would suck if they fixed it for the named-range case, and it still didn't work for you.
0
Mark
Top achievements
Rank 1
answered on 04 Nov 2016, 09:56 PM

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

0
Aylin
Telerik team
answered on 07 Nov 2016, 04:48 PM
Hi,

We managed to partially improve the performance so that setting cell values does not get slower exponentially. The fix will be included in our next LIB release.

However in order to fix the issue fully we need to introduce some architectural changes but for now, we cannot commit a specific time frame when it will be fixed due to the low demand opposed to the complexity of the changes needed.

Let me know if I can be of any help.

Regards,
Aylin
Telerik by Progress
Do you need help with upgrading your WPF project? Try the Telerik API Analyzer and share your thoughts!
0
Aylin
Telerik team
answered on 14 Nov 2016, 02:09 PM
Hello,

I just wanted to notify you that we found some issues related to the partial improvement mentioned before. The fix is currently suspended and it is not included in today's LIB release.

Regards,
Aylin
Telerik by Progress
Do you need help with upgrading your WPF project? Try the Telerik API Analyzer and share your thoughts!
0
Aylin
Telerik team
answered on 23 Nov 2016, 01:13 PM
Hello,

Unfortunately, the fix caused a serious issue with formula value updating and we needed to rollback.

The best I can suggest is to follow the public item in order to be notified about any status changes. Sorry for the caused inconvenience.

Regards,
Aylin
Telerik by Progress
Telerik UI for WPF is ready for Visual Studio 2017 RC! Learn more.
Tags
Spreadsheet
Asked by
MikeWiese
Top achievements
Rank 1
Answers by
T. Tsonev
Telerik team
MikeWiese
Top achievements
Rank 1
Deyan
Telerik team
Nikolay Demirev
Telerik team
Mark
Top achievements
Rank 1
Aylin
Telerik team
Share this question
or