SQLite Forum

Faster way to insert into WITHOUT ROWID table?
Login
I did, sorry for that. I checked again with 3.37.0, and the results I get are similar (maybe a little bit faster). The modification with the append-bias flag on the earlier version (3.35.4) was faster than the newest version though.

Looking further at the code, I found that I can skip the `NoConflict` and directly do an insert if I use `INSERT OR REPLACE INTO`. That's not faster or slower, this does mean I can pass the `BTREE_APPEND` flag to `sqlite3BtreeInsert`. But that flag isn't used, since the insert is an "unpacked record" :-(

From profiling it seems that quite a large part of the time is being spent finding the place to insert the new row. This is because it apparently goes to the root page and descends the B-tree for every single row being inserted. I think most of that can be avoided if the `seekResult` parameter and/or the `BTREE_SAVEPOSITION` flag of `sqlite3BtreeInsert` can be used somehow. I'm not familiar enough with the SQLite code to do that though.

From the comments, it appears that inserts in rowid-tables do have such optimizations. I might have misunderstood these parts of the SQLite code, but given that the above is true-ish: is it worthwhile to add such optimizations for without-rowid tables too? Ideally the `NoConflict` opcode could use this optimization too.