19 March 2018, 09:26:15, by "Rowan Worth" <[email protected]>:
> On 16 March 2018 at 21:44, Paul <> [email protected]> 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. I agree with you here. But then again you have to issue both BEGIN and COMMIT through SQL statements, ie through the parser. Even if overhead is small, it's still present. It all depends on the scenario that database is used in. If you have a lot of scenarios when you INSERT or UPDATE some data quite frequently then overhead is visible. By manually tweaking strategies in various places (whether to use UPDATE first or INSERT) I've managed to improve performance by tens of percents. Then again, it depends on scenario. Users that use this model not very often will definitely not benefit that much from UPSERT. But the ability to work around, and potentially small benefit to an average user should not be the arguments to dismiss its implementation. After all, half of the features in SQLite3 are not that useful to an average user. > > 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. It's easy to calculate, exactly twice as much time as it takes to do a B-Tree lookup. How big is the piece of the pie, again, depends on the scenario. Fort me personally, the most sad thing is an annoyance. Because I have to maintain two almost identical queries and manually tweak strategies. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
