Whatever the case, it will be, on average, 1.5x time less efficient than a 'I-Hope-Would-Be' UPSERT. It all depends on the probability of INSERT to fail. In the scenario, where updates are frequent and inserts are rare, INSERT + UPDATE approach takes ~2x performance overhead. In most cases we iterate B-Tree 2 times: once for INSERT, that will fail and second time for UPDATE. In reverse scenario, where inserts are frequent and updates are rare, INSERT + UPDATE approach will have almost no overhead. But how would you know which scenario would take place, to adjust the order of calls? Especially when you write a generic Insert-Or-Update method? So, whatever strategy you choose (either UPDATE or INSERT is first), statistically, on average, you have 50% of cases where UPDATE would be efficient if performed first and 50% of cases where the reverse is true.
If implemented inside the SQLite engine, overhead can be removed. It is possible to descend B-Tree once, either to find the row to UPDATE or a hint where to INSERT new row. The only thing that puzzles me is why it wasn't implemented years ago. 30 June 2016, 12:01:56, by "Simon Slavin" < slav...@bigfraud.org >: On 30 Jun 2016, at 8:24am, Olivier Mascia < o...@integral.be > wrote: > Of course writing straight code in C/C++ it's rather simple to emulate > situations where you want to update some values in an existing row, creating > the row if needed. The standard way of doing this is to do two commands: 1) INSERT without the REPLACE 2) UPDATE When step 1 fails because the key values already exist you trap this and explicitly ignore it in your code (commented, because you're being nice to other programmers). Then it's always the UPDATE which updates the fields you care about. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
