Is inserting a new database entry faster than checking if the entry exists first?

Question

I was once told that it is faster to just run an insert and let the insert fail than to check if a database entry exists and then inserting if it is missing.

I was also told that that most databases are heavily optimized for reading reading rather than writing, so wouldn't a quick check be faster than a slow insert?

Is this a question of the expected number of collisions? (IE it's faster to insert only if there is a low chance of the entry already existing.) Does it depend on the database type I am running? And for that matter, is it bad practice to have a method that is going to be constantly adding insert errors to my error log?

Thanks.

Answer 1

If the insert is going to fail because of an index violation, it will be at most marginally slower than a check that the record exists. (Both require checking whether the index contains the value.) If the insert is going to succeed, then issuing two queries is significantly slower than issuing one.

Answer 2

You can use INSERT IGNORE so that if the key already exist, the insert command would just be ignored, else the new row will be inserted. This way you need to issue a single query, which checks the duplicate values as well inserts new values too.
still Be careful with INSERT IGNORE as it turns EVERY error into a warning. Read this post for insert ignore
On duplicate key ignore?

Answer 3

I think INSERT IGNORE INTO .... can be used here, either it will insert or ignore it. If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

Answer 4

If you want to delete the old value and insert a new value you can use REPLACE You can use REPLACE instead of INSERT to overwrite old rows.

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Else use the INSERT IGNORE as it will either inserts or ignores.

a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

Answer 5

If your intension is to Insert if its a new record OR Update the record if it already exists then how about doing an UPSERT?

Check out - http://vadivel.blogspot.com/2011/09/upsert-insert-and-update-in-sql-server.html

Instead of checking whether the record exists or not we can try to Update it directly. If there is no matching record then @@RowCount would be 0. Based on that we can Insert it as a new record. [In SQL Server 2008 you can use MERGE concept for this]

EDIT: Please note, I know this works for MS SQL Server and I don't know about MySQL or ORACLE