Is inserting a new database record faster than checking for the first record?

Question

I was once told that it’s faster to just start the insert and paste the failure than to check if the database record exists and then insert if it is missing.

I was also told that most databases are highly optimized for reading, not writing, so a quick check was faster than a slow insert?

Is this a matter of the expected number of collisions? (IE is faster to embed only if there is a slight chance that the record already exists.) Does it depend on the type of database I'm running? And in this regard, does bad practice have a method that will constantly add insert errors to my error log?

Thanks.

Answer 1

If the insert fails due to an index violation, it will be as slow as possible than checking that the record exists. (Both require checking if the index contains a value.) If the insert is successful, then issuing two queries is much slower than issuing.

Answer 2

You can use INSERT IGNORE so that if the key already exists, the insert command is simply ignored, otherwise a new line will be inserted. Thus, you need to issue a single query that checks for duplicate values, and also inserts new values.
anyway Be careful with INSERT IGNORE as it turns EVERY error into a warning. Read this post to insert ignore
When re-ignoring the key?

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 considered warnings. For example, without IGNORE, a row that duplicates an existing UNIQUE or PRIMARY KEY index in a table causes an error with a duplicate key, and the statement is aborted. With IGNORE, the row is still not inserted, but no error is thrown.

Answer 4

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

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

Otherwise, use INSERT IGNORE , as it will either insert or ignore.

a row that duplicates an existing UNIQUE or PRIMARY KEY index in the table raises a duplicate key error, and the statement aborts. With IGNORE, the row is still not inserted, but no error is thrown.

Answer 5

If your intention is to insert if its a new record OR Update the record if it already exists, but what about running UPSERT?

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

Instead of checking if the record exists or not, we can try to update it directly. If there is no corresponding entry, then @@ RowCount will be 0. Based on this, we can insert it into a new entry. [In SQL Server 2008, you can use the MERGE concept for this]

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