Simon Slavin wrote: > Just wanted to check your phrasing. You process a while file into INSERT > commands, then put > BEGIN before the first one, and END after the last one, thus making the whole > file a single > transaction ? If so, then should make SQLite do things about as quickly as > it can. Though I think > a huge transaction might actually slow things down if your database is in WAL > mode.
Yes, that is correct - the entire bulk insert operation is wrapped into a single BEGIN…COMMIT transaction. My original results were done without the use of any PRAGMAs, so SQLite was in DELETE mode. > If he did, and that does seem very fast, I would guess he used some PRAGMAs > to speed things > up, or did it on a virtual machine where the whole operation was done in > memory and flushed to > disk later. > > If you're running on a Virtual Machine then you have lost ACID anyway, so you > might as well > sacrifice all durability using PRAGMAs like > > PRAGMA synchronous = OFF > > If your program needs to do other things after the huge inserts are finished, > close the database > connection and open a new one to make sure changes are written to disk and > the PRAGMAs are > reset. I am a wee bit suspicious of his results (he spoke of those numbers off the top of his head, so he might have remembered them wrongly). However, his original code that I was working on did not make use of any PRAGMAs and did not run on a virtual machine (we both use macs, and the target environment is a Linux-based cluster). I have tried several pragmas: PRAGMA cache_size=400000; PRAGMA journal_mode=MEMORY; PRAGMA locking_mode=EXCLUSIVE; PRAGMA count_changes=OFF; PRAGMA auto_vacuum=NONE; PRAGMA temp_store = MEMORY; PRAGMA synchronous=OFF; and it seems that they don’t really improve on my insert speed much - variances about +/- 0.3s on 500K inserts. Kevin. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
