We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

PivotTable.RefreshData takes a lot of time

Aspose.Cells Product Family
#1

Hi


We have encountered a performance problem with the PivotTable when refreshing data on large Excel sheets.

The behaviour is reproduced in the attached sample application.

To run the application
- unzip the attached file
- open the AsposeRefreshPivotTest.sln
- Open the LicenseHandler.cs file and insert contents of a valid Aspose licens in the variable LICENSEFILE - look for “INSERT LICENSE HERE”
- and build in Visual Studio.

Start the application and push the button.

It takes almost (on my machine) 70 seconds to refresh the data.

A performance profiler trace from Ants reveals almost 1.700.000.000 calls to pivotItem.get_Value - and the double 3.600.000.000 calls to ArrayList.get_Item - being responsible for most of the time.

It might not be exactly where it takes time, as profiler traces may introduce noticable overhead in low-level methods though. In a trace with smaller overhead the get_Value was only responsible for 15% of the time - and an obfuscated parent method used the remaining time.

Will you please examine, and verify you can reproduce it using the sample - and indicate whether or not this is something you expect to be able to fix?

I’ve tested using december 2016 release of Aspose.Cells.

Best regards
/Anders


#2

Hi Anders,


Thank you for the sample project.

I have evaluated the presented scenario while using the latest version of Aspose.Cells for .NET 16.12.5, and I am able to notice that PivotTable.RefreshData call is taking almost 70 seconds on my machine. I have raised this incident with the product team for further review however, I cannot promise if we can improve the time unless we first analyze the scenario thoroughly on our side. The tracking code for the aforementioned incident is CELLSNET-45023. Please spare us little time for analysis. In the meanwhile, we will keep you posted with updates in this regard.
#3
Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue now. We will soon provide the fix after performing QA and including other enhancements and fixes.
#4
Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix

Aspose.Cells for .NET v16.12.8 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v16.12.8 (.NET 4.0) compiled in .NET Framework 4.0.

and let us know your feedback.
#5

The issues you have found earlier (filed as CELLSNET-45023) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
#6

Hi


Just to revert, and sorry for the delay, had a few other tasks before I got to test it.

The fix seems to do the trick.

I still have some scenarios where the RefreshData takes a lot of time - but can’t quite reproduce it. But maybe something about if the sheet is entirely new, created by Aspose, and filled with data, create new Pivot and then refresh the data.

But then again, if I take the Excel in my sample project and reverts the sort in the Pivot Table - Excel uses a lot of time as well.

Sorting a lot of rows is just not that easy.

But - the specific issue in the sample project is solved - and if the other part keeps bugging me I will create a new issue.

Thanks you for the quick feedback, support, and fix. Great work.

Best regards
/Anders
#7

Hi,


Thanks for your feedback and using Aspose.Cells.

Please feel free to create new threads for your every new issue. Even the issue is related to previous one, creating a new thread is good. Because your tickets are linked with threads. And when there are multiple issues or tickets in a single thread, it sometimes create problems for us and for users as well. So, there should be one thread for one issue if possible. It is also good to know that your previous issue seems to be resolved now.