GetPivotData performance issue

Aspose.Cells Product Family
1

Hello

I have a table with 17000 records and one column is using GETPIVOTDATA function on a PivotTable.

On my computer this is taking 30 seconds to complete with Aspose.Cells whereas in Excel it takes a second or two.

Is there anything that can be done to improve this performance issue?

Here is the sample code: (xlsx file attached)
var workbook = new Workbook(“PivotTableTest.xlsx”);
Stopwatch sw = new Stopwatch();
sw.Start();
Console.WriteLine(“Calculating”);
workbook.CalculateFormula();
Console.WriteLine(“Time taken: {0}”, sw.ElapsedMilliseconds);

Many thanks
Francisco Taborda

2

Hi,


Thanks for providing us template file and sample code.

After an initial test, I am able to reproduce the issue as you mentioned by using your sample code with your template file. I found performance issue with GetPivotData method, it takes more time to calculate the formulas involving GetPivotData function/formula as you pointed out.
e.g
Sample code:

var workbook = new Workbook(“PivotTableTest.xlsx”);
Stopwatch sw = new Stopwatch();
sw.Start();
Console.WriteLine(“Calculating”);
workbook.CalculateFormula();
Console.WriteLine(“Time taken: {0}”, sw.ElapsedMilliseconds);

I have logged a ticket with an id “CELLSNET-44847” for your issue. We will check if we could enhance the formula calculation engine further to calculate the formulas efficiently.

Once we have an update on it, we will let you know here.

Thank you.
3
Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v16.10.6.0 and let us know your feedback.
4

Hi

I’ve tried version 16.10.6.0. It seems to be faster, but on the example file I’ve sent previously it still takes 15seconds.

Is there any room for more optimization?

Thank you
Best regards
Francisco Taborda

5

Hi Francisco,


Thank you for sharing your feedback.

I have requested the concerned member of the product team to review the scenario and share the feedback. As soon as we get any news in this regard, we will post here for your kind reference.

Please note, I have tested the scenario on my side as well, and I have noticed that the process took 12 seconds. The small difference in time (15 sec vs 12 sec) could be due to the difference in computing power of machines.
6

Hi again,


This is to update you that the ticket logged earlier as CELLSNET-44847 has been marked resolved. As soon as the next build of the API is available for public use, we will notify you here.
7

Hi

Were there any more improvements?

Best Regards
Francisco

8

Hi Francisco,


I regret to inform you that we had to reopen the aforementioned ticket as a few test cases didn’t pass. The ticket is in-progress again, and we hope to provide a fix soon. Please note, the ticket was previously reopened due to the performance considerations therefore we are trying our best to lower the execution time as compared to the previous fix. I am in touch with the concerned member of the product team in this regard, and I will keep you posted with updates.

Please accept our apologies for the inconvenience.
9
Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v16.10.10.0 and let us know your feedback.

We have tried our best to improve the performance, so it is the final fix version.

10

Hello

Were there any improvements?

This is still taking 30 second, at least on the second run.

On the example file I’ve sent previously:
Calculate: 13 seconds
Refresh Pivot Table: 500ms
Calculate again: 30 seconds

Many thanks
Best Regards
Francisco

11

Hi Francisco,


Thank you for your feedback.

Please note, the process that took 13 seconds on my machine with previous release is now taking 11 seconds so there is some improvement. Regarding the second run, could you please provide an executable sample application/code to demonstrate the problem.
12

Here is the sample code:

Stopwatch sw = new Stopwatch();
var workbook = new Workbook(“PivotTableTest.xlsx”);

sw.Restart();
Console.WriteLine(“Calculating”);
workbook.CalculateFormula();
Console.WriteLine("Time taken: " + sw.ElapsedMilliseconds);

sw.Restart();
Console.WriteLine(“Refresh Pivot”);
var ws = workbook.Worksheets[0];
foreach (var pivotTable in ws.PivotTables.Cast())
{
pivotTable.RefreshData();
pivotTable.CalculateData();
}
Console.WriteLine("Time taken: " + sw.ElapsedMilliseconds);

sw.Restart();
Console.WriteLine(“Calculating again”);
workbook.CalculateFormula();
Console.WriteLine("Time taken: " + sw.ElapsedMilliseconds);

Best Regards
Francisco Taborda

13

Hi again,


Thank you for sharing the code snippet. I have checked the case on my side and I am able to notice the said problem, that is; calculating the formulas for second time is taking more time than the first calculation. I have logged my observations to the aforementioned ticket for the concerned member of the product team. Allow me some time to discuss this matter and get back with updates in this regard.
14

Hi Francisco,


This is to update you that I have raised your recently shared scenario as a separate ticket with Id CELLSNET-44899. The said ticket is currently in analysis phase and we will share more details as soon as we have completed the preliminary analysis. Thank you for your patience with us.
15

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
16
Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v16.11.1.0 and let us know your feedback.

17

Hello

The second run seems to be the same time as the first (~15sec).

15 seconds is still a lot compared to what excel does (~1sec), not sure if anything else can be done…

Thank you
Francisco Taborda

18

Hi again,


Please ignore the previous comments (removed from the thread) as I didn’t let the process to complete. I have checked the second calculation is taking almost same time as of the first calculation. I have reattached the console.png showing the complete results.

I am sorry for the inconvenience.
19

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

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