Removing a range name is very slow

In this case we have a template row that we copy down that has two named ranges in it. We fill in the ranges, then remove the names. The next template row is then copied in and the process repeats up to 50,000 times.

Removing the names gets exponentially worse. This appears to be because every cell is checked to see if there is a reference to the name. Looks to be an allocation for every cell as it goes as well.
The entire process can take up to half an hour.

I have attached a profile of all cpu time being spent in the remove.

We know beforehand that the names are not referenced is there any other method than the following to remove a name that wont call the IsReferenced property?

template.Worksheets.Names.Remove(name);

Hi,


Thanks for providing us some details with screenshot.

Please try our latest version/fix: Aspose.Cells for .NET v16.11.8 if it makes any difference.

If you still find the performance issue with v16.11.8, well, we need your sample application to reproduce the issue on our end. We appreciate if you could create a simple demo console application (runnable), zip it and post us here to show the issue, we will check it soon. Also attach your template files if you have any.

Thank you.
Thanks Amjad, the provided build didn't change the times.
A simplified repro is below.

static void Main(string[] args)
{
//const int Iterations = 10000; // slow but ok
const int Iterations = 50000; // v. slow

var template = new Aspose.Cells.Workbook();
var templateWorksheet = template.Worksheets[0];
var range = templateWorksheet.Cells.CreateRange("A1", "A1");
range.Name = "One";
range = templateWorksheet.Cells.CreateRange("B1", "B1");
range.Name = "Two";
range = templateWorksheet.Cells.CreateRange("A1", "B1");
range.Name = "Insert";

var work = new Aspose.Cells.Workbook();
range = work.Worksheets[0].Cells.CreateRange("A1", "B1");
range.Name = "Insert";

for (int i = 0; i < Iterations; i++)
{
var insertRange = work.Worksheets.GetRangeByName("Insert");
int insertIndex = insertRange.FirstRow + insertRange.RowCount;
insertRange.Worksheet.Cells.InsertRows(insertIndex, 1, true);
insertRange.Worksheet.Cells.CopyRows(templateWorksheet.Cells, 0, insertIndex, 1);

// Copy ranges
var ranges = templateWorksheet.Workbook.Worksheets.GetNamedRanges();
foreach (var sourceRange in ranges)
{
var cloneRange = insertRange.Worksheet.Cells.CreateRange(insertIndex + sourceRange.FirstRow, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount);
cloneRange.Name = sourceRange.Name;
}

// Set a value. This must set a dirty flag as without it the name removes are fast
work.Worksheets.GetRangeByName("One")[0, 0].PutValue("123", true);

work.Worksheets.Names.Remove("One");
work.Worksheets.Names.Remove("Two");
}
}

Hi again,


I have performed tests using your provided snippet and latest version of Aspose.Cells for .NET 16.11.8, and I have noticed that the process (iteration count set to 50,000) took almost 5 minutes and 6 seconds to complete. You have mentioned in your original post that the process is taking 30 minutes to complete which was not reproduced on our side. If you have already tried latest version of the Aspose.Cells for .NET 16.11.8 on your side, could you please also write the API version to the console, just to make sure that latest version is being referenced in your test project. Please use the CellsHelper.GetVersion method to fetch the Aspose.Cells’ API version.

C#

Stopwatch watch = new Stopwatch();
watch.Start();
//const int Iterations = 10000; // slow but ok
const int Iterations = 50000; // v. slow

var template = new Aspose.Cells.Workbook();
var templateWorksheet = template.Worksheets[0];
var range = templateWorksheet.Cells.CreateRange(“A1”, “A1”);
range.Name = “One”;
range = templateWorksheet.Cells.CreateRange(“B1”, “B1”);
range.Name = “Two”;
range = templateWorksheet.Cells.CreateRange(“A1”, “B1”);
range.Name = “Insert”;

var work = new Aspose.Cells.Workbook();
range = work.Worksheets[0].Cells.CreateRange(“A1”, “B1”);
range.Name = “Insert”;

