On 16 March 2018 at 21:44, Paul <de...@ukr.net> wrote: > A few years back I've been asking the same question. To be honest, there's > no more > efficient alternative, than the one that can be implemented within library > itself. > Both performance-wise and productivity-wise. > > Doing hacks with INSERT + UPDATE or UPDATE + INSERT leaves us with > problems: > * Which strategy to choose, INSERT + UPDATE or the reverse? No way to > make this generic without hurting performance > * No matter the strategy, we end up with two queries which leads to extra > code that has to be maintained and kept in sync plus a penalty from > preparing two statements > * Existence of two statements leaves us vulnerable to race conditions, > which adds two extra statements to BEGIN and COMMIT a transaction >
I agree with your overall sentiment, but BEGIN/COMMIT actually eliminates two statements because in the standard mode of operation (ie. autocommit) you're essentially doing: (implicit) BEGIN INSERT ... (implicit) COMMIT (implicit) BEGIN UPDATE ... (implicit) COMMIT By making the BEGIN/COMMIT explicit you reduce the overall work when two statements are required. It does seem like sqlite could avoid an extra btree lookup if it implemented UPSERT itself, but since the required pages are practically guaranteed to be in cache for the second query I wonder how many rows you'd need in a table for it to make a significant difference. As you say the main benefit would be to avoid synthesising two statements in user code. -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
