Range#setValue/setValues is very slow for large sheet (100K rows)

[thestrox]

Version
SODS 1.8.2

Problem

• Range#setValue/setValues is very slow.
• Reproduce using:

  static void main() throws IOException {
    int rows = 100000;
    int columns = 1;
    String[][] data = new String[rows][columns];
    for (int i = 0; i < rows; i++) {
      for (int j = 0; j < columns; j++) {
        data[i][j] = i + ";" + j;
      }
    }
    SpreadSheet out = new SpreadSheet();
    Sheet sheet = new Sheet("Sheet1", rows, columns);
    out.appendSheet(sheet);
    long startNano = System.nanoTime();
    sheet.getDataRange().setValues(data);
    double endNano = (double) (System.nanoTime() - startNano) / 1_000_000_000;
    System.out.println("Time: " + endNano);
    out.save(Path.of("testExport.ods").toFile());
  }

• In my test: the Range.setValues() takes around 22 sec with Sheet#getIndexDelete being the hotspot with 94.6% time invested on it, and only 3.4% for saving the file.

Expected

• I would expect this to be significantly faster than current At the moment, the performance becomes exponentially worse as the row count increases (takes around 125 sec for 200K rows) making large file processing not possible.
• I thought using Range.setValues could give performance boost compare to Range.setValue, but there is no significant difference.

[thestrox]

Hi @miachm , thanks for the quick fix. The bulk api: Range#setValues is now fast, so this issue is resolved on that front.

However, I would like to point out that iterating ourselves and calling Range#setValue/setStyle per-cell is still not performant. So for large exports we need to use the bulk APIs. One could argue that's exactly what the bulk APIs are for, but I want to flag a couple of trade-offs that come with that path:

1. Range#setStyles requires a fully-populated Style[][] — null is not allowed, so we have to create a Style instance for every cell, even ones that just want the default style. And since the library clones every passed instance into the cell, caching a single shared default Style on our side doesn't help. This can explode the memory unnecessarily for large data-set.
2. The need to call Range#setValues, Range#setStyles, etc. separately repeats the redundant iteration.

One direction that would address both:
A cell iterator API that gives callers per-cell control; a public version of iterateRangeEachLogicalCell. Callers get per-cell control without the per-cell lookup cost, and default-styled cells can just be skipped. Since the Cell is not exposed, we would need a wrapper around Cell.

If the iterator route isn't appealing, the per-cell issue seems to come from Sheet#getIndexDelete walking the RLE runs from the start on every external call. Probably, a sheet-level last-access cursor cache is needed to resolve it.

Thanks again for the quick turnaround!

[miachm]

There is an alternative method: range.setStyle(). Where you apply the same style to the entire range.

Anyway I did two changes:

• You can now use null values for setStyle() methods. It defaults to Style.default
• I created a prototype where you can iterate through a Range and execute a lambda function with your custom operations. Which could be reading or writting.

It's still a prototype and need more tests. But I think that's the idea we should use.

[thestrox]

Thanks for the changes. I think the api for the new iterator is good.

I see that you did not make a release. Anything preventing that?

[miachm]

Haven't properly tested. I would like to add more automated tests.

Also since this would be an Y release. It would be appropiate to add at least a couple of extra features. I don't like over-use Y releases so it would be good to add something from the backlog. When to release? Probably the next 1-2 weeks.

You can fork to your own maven repo (or create your own jar) meanwhile you wait for the release.

You can also help me with a pull request or sponsoring the project.