> On 10/29/15, Jason H <jhihn at gmx.com> wrote: > > > > Ah, so this is what I seem to have missed. The pages... This is unfortunate > > as the read-heavy application won't likely benefit from SQLite. > > Your filesystem and your disk hardware work the same way. Your > application might only ask for 10 bytes, but the underlying OS and > hardware are pulling a complete sector (or perhaps a complete track) > from disk, pass the 10 bytes you asked for off to your app, and > holding the rest in cache. > > So, no, you cannot improve performance by only reading a few bytes > here and a few bytes there. The disk has to work just as hard.
First, thanks everyone, I've learned a lot about SQLite3! You make a very good point here, the OS will be caching disk reads (16-64k from what I understand) so the reading is on average cheaper, but overall more expensive on average if I do not need all the data. I was hoping that those very large tables (width in terms of column number and size, not row count) could benefit from a database being able to discard fields. If I could ask one more question.... If I were to try to work around all this excess reading, how good or bad would it be to take the following approach: Define a set of smaller tables (column count and size) with a common key, then create a view for the specific query, then query the view. create table k (id integer primary key, ... other always used columns...); create table c1 (k integer, data text); -- seldomly used large column create table c2 (k integer, data text); -- seldomly used large column create table c3 (k integer, data text); -- seldomly used large column ... create table c128 (k integer, data text); -- seldomly used large column create view myquery as select k1.id id, c1.data c1, c2.data c2, c3.data c3 from k left join c1 on c1.k=k.id left join c2 on c2.k=k.id left join c3 on c3.k=k.id; select * from myquery; Shouldn't that skip the data I don't need, make good use of disk caches. What I don't know though, is how efficient SQLite3 will be at putting that view together. Did I make a mess giving sqlite3 busy work or did I speed it up? "Nobody ever made them like this! I mean the architect had to be a certified genius or an authentic wacko!" - Dr Ray Stantz, Ghostbusters
