Add Worksheet.values() batch function to the API

[akbertram]

For use-cases where rows are written sequentially, this avoids the
overhead of resizing the cells array each time a new cell is added,
as well as repeating all of the bounds checks for each cell.

[rzymek]

I did a micro benchmark comparison of:

private static final int NB_ROWS = 10000;
private static Object[] row = IntStream.range(0, 200).boxed().toArray();

@Benchmark
public int oneByOne() throws IOException {
  CountingOutputStream count = new CountingOutputStream(new NullOutputStream());
  Workbook wb = new Workbook(count, "Perf", "1.0");
  Worksheet ws = wb.newWorksheet("Sheet 1");
  for (int r = 0; r < NB_ROWS; ++r) {
    for (int c = 0; c < row.length; c++) {
      ws.value(r, c, row[c]);
    }
    if (r % 1000 == 0) {
      ws.flush();
    }
  }
  wb.finish();
  return count.getCount();
}

against

@Benchmark
public int wholeRowReverse() throws IOException {
  CountingOutputStream count = new CountingOutputStream(new NullOutputStream());
  Workbook wb = new Workbook(count, "Perf", "1.0");
  Worksheet ws = wb.newWorksheet("Sheet 1");
  for (int r = 0; r < NB_ROWS; ++r) {
    ws.values(r, row);
    if (r % 1000 == 0) {
      ws.flush();
    }
  }
  wb.finish();
  return count.getCount();
}

and the results unfortunately do not show any difference in performance:

WriterMultipleRows.oneByOne           ss    5  0.987 ± 0.039   s/op
WriterMultipleRows.wholeRowReverse    ss    5  0.988 ± 0.121   s/op

[akbertram]

Fascinating! Thanks for benchmarking. Another reminder that the JVM is really good at lifting things out of loops. I am workkng on optimizing our export routine, but I will go back to the drawing board and benchmark myself before submitting another PR!

…

On Sun, Dec 20, 2020, 18:03 Krzysztof Rzymkowski ***@***.***> wrote: I did a micro benchmark comparison of: private static final int NB_ROWS = 10000; private static Object[] row = IntStream.range(0, 200).boxed().toArray(); @benchmark public int oneByOne() throws IOException { CountingOutputStream count = new CountingOutputStream(new NullOutputStream()); Workbook wb = new Workbook(count, "Perf", "1.0"); Worksheet ws = wb.newWorksheet("Sheet 1"); for (int r = 0; r < NB_ROWS; ++r) { for (int c = 0; c < row.length; c++) { ws.value(r, c, row[c]); } if (r % 1000 == 0) { ws.flush(); } } wb.finish(); return count.getCount(); } against @benchmark public int wholeRowReverse() throws IOException { CountingOutputStream count = new CountingOutputStream(new NullOutputStream()); Workbook wb = new Workbook(count, "Perf", "1.0"); Worksheet ws = wb.newWorksheet("Sheet 1"); for (int r = 0; r < NB_ROWS; ++r) { ws.values(r, row); if (r % 1000 == 0) { ws.flush(); } } wb.finish(); return count.getCount(); } and the results unfortunately do not show any difference in performance: WriterMultipleRows.oneByOne ss 5 0.987 ± 0.039 s/op WriterMultipleRows.wholeRowReverse ss 5 0.988 ± 0.121 s/op — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#135 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADO5QZZLLM6J27VD7UMYNDSVYU6TANCNFSM4VCMBJ5Q> .

[rzymek]

Great! Keeping my fingers crossed that you'll find some place that can be optimized!
Microbenchmark infrastructure for fastexcel is setup in https://github.com/dhatim/fastexcel/tree/master/e2e subproject. Just create a class that extends BenchmarkLauncher, mark methods with JMH's @Benchmark annotation and run the class as a JUnit test. Feel free to drop be a line at rzymek@gmail.com if you need any help.
As for this PR, I'm gonna close it,as the new API method does not bring noticeable performance benefit.