Suppose you have a 1MB row in SQLite and you want to read the whole thing. SQLite must first ask for the 1st 1K page and wait for it to be retrieved. Then it asks for the 2nd 1K page and waits for it. And so forth for all 1000+ pages. If each page retrieval requires 1 rotation of the disk, that's about 8.5 seconds on a 7200RPM disk drive.
I assume that the reason for this is that you don't know in advance which are all the pages that a row is stored in, such that you could just ask the database to read 1000 blocks; you would have to look at the overflow indicator within a block to know where the next one is.
While that may be the case now, I would like to suggest an optimization feature for 3.0 that could work around the problem and make working with large rows much faster. This suggestion has at least two variations to choose from; hopefully each would be simple to implement.
...
I'm suddenly on the clock, so I'll continue now briefly, and possibly expand later if there are questions.
1. Add an extra detail or two in references to overflow pages (usually just to the first overflow page) which say that the first/next N overflow pages are in contiguous blocks. Then, when reading, you can ask the operating system to return that many pages all at once. Presumably this would work if the entire row or large value is written at once such as on an insert; you can specify that the row is stored in contiguous pages to begin with.
Something along those lines.
-- Darren Duncan
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
