On Thu, Mar 22, 2018 at 3:22 PM, Richard Hipp <d...@sqlite.org> wrote:
> On 3/22/18, Dominique Devienne <ddevie...@gmail.com> wrote: > > > > Hi Richard. Is 8.d from https://www.sqlite.org/draft/ > releaselog/current.html > > the result of this inquiry? > > No. I just happened to notice the inefficiency while I was working on 8d. > > > And is there a chance the "some kind of indexed lookup" you mention above > > is likely to land in the future? > > That is still unclear, but probably not. > > > If we insert 1M rows in that schema.i DB with 117 AUTOINCREMENT tables, > are > > we really full-scanning > > up to 117M rows just to get the ROWIDs of those 1M rows? Thanks for your > > insights into this. --DD > > No. You are only doing a full table scan on the 117 entries of the > sqlite_sequence table. And as all 117 entries probably all fit on the > same page, adding an index probably won't speed things up any. > What I meant was that each row-insert incurs a sqlite_sequence full-scan, and thus inserting 1M rows logically incurs the multiplication of both cardinality. I already guessed sqlite_sequence is cached, since "extremely hot", but that's still a lot of rows even if cached, even more so since sqlite_sequence also needs to be *written* to, not just read from, to record the new max rowid. > Another idea is to implement an in-memory cache so that if you do > multiple inserts into an AUTOINCREMENT table, it only does the scan of > sqlite_sequence once. > > In private communications with some groups that have a lot of > AUTOINCREMENT tables and who performance sensitive and who heavily > instrument their code, nobody has noticed any performance issues > associated with scanning the sqlite_sequence table. For that reason, > we probably are not going to take any action on this right now. But I > will hold open the possibility of enhancing the sqlite_sequence lookup > at some point in the future if somebody (especially a client) notices > the potential for a performance benefit. Thanks, this is reassuring. Many thanks for the quick update. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
