Pavel Ivanov <paiva...@gmail.com> wrote: > On Fri, Mar 29, 2013 at 12:17 PM, Jeff Archer > <jsarc...@nanotronicsimaging.com> wrote: >> I have previously made an apparently bad assumption about this so now I >> would like to go back to the beginning of the problem and ask the most >> basic question first without any preconceived ideas. >> >> This use case is from an image processing application. I have a large >> amount of intermediate data (way exceeds physical memory on my 24GB >> machine). So, I need to store it temporarily on disk until getting to next >> phase of processing. I am planning to use a large SSD dedicated to holding >> this temporary data. I do not need any recoverability in case of hardware, >> power or other failure. Each item to be stored is 9 DWORDs, 4 doubles and >> 2 variable sized BLOBS which are images. >> >> I could write directly to a file myself. But I would need to provide some >> minimal indexing, some amount of housekeeping to manage variable >> sized BLOBS and some minimal synchronization so that multiple instances of >> the same application could operate simultaneously on a single set of data. >> >> So, then I though that SQLite could manage these things nicely for me so >> that I don't have to write and debug indexing and housekeeping code that >> already exists in SQLite. >> >> So, question is: What is the way to get the fastest possible performance >> from SQLite when I am willing to give up all recoverability guarantees? > > Use > pragma journal_mode = off; > pragma synchronous = off; > pragma locking_mode = exclusive; > > In addition to that you may issue BEGIN statement at the beginning of > the application and never COMMIT.
Yes that should be the fastest. In addition: * make sure that you prepare your INSERT query just once before the insert loop, and use bind/step/reset in the insertion loop. * avoid duplicating the implicit uid index with another primary key by using INTEGER PRIMARY KEY and not something like INT PRIMARY KEY * if you have indexes, make sure that you create them after all the inserts, rather than before. * if you have several tables to populate, you can consider storing them in different databases, and populate them in parallel in different processes or threads. Then later you can ATTACH all of them to see them as a unique database. Regards Dominique _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
