Pierre, Have you considered storing the blob data into a file?
Just keep a record of the blob in the sqlite.db if you store all the blobs in a single file then you'll need a filename, begin, length. Or if one blob per file just the filename.. Just an idea. Not really sure which would be faster. --- On Fri, 3/13/09, Pierre Chatelier <k...@club-internet.fr> wrote: > From: Pierre Chatelier <k...@club-internet.fr> > Subject: [sqlite] Advices to get max performance with SQLITE and BLOBS > To: sqlite-users@sqlite.org > Date: Friday, March 13, 2009, 3:49 AM > Hello, > > I am using SQLITE to store and retrieve raw data blocks > that are > basically ~300Ko. Each block has an int identifier, so that > insert/ > select are easy. This is a very basic use : I do not use > complex > queries. Only "INSERT/SELECT where index=..." > > Now, I am thinking about performance, for writing a > sequence of a few > hundreds 300k blocks, as fast as possible. > Obviously, I use bind_blob(), blob_read() and blob_write() > functions. > I have already tuned the PRAGMAs for > journal/synchronous/page_size/ > cache, so that it's rather efficient. > I do not DELETE any content and the whole database is > dropped after > use: VACUUM is not important. > > There are other ways to optimize, but I wonder if it is > worth, or it > the gain would be only marginal regarding what I am doing. > 1)recompile SQLite ? Which compile options would help in > this case ? > 2)using other memory allocators ? I am not sure that > writing big data > blocks triggers many calls to malloc() > 3)using compression ? zlib could help, but since my data > does not > compress very well (Let's say an average 20% space can > be saved per > block), I am not sure that the compression time will > balance the > writing time. > > Of course, I am only asking for advices regarding your > experience, > there is certainly no exact answer, and it will always > depend on my > data. > > Regards, > > Pierre Chatelier > > _______________________________________________ > 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
