Hello Paul, My experiences with blobs suggests it's better to keep them in a different DB file. My uses sounded very similar to yours, tables of normal data interleaved with blob inserts. The physical process of having to move from page to page seems to be the bottleneck, not Sqlite itself. I was working with multi-gig DB files so, this might not be as true for smaller files.
C Tuesday, March 2, 2010, 2:54:06 AM, you wrote: PV> Hi there, PV> I'm wondering how larger BLOBs in a database affect performance PV> of accessing the non-blob data. We've got a database with PV> potentially a few million records in the main tables (of just PV> strings and ints), but joined to that data set we want to store up PV> to thousands (maybe 75000 max) of blobs ranging in size from 75kB PV> - 4MB. If it comes down to it, we can use the filesystem for PV> these, but we'd prefer to store them in a database if it didn't PV> bog things down (cleaner to manage implementation-wise and PV> user-experience-wise). Now I'm guessing that storing all those PV> blobs will slow down access to the main tables (assuming records PV> are added gradually - most without associated blobs, some with), PV> because records would be spread out over many more pages (more PV> seeking / disk access) - is that correct? Would performance PV> likely be better if the blob table were stored in a separate PV> database file? Also, is it possible to adjust page size on a per-database basis PV> (I'm thinking larger pages for the blob database would be PV> better, right?)? Any other suggestions to do this efficiently? PV> Sorry if this is answered elsewhere; when searching, most of the PV> BLOB performance posts I've seen are about storing / retrieving PV> the blobs. We're not so worried about the time to retrieve / PV> store the blobs as much as time to access the main tables. PV> Thanks! PV> Paul PV> _______________________________________________ PV> sqlite-users mailing list PV> sqlite-users@sqlite.org PV> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Teg mailto:t...@djii.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