for (int i = 0; i < Iterations; i++)
{
var insertRange = work.Worksheets.GetRangeByName(“Insert”);
int insertIndex = insertRange.FirstRow + insertRange.RowCount;
insertRange.Worksheet.Cells.InsertRows(insertIndex, 1, true);
insertRange.Worksheet.Cells.CopyRows(templateWorksheet.Cells, 0, insertIndex, 1);

// Copy ranges
var ranges = templateWorksheet.Workbook.Worksheets.GetNamedRanges();
foreach (var sourceRange in ranges)
{
var cloneRange = insertRange.Worksheet.Cells.CreateRange(insertIndex + sourceRange.FirstRow, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount);
cloneRange.Name = sourceRange.Name;
}
// Set a value. This must set a dirty flag as without it the name removes are fast
work.Worksheets.GetRangeByName(“One”)[0, 0].PutValue(“123”, true);
work.Worksheets.Names.Remove(“One”);
work.Worksheets.Names.Remove(“Two”);
}
watch.Stop();
Console.WriteLine(watch.Elapsed);
Console.ReadKey();

Hi there,


This is to update you that I have discussed this matter with the concerned member of the product team and have raised an investigative ticket to analyze the case for performance considerations. Please note, the said problem seems to be related to NamedCollection.Remove method because Aspose.Cells APIs have to check all cells to find if there is any cell’s formula refers to the name being removed therefore if there are more cells, the process works more slowly.

The ticket Id for your future reference is CELLSNET-44967. You have to spare us little time to properly analyze the case, and revert back with updates in this regard.

Hi again,


Please try the latest version of Aspose.Cells for .NET 16.11.9 (attached) against the problem logged earlier as CELLSNET-44967. Please note, the following code takes almost 1 second for the complete process.

C#

Stopwatch watch = new Stopwatch();
watch.Start();
//const int Iterations = 10000; // slow but ok
const int Iterations = 50000; // v. slow
var template = new Aspose.Cells.Workbook();
var templateWorksheet = template.Worksheets[0];
var range = templateWorksheet.Cells.CreateRange(“A1”, “A1”);
range.Name = “One”;
range = templateWorksheet.Cells.CreateRange(“B1”, “B1”);
range.Name = “Two”;
range = templateWorksheet.Cells.CreateRange(“A1”, “B1”);
range.Name = “Insert”;

var work = new Aspose.Cells.Workbook();
var insertRang = work.Worksheets[0].Cells.CreateRange(“A1”, “B1”);
insertRang.Name = “Insert”;
var insertRange = work.Worksheets.GetRangeByName(“Insert”);
int insertIndex = insertRange.FirstRow + insertRange.RowCount;
insertRange.Worksheet.Cells.InsertRows(insertIndex, 1 + Iterations, true);
// Copy ranges
var ranges = templateWorksheet.Workbook.Worksheets.GetNamedRanges();
for (int i = 0; i < Iterations; i++, insertIndex++)
{
insertRange.Worksheet.Cells.CopyRows(templateWorksheet.Cells, 0, insertIndex, 1);
foreach (var sourceRange in ranges)
{
var cloneRange = insertRange.Worksheet.Cells.CreateRange(insertIndex + sourceRange.FirstRow, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount);
cloneRange.Name = sourceRange.Name;
}
// Set a value. This must set a dirty flag as without it the name removes are fast
work.Worksheets.GetRangeByName(“One”)[0, 0].PutValue(“123”, true);
work.Worksheets.Names.Remove(“One”);
work.Worksheets.Names.Remove(“Two”);
}
watch.Stop();
Console.WriteLine(watch.Elapsed);
The test code has been modified in such a way that it no longer adds new rows. It is just copying to the same location.

However with the original code the time goes from 4:52 minutes to 45 seconds under 16.11.9. That is a pretty good improvement, thank you! Most of the time now is in InsertRows which sounds appropriate.

For the record the 30 minutes was just a more complex setup. About 8 named ranges and cells.

Hi again,


Thank you for your feedback.

It is good to know that you are seeing performance improvements with latest release in your original scenario. Please feel free to contact us back in case you need our further assistance with Aspose APIs.

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


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