Hello, I made a small benchmark to test sqlite2 vs. sqlite3, since the benchmarks on the webpage were quite old. Unfortunately, the performance of sqlite3 was not as good as I hoped.
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100)); INSERT INTO t1 VALUES(1,88774,'eighty-eight thousand seven hundred seventy-four'); INSERT INTO t1 VALUES(2,53915,'fifty-three thousand nine hundred fiveteen'); .... 97 lines omitted INSERT INTO t1 VALUES(100,26908,'twenty-six thousand nine hundred eight'); 100 transactions, Windows XP SP2 on a 1.5Ghz centrino laptop: Sqlite 2.8.16: 0m10.018s 0m11.759s 0m10.849s Sqlite 3.2.1: 0m16.090s 0m15.940s 0m14.871s Sqlite 2.8.16 (nosync): 0m0.460s 0m0.453s 0m0.458s Sqlite 3.2.1 (nosync): 0m0.457s 0m0.474s 0m0.500s Sqlite3 is around 40-60% slower than sqlite2. Sqlite2 is doing less than 10 transactions per second, while sqlite3 does around 6-7. The nosync values were made using PRAGMA synchronous = OFF; 100 transactions, 3GHz P4, Windows XP SP2 with 7200 rpm harddrive: Sqlite 2.8.16: 0m4.761s 0m5.633s 0m5.431s Sqlite 3.2.1: 0m7.251s 0m7.251s 0m7.130s Sqlite 2.8.16 (nosync): 0m0.211s 0m0.204s 0m0.205s Sqlite 3.2.1 (nosync): 0m0.189s 0m0.195s 0m0.186s These transactions are much faster due to the faster harddrive, but the performance of sqlite2 still outperforms sqlite3. I also decided to test this on Linux, and the result very much surprised me. Unfortunately I can't test with sqlite2. 100 transactions, Linux 2.4.22 2.4Ghz P4: Sqlite 3.2.1: 0m0.373s 0m0.368s 0m0.368s Sqlite 3.2.1 (nosync): 0m0.037s 0m0.037s 0m0.039s What is this? Sqlite on Linux is 15-30 times faster than windows?! 10000 INSERTS, single big transaction, Windows Laptop: Sqlite 2.8.16: 0m0.983s Sqlite 3.2.1: 0m0.916s Sqlite3 is back on track here. In this case the performance is not disk bound. 10000 INSERTS, single big transaction, Windows P4: Sqlite 2.8.16: 0m0.474s Sqlite 3.2.1: 0m0.466s 10000 INSERTS, single big transaction, Linux Sqlite 3.2.1: 0m0.530s Questions: 1) Why is sqlite2 faster than sqlite3 per transaction? 2) Can anyone explain why sqlite performs so badly on Windows compared to Linux? /Ludvig
