Github user LosD commented on the issue:
https://github.com/apache/metamodel/pull/194
Okay, made an even stupider test, this time doing multiple inserts, from
multiple threads (I think we had problems with that on Excel previously) :)
```java
import java.io.File;
import java.util.Collections;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import org.apache.metamodel.excel.ExcelDataContext;
import org.apache.metamodel.schema.Column;
import org.apache.metamodel.schema.Schema;
import org.apache.metamodel.schema.Table;
public class ExcelTest {
private final ExcelDataContext dataContext;
private ExcelTest(ExcelDataContext dataContext) {
this.dataContext = dataContext;
}
private void go() throws InterruptedException {
long startTime = System.currentTimeMillis();
ExecutorService executor = Executors.newFixedThreadPool(8);
List<Callable<Long>> workers = Collections.nCopies(16,
this::addStuff);
try {
final long combinedTime =
executor.invokeAll(workers).stream().mapToLong(future -> {
try {
return future.get();
} catch (InterruptedException | ExecutionException e) {
throw new IllegalStateException(e);
}
}).sum();
long endTime = System.currentTimeMillis();
System.out.println(
"Combined execution time: " + combinedTime + "ms, real
time: " + (endTime - startTime) + "ms");
} finally {
executor.shutdown();
}
}
private long addStuff() {
long startTime = System.currentTimeMillis();
Schema schema = dataContext.getDefaultSchema();
Table table = schema.getTable(0);
Column column1 = table.getColumn(0);
Column column2 = table.getColumn(1);
Column column3 = table.getColumn(2);
char[] chars = "abcdefghijklmnopqrstuvwxyz".toCharArray();
dataContext.executeUpdate(updateCallback -> {
for (int i = 0; i < 100; i++) {
updateCallback.insertInto(table).value(column1,
i).value(column2, i * 10)
.value(column3, chars[i % chars.length]).execute();
}
});
long endTime = System.currentTimeMillis();
final long time = endTime - startTime;
System.out.println("Thread '" + Thread.currentThread().getName() +
"' execution time: " + time + "ms");
return time;
}
public static void main(String[] args) throws InterruptedException {
ExcelTest excelTest = new ExcelTest(new ExcelDataContext(new
File("C:\\Users\\Dennis\\Documents\\empty.xlsx")));
excelTest.go();
}
}
```
The test runs fine, at least I saw no issues over 10-15 runs. It's obvious
that workers are blocked while others are writing, but that's fair, as long as
nothing goes wrong.
XLSX is really slow when inserting:
```
Thread 'pool-1-thread-7' execution time: 2203ms
Thread 'pool-1-thread-2' execution time: 3333ms
Thread 'pool-1-thread-6' execution time: 4424ms
Thread 'pool-1-thread-3' execution time: 5528ms
Thread 'pool-1-thread-8' execution time: 6629ms
Thread 'pool-1-thread-5' execution time: 7697ms
Thread 'pool-1-thread-1' execution time: 8776ms
Thread 'pool-1-thread-4' execution time: 9880ms
Thread 'pool-1-thread-1' execution time: 2246ms
Thread 'pool-1-thread-5' execution time: 4444ms
Thread 'pool-1-thread-8' execution time: 6621ms
Thread 'pool-1-thread-3' execution time: 8854ms
Thread 'pool-1-thread-6' execution time: 11074ms
Thread 'pool-1-thread-2' execution time: 13257ms
Thread 'pool-1-thread-7' execution time: 15490ms
Thread 'pool-1-thread-4' execution time: 8893ms
Combined execution time: 119349ms, real time: 18827ms
```
(The combined numbers gets quite inflated by the waiting)
XLS is quite a bit faster:
```
Thread 'pool-1-thread-7' execution time: 244ms
Thread 'pool-1-thread-2' execution time: 260ms
Thread 'pool-1-thread-5' execution time: 274ms
Thread 'pool-1-thread-8' execution time: 287ms
Thread 'pool-1-thread-3' execution time: 303ms
Thread 'pool-1-thread-1' execution time: 319ms
Thread 'pool-1-thread-1' execution time: 16ms
Thread 'pool-1-thread-4' execution time: 352ms
Thread 'pool-1-thread-6' execution time: 369ms
Thread 'pool-1-thread-4' execution time: 35ms
Thread 'pool-1-thread-1' execution time: 68ms
Thread 'pool-1-thread-3' execution time: 118ms
Thread 'pool-1-thread-8' execution time: 154ms
Thread 'pool-1-thread-5' execution time: 188ms
Thread 'pool-1-thread-2' execution time: 224ms
Thread 'pool-1-thread-7' execution time: 264ms
Combined execution time: 3475ms, real time: 566ms
```
XLSX on the master branch was maybe a little bit faster, but I didn't
really run it often enough to make sure:
```
Thread 'pool-1-thread-3' execution time: 1779ms
Thread 'pool-1-thread-1' execution time: 2903ms
Thread 'pool-1-thread-4' execution time: 4040ms
Thread 'pool-1-thread-2' execution time: 5155ms
Thread 'pool-1-thread-8' execution time: 6249ms
Thread 'pool-1-thread-5' execution time: 7354ms
Thread 'pool-1-thread-6' execution time: 8440ms
Thread 'pool-1-thread-7' execution time: 9552ms
Thread 'pool-1-thread-6' execution time: 2214ms
Thread 'pool-1-thread-5' execution time: 4377ms
Thread 'pool-1-thread-8' execution time: 6600ms
Thread 'pool-1-thread-2' execution time: 8795ms
Thread 'pool-1-thread-4' execution time: 11014ms
Thread 'pool-1-thread-1' execution time: 13242ms
Thread 'pool-1-thread-3' execution time: 15466ms
Thread 'pool-1-thread-7' execution time: 8781ms
Combined execution time: 115961ms, real time: 18391ms
```
XLS was very slow compared to the new branch, though:
```
Thread 'pool-1-thread-3' execution time: 1061ms
Thread 'pool-1-thread-4' execution time: 1896ms
Thread 'pool-1-thread-7' execution time: 2743ms
Thread 'pool-1-thread-1' execution time: 3582ms
Thread 'pool-1-thread-5' execution time: 4438ms
Thread 'pool-1-thread-8' execution time: 5276ms
Thread 'pool-1-thread-6' execution time: 6170ms
Thread 'pool-1-thread-2' execution time: 7016ms
Thread 'pool-1-thread-6' execution time: 1682ms
Thread 'pool-1-thread-8' execution time: 3432ms
Thread 'pool-1-thread-5' execution time: 5119ms
Thread 'pool-1-thread-1' execution time: 6836ms
Thread 'pool-1-thread-7' execution time: 8505ms
Thread 'pool-1-thread-4' execution time: 10180ms
Thread 'pool-1-thread-3' execution time: 11874ms
Thread 'pool-1-thread-2' execution time: 6791ms
Combined execution time: 86601ms, real time: 13865ms
```
To make sure, I checked the Excel documents, and everything seems correct
(here the blocking is also obvious, as values are progressing from low to high
values, with no intertwined values from other threads).
I think the conclusion must be that in general it works as it should, and
with quite a good boost on XLS speed, _maybe_ at the cost of a little bit of
writing speed on the XLXS side.
---
