The idea of temporary tables in-memory is nice, but I do not know how to apply it. I see in the documentation I can use the TEMP in CREATE TABLE, but I am not sure of the effect. Does it mean that the table is created in memory and it is lost in sqlite3_close?
On Fri, Apr 26, 2013 at 8:07 PM, David King <dk...@ketralnis.com> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
