All,
I have some revised info regarding the SQLite V2 vs V3 speed comparisons.
First, a correction. The values for version 3.3.5 using memory and no
transaction were actually measured with 100K records not 1M records.
My original test code was made to match Ivan's original posting as
closely as possible. This included the index on the time column. This is
a particularly bad column to index since every record has exactly the
same value in this column. To check the impact of updating this index on
every insert, i repeated the tests without the index (by commenting out
the CREATE INDEX line).
The revised results are shown below.
SQLite IDX DB TX records inserts/sec
===============================================================
3.3.5 yes :memory: no 100K 2778
3.3.5 yes :memory: yes 1M 22727
3.3.5 yes file no 1K 10
3.3.5 yes file yes 1M 24390
3.3.5 no :memory: no 100K 5263
3.3.5 no :memory: yes 1M 33333
3.3.5 no file no 1K 11
3.3.5 no file yes 1M 35714
2.8.17 yes :memory: no 1M 62500
2.8.17 yes :memory: yes 1M 58824
2.8.17 yes file no 1K 13
2.8.17 yes file yes 1M 23256
2.8.17 no :memory: no 1M 83333
2.8.17 no :memory: yes 1M 76923
2.8.17 no file no 1K 13
2.8.17 no file yes 1M 40000
Dropping the index increased the speed of almost all cases, but the
difference between V3 and V2 remains about the same. The V3 memory
inserts without a transaction increased the most, by a factor of 1.9.
Most other cases increased by a factor of around 1.3 to 1.5. File
inserts without a transaction did not speed up at all for V2, and barely
for V3.
Dennis Cote
