Hi all As discussed before, I have performance issues when using sqlite on big (multi gig) databases. I am still trying to use sqlite as the main db because it simplifies deployment so much.
The main reason seems to be that inserting in the btree is very slow (even using transactions) because a lot of data has to be moved around in the file. Would using sparse files, or at least leave some space between group of nodes, help, so that the need to move data around in the file is not needed as much?. Maybe that could be a pragma of some sort (so that people that still want the most compact db file won't suffer)? Even better, maybe it's a setting I didn't see :) An other concern I have is row size: the average data in our data contains a blob of about 3 kb. My understanding is that in that case, the first kilobyte (primary key included) is stored in the b-tree node and the rest somewhere else in the file. My question now is: is there some sort of fragmentation inside the dbfile happenning with this extra data? Nicolas
