Norman Young wrote: > D. Richard Hipp wrote: > > It turns out to be faster to read images directly out of > > SQLite BLOBs until the image gets up to about 15KB on > > windows and up to about 60KB on linux. > > And even for much larger images, the performance difference > > between reading from SQLite and reading from a file is not > > that great, so it is a reasonable thing to do to read from > > SQLite if transactions are important to you or if it is > > just more convenient.
> Reliability is paramount. If there is no way to achieve > transactional semantics using files outside of SQLite, then > any discussions about performance and programmer convenience > are purely academic. The application must behave deterministically. I have done this as folllows: 1. Every file is given a unique name using mkstemp(). This is not fast. 2. Every file is written only once, and never overwritten. If a file needs to be replaced, a new file (with a new name) is created, and the link to the file in the SQLite database is updated. 3. The file must be fsync()'d, and its directory must be fsync()'d, before any attempt is made to update the links that are in the SQLite database. Then, the SQLite database is committed, which results in at least two more fdatasync()s. So, basically, you have to call the slowest filesystem operation four times every time you update a file. 4. File deletion is slow, so I have a seperate process that periodically runs to delete all the replaced files asynchronously with any requests. All of this makes inserting/updating files very slow compared to just stuffing them into SQLite. It will only have a raw performance advantage over storing files in SQLite when the files are large, when I cache file descriptors (so I don't have to repeatedly call open() to read files), and when I am using optimized I/O calls like sendfile(), or mmap()ing into shared memory. It *does* have the advantage that you can significantly reduce the amount of time the database is locked when files are inserted/updated. - Brian _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
