UPSERT or UPDATE-INSERT?

Asked

Viewed 1,445 times

2

Across of that question felt the need to research and understand a little about the UPSERT. I was wondering What blessed command is that? I’ve never seen..

I found some information but it is not clear to me because there is no "basic command", like UPSERT tabela (id=0, valor='nome'), for example.

I usually see codes using the following structure:

IF EXISTS (SELECT 1 FROM tabela WHERE id = 0)
    UPDATE tabela SET campo = 2 where id = 0
ELSE
    INSERT INTO tabela (id, campo) VALUES (0, 1)
END

About upsert found that way:

MERGE INTO tabela
USING (SELECT 0 AS id, 1 AS campo) AS reg
ON tabela.id = reg.id
WHEN MATCHED THEN
     UPDATE SET campo = reg.campo
WHEN NOT MATCHED THEN
     INSERT(id , campo)
     VALUES (reg.id, reg.campo)

Therefore, with regard to performance, what form should I use? o IF which consults the existence by the identifier and defines which flow to follow (Insert or update) or the MERGE (upsert) condensing the two commands?

sql sql-server performance

2 answers

3


What do you call the second block of code in the question? I call it a command. It could be indented to make it clearer that it’s one thing.

You mean you don’t have a password UPSERT? Okay, that’s not necessary, except perhaps to make it easier. If it was a keyword, I would have written it in the appropriate style. I think it’s wrong to even write everything in capital letters because of the grammar.

The command needs to perform atomically and maybe idempotente (not yet complete this, but has several sources saying, without explaining, so I will not buy so easy, have tried to explain to me and not convinced me, was dictionary explanation) one operation or the other.

Is the first in a transaction? Perhaps it can be considered as Upsert, but not a single command. I can’t say this. If it’s not, it certainly isn’t and can even cause problems, even if rare. I imagine the first one doesn’t run in isolation, so it’s all right.

Each database implements as you wish, or fails to implement. As far as I know does not have a standard in SQL ANSI, even because if it has, do a bad job in the implementations.

Whether it interferes with performance or is not implementation detail, but should change very little, other things make more difference, the interpretation of the command will make much more difference than it in itself. The first can be faster because it’s simpler parse it and execute him, but only by speculating. It may be that in the first is occurring a duplicate existence check and this can make the performance worse. Take a test in real case. You can change case by case.

What I know is that some Dbs face difficulties to do right and maintain performance, probably by previous wrong choices.

I don’t know if the MERGE has is an exact equivalence to the first block, then comparing them makes no sense.

  • my main point would be "exist a command (keyword) with the name upsert". I understand that the merge works like this commando (and that yes, it is a single block).

  • I found valid your answer; really the first block (if) has separate operations and perhaps duplicate validation, but still may have better performance than the second (follows the doubt =p)

2

In relation to performance, I think it makes a difference when you have UPSERT natively, as in the database Note that INSERT is an UPSERT, so by giving INSERT as every database already has to validate if there is already the key, he already decides there in that query whether it will be an update or Insert. Now, if you have to perform the query, as in the case of IF or MERGE, I believe it makes no significant difference.

Browser other questions tagged sql sql-server performance

You are not signed in. Login or sign up in order to post.