Is checking for primary key value before insert faster than using try-catch?

Question

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?

Answer 1

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.