> 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?
There are a few things at work here. You can have your entire database in memory (and is lost on close) by giving sqlite_open the filename :memory: Additionally and unrelatedly, you can create temporary tables (CREATE TEMP TABLE, as you say). These are tables that disappear on close. They are associated with the database connection, not the database file itself. They are usually stored in temporary files in a different on-disk location from your main database file. With the pragma temp_store set to MEMORY, the temporary tables can be entirely in memory, even if the rest of your database isn't. Using this, you can mix on-disk tables (in your regular database) and in-memory tables (temporary tables, when temp_store=MEMORY). That lets you easily keep some intermediate stuff in memory and only flush out the disk tables when you're ready. Sometimes (depending on your usage patterns of course) this batching can help speed things up. > > On Fri, Apr 26, 2013 at 8:07 PM, David King <dk...@ketralnis.com > (mailto: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 (mailto:sqlite-users@sqlite.org) > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > 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
