3 ms·
I've noticed that MERGE in MSSQL can have a performance penalty as opposed to have separate insert and update statements.
by sebazzz 3y ago
I've noticed that MERGE in MSSQL can have a performance penalty as opposed to have separate insert and update statements.
- swasheck 3y agomssql merge has a lot of problems. it's good for the most basic and straightforward of use cases, but it struggles with a variety of features: https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-to-use-merge/ https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-...
- mwigdahl 3y agoWe stripped MERGE out of all of our production code. Not worth the many corner cases and footguns that remain even after over 15 years of being in the product.
- NicoJuicy 3y agoIn ms sql you can do an update query, check the rowcount and if it's 0, then do an insert. That's how I got an improved query performance for syncing data.
- hot_gril 3y agoI think you need a unique index if you're doing something like this in MS-SQL, Postgres, or MySQL. Otherwise, the default isolation level won't prevent this data race: writer A: begin xact, update, see 0 rows updated writer B: begin xact, insert same thing, end xact writer A: insert, end xact One reason I use ON CONFLICT in Postgres is for explicitness. You're specifically telling the DBMS to upsert, and it'll throw an error if you don't have a unique index to support this.
- hot_gril 3y agoAlso mentioned above, ON CONFLICT DO UPDATE would be able to update the concurrent writer's row instead of throwing a uniqueness violation.