You'll find a LARGE performance improvement if you wrap your inserts inside a BEGIN/COMMIT and only commit every 1000 or so inserts or such. BEGIN for all records INSERT count++ if ((count % 1000)==0) COMMIT;BEGIN // commit and begin again end COMMIT Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Martin Knafve Sent: Tue 7/6/2010 12:05 PM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] WAL - Performance/fsync Hi, I'm considering using SQLite as backend for my software. What's stopping me is the low insert-performance. If I disable fsync/FlushFileBuffers, performance is good but I can't do with the risk of database corruption. I don't know the details of the WAL-implementation in 3.7, but if fsync/FlushFileBuffers are only required when data is moved from the write-ahead log to the database file, I would expect performance to be a lot faster. Reading the documentation about WAL, I get the impression that I can accomplish this using the following pragmas: sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, 0, &db_err); sqlite3_exec(db, "PRAGMA synchronous=NORMAL", NULL, 0, &db_err); I picked a snapshot from git and tried it. I'm unable to see any real performance improvement by doing this though. Haven't looked very carefully and only run the tests a few times but I don't notice any difference larger than 10% or so. On my PC, INSERT's with fsync disabled is ~50 times faster than when fsync is used, so I would expect enabling WAL to give me at least a 500% speedup. Of course, my assumptions are very rough and I understand that I'm likely far off. Am I missing something here? What kind of performance improvments should I expect on INSERT statements not taking part in a transaction when using the WAL? Kind regards, Martin Knafve _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
