Hi All,
I finally got a chance to upgrade SQLite for our product from ancient 3.7.16.2.
Initial tests on Linux were very promising - ranging from 33% to even 300% (for
one degenerated case) speed improvement. So far so good. Problems begun when I
have tested it on Windows. Depending on test case the new version is up to
0.28x slower! In the course of investigation I have managed to improve the
performance by adding few missing indexes but this has influenced both old and
new versions so the relative performance with 3.8.11.1 is still like one third
worse.
I have tried doing it step-by-step: 3.7.16.2 -> 3.7.17.0 -> 3.8.0.0 and the
results are quite surprising:
3.7.16.2 -> 3.7.17.0 : ~16% faster
3.7.17.0 -> 3.8.0.0 : ~26% slower
3.7.16.2 -> 3.8.0.0 : ~15% slower
3.7.16.2 -> 3.8.11.1 : ~28% slower
We use SQLite as backend ("persistent storage") to an implementation of a state
machine. The queries are rather simple and each of them is too fast to measure
(SQLite performance timer only has resolution of milliseconds) in the profiler
it looks like the actual filesystem functions were taking more time in new
versions but that tells me nothing really.
Is there something I can tweak?
The page size is set to 4K (NTFS file system), synchonous is OFF,
journal_mode=truncated;
With 10K objects all versions seem to perform equally: test takes ~45s. For
100K objects it's already [m]:[s]
3.7.16.2 : ~10:55
3.7.17.0 : ~09:30
3.8.0.0 : ~12:46
3.8.11.1 : ~15:08
I'm out of ideas here. Can someone help me with further investigation?
--
Gruesse,
Jakub
