There is quite often situation when you need to execute INSERT, UPDATE or DELETE statement based on some condition. And my question is whether the affect on the performance of the query add IF EXISTS before the command.
Example
IF EXISTS(SELECT 1 FROM Contacs WHERE [Type] = 1) UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1
What about INSERTs or DELETEs?
The bigger the table (number of and size of columns) the more expensive it becomes to delete and insert rather than update. Because you have to pay the price of UNDO and REDO. DELETEs consume more UNDO space than UPDATEs, and your REDO contains twice as many statements as are necessary.
int executeUpdate(String sql): executes an INSERT, UPDATE or DELETE statement and returns an update account indicating number of rows affected (e.g. 1 row inserted, or 2 rows updated, or 0 rows affected).
With a MERGE you can can 'sync' two tables by executing an insert, delete and update in ONE statement. A MERGE is much more than that though; it offers you a wide range of options in comparing and syncing tables. You can even keep track of the output of the merge.
I'm not completely sure, but I get the impression that this question is really about upsert, which is the following atomic operation:
UPDATE
the target;INSERT
the row into the target;DELETE
the row from the target.Developers-turned-DBAs often naïvely write it row-by-row, like this:
-- For each row in source IF EXISTS(<target_expression>) IF @delete_flag = 1 DELETE <target_expression> ELSE UPDATE target SET <target_columns> = <source_values> WHERE <target_expression> ELSE INSERT target (<target_columns>) VALUES (<source_values>)
This is just about the worst thing you can do, for several reasons:
It has a race condition. The row can disappear between IF EXISTS
and the subsequent DELETE
or UPDATE
.
It's wasteful. For every transaction you have an extra operation being performed; maybe it's trivial, but that depends entirely on how well you've indexed.
Worst of all - it's following an iterative model, thinking about these problems at the level of a single row. This will have the largest (worst) impact of all on overall performance.
One very minor (and I emphasize minor) optimization is to just attempt the UPDATE
anyway; if the row doesn't exist, @@ROWCOUNT
will be 0 and you can then "safely" insert:
-- For each row in source BEGIN TRAN UPDATE target SET <target_columns> = <source_values> WHERE <target_expression> IF (@@ROWCOUNT = 0) INSERT target (<target_columns>) VALUES (<source_values>) COMMIT
Worst-case, this will still perform two operations for every transaction, but at least there's a chance of only performing one, and it also eliminates the race condition (kind of).
But the real issue is that this is still being done for each row in the source.
Before SQL Server 2008, you had to use an awkward 3-stage model to deal with this at the set level (still better than row-by-row):
BEGIN TRAN INSERT target (<target_columns>) SELECT <source_columns> FROM source s WHERE s.id NOT IN (SELECT id FROM target) UPDATE t SET <target_columns> = <source_columns> FROM target t INNER JOIN source s ON t.d = s.id DELETE t FROM target t WHERE t.id NOT IN (SELECT id FROM source) COMMIT
As I said, performance was pretty lousy on this, but still a lot better than the one-row-at-a-time approach. SQL Server 2008, however, finally introduced MERGE syntax, so now all you have to do is this:
MERGE target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE <target_columns> = <source_columns> WHEN NOT MATCHED THEN INSERT (<target_columns>) VALUES (<source_columns>) WHEN NOT MATCHED BY SOURCE THEN DELETE;
That's it. One statement. If you're using SQL Server 2008 and need to perform any sequence of INSERT
, UPDATE
and DELETE
depending on whether or not the row already exists - even if it's just one row - there is no excuse not to be using MERGE
.
You can even OUTPUT
the rows affected by a MERGE
into a table variable if you need to find out afterward what was done. Simple, fast, and risk-free. Do it.
That is not useful for just one update/delete/insert.
Possibly adds performance if several operators after if condition.
In last case better write
update a set .. where .. if @@rowcount > 0 begin .. end
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With