InsertRange Multiple Calls Performance Issue

Aspose.Cells Product Family
1

When calling cells.InsertRange multiple times, the performance suffers greatly. I'm using Aspose.Cells.8.1.1.

The first InsertRange returns in about 50 milliseconds, which is what I would expect as that's about how long it takes in the actual excel application.

Then it blooms to 900 milliseconds and continues to creep up. The creep up could be expected because the excel sheet is getting bigger, more things to keep track of etc.

Is there anything that can be done to speed this up?

Iteration Loop took: 42.00
Iteration Loop took: 949.00
Iteration Loop took: 922.00
Iteration Loop took: 940.00
Iteration Loop took: 942.00
Iteration Loop took: 950.00
Iteration Loop took: 954.00
Iteration Loop took: 947.00
Iteration Loop took: 981.00
Iteration Loop took: 971.00
Press any key to continue . . .

2

Hi,


Well, you may try to use MemoryPreference options while loading the file and inserting data. Please see the document for your complete reference about the new feature that Aspose.Cells now provides:
http://www.aspose.com/docs/display/cellsnet/Optimizing+Memory+Usage+while+Working+with+Big+Files+having+Large+Datasets

I think you may try to update your code segment (at the start) to:
e.g
Sample code:

//Specify the LoadOptions
LoadOptions opt = new LoadOptions();
//Set the memory preferences
opt.MemorySetting = MemorySetting.MemoryPreference;


var stream = Assembly.GetCallingAssembly().GetManifestResourceStream(“ConsoleApplication1.SummaryReport_Template.xlsx”);
var workbook = new Workbook(stream, opt);
…


It would work fine and quickly.

Thank you.

3

Thanks Amjad.

The subsequent runtimes are down from 5 seconds to one second now, so it is much more usable. I'm still concerned that the first InsertCells runs really quickly, then has a 2000% increase in run time for additional calls.

Thanks for your reply.

4

Hi,


Good to know that it suits your needs now. Could you also provide your stats/results for the calls after updating your code segment for memory preference, we might check if we could enhance it more.

Thank you.
5

This is with version 8.1.1.0

45,000 Rows without memory preference set.

Iteration for cell took: 71.00
Iteration for cell took: 4,797.00
Iteration for cell took: 4,805.00
Iteration for cell took: 5,898.00
Iteration for cell took: 5,210.00
Iteration for cell took: 5,325.00
Iteration for cell took: 5,018.00
Iteration for cell took: 5,373.00
Iteration for cell took: 5,157.00
Iteration for cell took: 4,828.00

and stays between 4,500 and 6,000

45,000 rows with memory preference set.

Iteration for cell took: 75.00
Iteration for cell took: 166.00
Iteration for cell took: 211.00
Iteration for cell took: 256.00
Iteration for cell took: 337.00
Iteration for cell took: 431.00
Iteration for cell took: 495.00
Iteration for cell took: 564.00
Iteration for cell took: 566.00
Iteration for cell took: 637.00
Iteration for cell took: 716.00
Iteration for cell took: 778.00
Iteration for cell took: 896.00
Iteration for cell took: 935.00
Iteration for cell took: 995.00
Iteration for cell took: 1,082.00
Iteration for cell took: 1,186.00
Iteration for cell took: 1,251.00
Iteration for cell took: 1,296.00
Iteration for cell took: 1,462.00
Iteration for cell took: 1,470.00
Iteration for cell took: 1,444.00
Iteration for cell took: 1,546.00
Iteration for cell took: 1,741.00
Iteration for cell took: 1,791.00
Iteration for cell took: 1,661.00
Iteration for cell took: 1,710.00
Iteration for cell took: 1,804.00Iteration for cell took: 75.00
Iteration for cell took: 166.00
Iteration for cell took: 211.00
Iteration for cell took: 256.00
Iteration for cell took: 337.00
Iteration for cell took: 431.00
Iteration for cell took: 495.00
Iteration for cell took: 564.00
Iteration for cell took: 566.00
Iteration for cell took: 637.00
Iteration for cell took: 716.00
Iteration for cell took: 778.00
Iteration for cell took: 896.00
Iteration for cell took: 935.00
Iteration for cell took: 995.00
Iteration for cell took: 1,082.00
Iteration for cell took: 1,186.00
Iteration for cell took: 1,251.00
Iteration for cell took: 1,296.00
Iteration for cell took: 1,462.00
Iteration for cell took: 1,470.00
Iteration for cell took: 1,444.00
Iteration for cell took: 1,546.00
Iteration for cell took: 1,741.00
Iteration for cell took: 1,791.00
Iteration for cell took: 1,661.00
Iteration for cell took: 1,710.00
Iteration for cell took: 1,804.00

I also noticed that the DeleteRange calls were taking a very long time, so during my application run, I need to keep toggling the memory mode: Preference during Inserts and Normal during Deletes.

I pulled down 8.1.2 and with the toggling the numbers have improved even more. (With the toggle)

Iteration for cell took: 69.00
Iteration for cell took: 89.00
Iteration for cell took: 85.00
Iteration for cell took: 95.00
Iteration for cell took: 99.00
Iteration for cell took: 106.00
Iteration for cell took: 112.00
Iteration for cell took: 127.00
Iteration for cell took: 128.00
Iteration for cell took: 139.00
Iteration for cell took: 141.00
Iteration for cell took: 158.00
Iteration for cell took: 160.00
Iteration for cell took: 172.00
Iteration for cell took: 218.00
Iteration for cell took: 189.00
Iteration for cell took: 229.00
Iteration for cell took: 212.00
Iteration for cell took: 215.00
Iteration for cell took: 218.00
Iteration for cell took: 220.00
Iteration for cell took: 229.00
Iteration for cell took: 243.00
Iteration for cell took: 246.00
Iteration for cell took: 259.00
Iteration for cell took: 341.00Iteration for cell took: 69.00
Iteration for cell took: 89.00
Iteration for cell took: 85.00
Iteration for cell took: 95.00
Iteration for cell took: 99.00
Iteration for cell took: 106.00
Iteration for cell took: 112.00
Iteration for cell took: 127.00
Iteration for cell took: 128.00
Iteration for cell took: 139.00
Iteration for cell took: 141.00
Iteration for cell took: 158.00
Iteration for cell took: 160.00
Iteration for cell took: 172.00
Iteration for cell took: 218.00
Iteration for cell took: 189.00
Iteration for cell took: 229.00
Iteration for cell took: 212.00
Iteration for cell took: 215.00
Iteration for cell took: 218.00
Iteration for cell took: 220.00
Iteration for cell took: 229.00
Iteration for cell took: 243.00
Iteration for cell took: 246.00
Iteration for cell took: 259.00
Iteration for cell took: 341.00

Also with the new 8.1.2 version, the toggling actual ran slower. I'm running entirely in Preference mode and the performance has improved by 90%.

Thanks for you and your teams efforts.

6

Hi,


Thanks for your stats and results.

Good to know that you got better performance results in our latest version of Aspose.Cells for .NET v8.1.2. Please keep using/ testing Aspose.Cells and in case, you find any issue, feel free to write back here, we will check it soon.

Thank you.