Thanks everyone, I cannot apply all the suggestions (for example I do use foreign keys), but probably I can improve the performance of the calculations.
On Sat, Apr 27, 2013 at 6:48 AM, David King <dk...@ketralnis.com> wrote: >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
