Spreadsheet slows exponentially with repeated calculations

19 posts, 1 answers
  1. MikeWiese
    MikeWiese avatar
    41 posts
    Member since:
    Apr 2007

    Posted 29 Aug Link to this post

    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

  2. T. Tsonev
    Admin
    T. Tsonev avatar
    2770 posts

    Posted 31 Aug Link to this post

    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.
     
  3. Kendo UI is VS 2017 Ready
  4. MikeWiese
    MikeWiese avatar
    41 posts
    Member since:
    Apr 2007

    Posted 04 Sep in reply to T. Tsonev Link to this post

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

  5. Deyan
    Admin
    Deyan avatar
    135 posts

    Posted 07 Sep Link to this post

    Hello Mike Wiese,

    We have tried to reproduce the performance issue with exponentially growing time consumption. However, in our test scenarios 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.
     
  6. MikeWiese
    MikeWiese avatar
    41 posts
    Member since:
    Apr 2007

    Posted 09 Sep in reply to Deyan Link to this post

    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

  7. Answer
    Nikolay Demirev
    Admin
    Nikolay Demirev avatar
    103 posts

    Posted 10 Sep Link to this post

    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.
  8. Mark
    Mark avatar
    17 posts
    Member since:
    Dec 2010

    Posted 28 Oct in reply to Nikolay Demirev Link to this post

    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.

     

  9. MikeWiese
    MikeWiese avatar
    41 posts
    Member since:
    Apr 2007

    Posted 31 Oct Link to this post

    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?

  10. Aylin
    Admin
    Aylin avatar
    75 posts

    Posted 01 Nov Link to this post

    Hello,

    I could confirm that we see the same results at 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!
  11. Mark
    Mark avatar
    17 posts
    Member since:
    Dec 2010

    Posted 01 Nov in reply to Aylin Link to this post

    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

  12. Aylin
    Admin
    Aylin avatar
    75 posts

    Posted 03 Nov Link to this post

    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 workaround 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 additional answer.

    Regards,
    Aylin
    Telerik by Progress
    Do you need help with upgrading your WPF project? Try the Telerik API Analyzer and share your thoughts!
  13. Mark
    Mark avatar
    17 posts
    Member since:
    Dec 2010

    Posted 03 Nov in reply to Aylin Link to this post

    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

  14. MikeWiese
    MikeWiese avatar
    41 posts
    Member since:
    Apr 2007

    Posted 03 Nov Link to this post

    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.

  15. Mark
    Mark avatar
    17 posts
    Member since:
    Dec 2010

    Posted 03 Nov in reply to MikeWiese Link to this post

    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

  16. MikeWiese
    MikeWiese avatar
    41 posts
    Member since:
    Apr 2007

    Posted 03 Nov Link to this post

    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.
  17. Mark
    Mark avatar
    17 posts
    Member since:
    Dec 2010

    Posted 04 Nov in reply to MikeWiese Link to this post

    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

  18. Aylin
    Admin
    Aylin avatar
    75 posts

    Posted 07 Nov Link to this post

    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!
  19. Aylin
    Admin
    Aylin avatar
    75 posts

    Posted 14 Nov Link to this post

    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!
  20. Aylin
    Admin
    Aylin avatar
    75 posts

    Posted 23 Nov Link to this post

    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.
Back to Top
Kendo UI is VS 2017 Ready