Does checking the primary key value before inserting is faster than using try-catch?

Question

Basically, I have a table with a primary key column with two fields (memberid, messageid), and I have a saved proc that inserts a new row into this table.

Now I am checking if a row exists with PK and inserts, if not, but I have already encountered a situation where the row was inserted by another process right after the check and before the actual insert, so I am thinking of an alternative path.

I DO NOT want to use transactions for performance reasons, so I thought about including INSERT in try-catch and skipping validation altogether. If the row already exists, the insert will not be executed, but the catch will be disabled, which is OK.

My question is - is it throwing a mistake and catching it with an expensive operation?

Answer 1

If you do not expect errors to occur too often, then it is normal to handle them through exceptions.

Performing a check each time reduces db performance, which can also affect application performance ...

Answer 2

In SQL 2008, you can simply use MERGE - much easier than any of your approaches.

Also, I'm not with you on the topic “I DO NOT want to use transactions for performance reasons” - every DML command you execute is part of a transaction, so there are transactions, even if you do not open them explicitly. If you are having performance issues, you can post more details to get more help with performance.

Edit: if you need really fast inserts, don't insert one line at a time. Add line sets and use MERGE - the advantage that you get from inserting line batches at a time should greatly reduce any minor improvements you get from optimizing the speed of adding one line.

In any case, theoretical considerations about anything related to databases are usually not good enough. You really need to navigate to determine which is faster. What you call an “unnecessary query for an existing row” can be completely insignificant, and you do not know if this is so, until you have measured it in real conditions.

Answer 3

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

Answer 4

If you are not using transactions, you need to consider the possibility that the row can be inserted anyway, so you cannot eliminate exception handling. In the best case, you can minimize the rare exception by making a request in advance. However, the cost of executing a request before each insertion is likely to be more than a random exception.

You will have to test it yourself to see which is more expensive in real life. The odds are that the millisecond it costs for a random exception will be much less than the cost of a constant request for the key you are about to insert.

However, you can use the stored procedure to handle the insert and return the error value if a duplicate key is inserted into it, and not an exception. This should completely eliminate the performance issue.