3

Is it better to write:

IF EXISTS (SELECT ...) OR EXISTS (SELECT ...)
  BEGIN
    INSERT INTO myTable
    VALUES ('myValue1', 'myValue1')
  END

Or to write:

INSERT INTO myTable
SELECT 'myValue1', 'myValue1'
WHERE EXISTS (SELECT ...) OR EXISTS (SELECT ...)

?

I mean better in terms of performance and readability.

Pileggi

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
lamarmora
  • 1,116
  • 4
  • 16
  • 32

1 Answers1

6

You should use the latter, not really a question of efficiency (I suspect there is no significant difference between the two), but the least likely to meet a race condition. If two inserts run concurrently, there is a small chance that when using the first method the same record is inserted by another thread in between checking if the value exists and actually doing the insert. Since both the check and the insert are done using the same lock in the latter this is less likely to occur.

So in my opinion, both readability and performance come a very distant second in terms of importance to accuracy and thread safety.

GarethD
  • 68,045
  • 10
  • 83
  • 123
  • @pileggi I would write it as `WHERE EXISTS (SELECT... UNION ALL SELECT ...)` – Magnus Dec 19 '13 at 09:51
  • 1
    Putting it in the same statement [doesn't prevent the race condition](http://stackoverflow.com/q/3407857/73226) – Martin Smith Dec 19 '13 at 11:06
  • @MartinSmith Correct, I just realised my incosistency, to begin with I said using `WHERE EXISTS` is the *least likely* to meet the race condition, but later said it *won't happen* using the `WHERE EXISTS` method. I have edited so that the answer is consistent in saying it less likely but still not flawless. – GarethD Dec 19 '13 at 15:15
  • @MartinSmith Thank you! So... what is the best option to avoid a race condition? – lamarmora Dec 20 '13 at 15:27
  • @pileggi - Depends what your `SELECT` statements are. You haven't shown us. You might need locking hints or to up the isolation level if you want to ensure that the situation doesn't change between the read and the insert. There is an example of using locking hints in the second answer in the link above. – Martin Smith Dec 20 '13 at 15:35