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

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 afte开发者_C百科r 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?

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.

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...

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.

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.