6

Basically I have a table with two-field primary key column (memberid, messageid) and I have a stored proc that inserts a new row into that table.

Right now I check if a row with the PK exists and insert if not, but I have already ran into situation when the row was inserted by a different process at the time right after the check and before the actual insert, so I'm thinking of an alternative way.

I DO NOT want to use transactions for performance reasons, so I was thinking of enclosing the INSERT into try-catch and skipping the check altogether. If the line already exists insert will fail but will be silenced by "catch" which is ok.

My question is - is throwing an error and catching it an expensive operation?

Andrey
  • 20,487
  • 26
  • 108
  • 176
  • 2
    Related: http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there – Martin Smith Mar 16 '11 at 20:49
  • You can create an `UNIQUE INDEX` on those columns with ignore duplicate key, so you don't have to check before, and neither do a `TRY-CATCH` – Lamak Mar 16 '11 at 20:50
  • @Lamar - PK is unique by definition. And how this fact helps me? – Andrey Mar 16 '11 at 20:56
  • @Martin - thanks for the link, now I feel more confident about try-catch solution :) – Andrey Mar 16 '11 at 20:59
  • @Andrey: I know that PK is unique, the important part was the "ignore duplicate keys" part. you can go to this link http://msdn.microsoft.com/en-us/library/ms175132.aspx and see the `IGNORE_DUP_KEY` part – Lamak Mar 16 '11 at 21:02
  • @Lamak: read this thoroughly: it is unrelated to insertions – gbn Mar 16 '11 at 21:35
  • @gbn - I think it's exactly related to inserts, but I'm not sure I want to go that way; I will most probably be doing some post-processing in "catch" area, like update the row – Andrey Mar 17 '11 at 21:46

4 Answers4

3

On SQL 2008, you can just use MERGE - much simpler than either of your approaches.

Also I am not with you on "I DO NOT want to use transactions for performance reasons" - every DML command you execute is a part of some transaction anyway, so there are transactions even if you do not open them explicitly. If you are experiencing performance problems, you can post more details so that you get more help with performance.

Edit: If you need really fast inserts, do not insert one row at a time. Add sets of rows, and use MERGE - the advantage you will get from inserting batches of rows at a time should far outweight any minor improvements you will get from optimizing the speed of adding one row.

Anyway, theoretical reasoning about anything related to databases is usually not good enough. You really need to benchmark to determine what is faster. What you are calling "unnecessarily query for an existing line" may be completely negligible, and you don't know if this is the case until you have measured it under realistic conditions.

A-K
  • 16,804
  • 8
  • 54
  • 74
  • What MATCH does is equal to: IF EXISTS (...) THEN UPDATE OTHERWISE INSERT, and that's exactly what I want to avoid - unnecessary index queries. – Andrey Mar 17 '11 at 22:02
  • Also, I believe INSERT does not lock anything in the table, where running query and then insert in a transaction should lock the whole table to avoid concurrency, thus huge performance hit. – Andrey Mar 17 '11 at 22:04
  • 1
    The following is plain wrong: "insert in a transaction should lock the whole table to avoid concurrency". I would suggest that you run some tests and see for yourself what actually gets locked by an insert. – A-K Mar 19 '11 at 17:57
  • Maybe I was wrong with locking, I'm not very professional with some aspects of SQL Server. But in any case, MATCH wouldn't help me here as it does exactly what I want to avoid - unnecessarily query for an existing line, where in 99% that line wouldn't be there – Andrey Mar 21 '11 at 21:59
0

Yes, throwing exceptions is an expensive operation. However, it may be application specific, but swallowing (silencing, as you put it) the exception is usually not a very good idea.

HardCode
  • 6,497
  • 4
  • 31
  • 54
  • 1
    Do you think throwing an exception rarely is more expensive than a database query always? – Gabe Mar 16 '11 at 20:51
  • Why "swallowing" the error is bad in my case? I know exactly what can go wrong and I'm Ok with it failing - ther eis no way for me to guarantee that there is no row with given PK already in the db unless I use transactions – Andrey Mar 16 '11 at 20:51
  • @Gabe - exactly my point! I think probability of my app stumbling upon this error is below 1%, so the question pretty much is whether throwing one error is more expensive than querying PK 100 times :) – Andrey Mar 16 '11 at 20:52
  • @gbn: And yet [this guy](http://sqlblog.com/blogs/paul_nielsen/archive/2007/12/12/10-lessons-from-35k-tps.aspx) seems to have found the try-catch method to be cheaper (which he summarises in his Lesson #4). – Andriy M Mar 16 '11 at 21:46
0

If you don't expect failures to happen too often then it is ok to handle it via exceptions.

Doing the check everytime will reduce db perfomance which could affect application perfomance as well...

Victor Parmar
  • 5,719
  • 6
  • 33
  • 36
0

If you're not using transactions, you have to account for the possibility that the row might be inserted anyway, so you can't eliminate the exception handling. At best you can minimize the rare exception by doing a query beforehand. However, the cost of doing the query before every insert is likely to be more than the occasional exception.

You'll have to test it yourself to see what's more expensive in real life, though. Odds are that the millisecond it costs for the occasional exception is going to be far less than the cost of constantly querying for the key you're about to insert.

That said, you could use your stored procedure to handle the insert and return an error value when a duplicate key is inserted rather than throw an exception. That should eliminate the performance issue altogether.

Gabe
  • 84,912
  • 12
  • 139
  • 238
  • What do you mean " you could use your stored procedure to handle the insert and return an error value when a duplicate key is inserted rather than throw an exception. That should eliminate the performance issue altogether."? The performance issue is happening because an error is thrown when I am inserting a duplicate, even if I'm handling it afterwards in the proc. Am I missing something? – Andrey Mar 21 '11 at 22:01