auto_vacuum Turn off autovacuum and just run it yourself when you're idle foreign_keys Turn off foreign keys checks (or just don't use foreign keys) ignore_check_constraints Same journal_mode OFF might actually be faster than MEMORY, but disables rollback support locking_mode EXCLUSIVE can be mildly faster in some cases
secure_delete OFF synchronous OFF as you said cache_size beef up as you said. this won't always make everything faster though, since it can starve the other processes on your machine for memory even for rarely-used sqlite data when they could potentially make better use of the OS page cache. temp_store set to MEMORY, this will help if you have queries that create temporary tables, even if you're not doing so yourself (e.g. unindexed group bys) If you can, use an entirely :memory: database. This may not work for you, but if it does, great. Since you're not using journal_mode = WAL this is moot for you, but if you were I'd say turn off wal_autocheckpoint and wal_checkpoint yourself when you're idle For my somewhat-similar use case I find that writing intermediate changes to an in-memory table (insert into my_temp_table) and periodically flushing those to disk (insert into real_table select from my_temp_table; delete from my_temp_table) can help speed things up if a lot of index-updating is involved in the on-disk table. Make sure you're doing all of your inserts in a transaction. inserting is pretty cheap, but committing a transaction is expensive, and if you're not in a transaction each insert is its own transaction Make sure you're re-using your prepared statements Play with page_size to get it right for your write patterns Don't use a connection concurrently, it's doing internal locking anyway. If you must, use the shared page cache. If you're doing it from multiple processes, use WAL mode. On Friday, 26 April, 2013 at 10:44, Paolo Bolzoni wrote: > The subject pretty much says it all, I use sqlite3 as a way to save > temporary results from a calculation. > > In this context I do not care about safety of the data. If the program > fails or there is a blackout I will just delete the sqlite3 file, eventually > fix the bug, and restart. > > At the moment I use this pragmas: > > PRAGMA synchronous = OFF; > PRAGMA journal_mode = MEMORY; > PRAGMA cache_size = -10240; > > Is there anything other I can do to speed-up sqlite3 at expenses of > safety? > > Thanks, > Paolo > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org (mailto: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
