On 4 Mar 2017, at 11:25pm, Yuri <y...@rawbw.com> wrote: > What is the reason to expire the blob handle when unrelated fields are > modified?
Because the BLOB has moved in the file. Because SQLite stores all of a row’s data in one continuous chunk. When you change any field it has to write the entire row of data back to the database file and the BLOB can end up in a different place. (Don’t forget that it’s not as simple as changing the contents of the database file since the row is actually written to the journal file first.) > Another bug is that reopening the blob every time is incredibly slow. Why is > opening/closing the blob is so much slower? It also seems to get slower with > the size of the data in the blob. Related to your first question. SQLite has to read through the data for that row in order to find out where the BLOB starts. Then it has to read through the blob until it finds the octet you want to modify. In the database a BLOB may be spread over several non-contigous pages. So finding the start of the bit you want to modify is non-trivial. It does seem to be that you are using BLOBs as a continuous data store. If you are modifying just one BLOB at a time it might be worth reading it from the file, keeping it in memory until you are finished with the modifications, then writing it back in one chunk using UPDATE. PS: This list uses a specific meaning for 'bug'. It means that SQLite gives the wrong result back to the programmer, either by storing the wrong thing in the database or by reporting the wrong thing when reading the database or by corrupting the database. Things that aren’t bugs include opportunities to improve speed. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
