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?
这是预期碰撞次数的问题吗? (IE只有在条目已经存在的可能性很小的情况下才能更快地插入。)它是否依赖于我正在运行的数据库类型?就此而言,有一种方法会不断向我的错误日志添加插入错误,这是不好的做法吗?
Thanks.
5 个解决方案
#1
7
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.
如果插入因索引违规而失败,则最多比检查记录存在的速度慢一些。 (两者都需要检查索引是否包含该值。)如果插入成功,则发出两个查询要比发出一个查询慢得多。
#2
0
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?
您可以使用INSERT IGNORE,这样如果密钥已经存在,则只会忽略insert命令,否则将插入新行。这样,您需要发出单个查询,该查询检查重复值以及插入新值。仍然要小心INSERT IGNORE,因为它将每个错误变成警告。阅读此帖子的插入忽略重复键忽略?
#3
0
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.
我认为INSERT IGNORE INTO ....可以在这里使用,要么插入要么忽略它。如果使用IGNORE关键字,则执行INSERT语句时发生的错误将被视为警告。例如,如果没有IGNORE,则复制表中现有UNIQUE索引或PRIMARY KEY值的行会导致重复键错误,并且语句将中止。使用IGNORE时,仍未插入行,但未发出错误。
#4
0
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可以使用REPLACE而不是INSERT来覆盖旧行。
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.
REPLACE与INSERT的工作方式完全相同,只是如果表中的旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值,则在插入新行之前删除旧行。
Else use the INSERT IGNORE as it will either inserts or ignores.
否则使用INSERT 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.
复制表中现有UNIQUE索引或PRIMARY KEY值的行会导致重复键错误,并且语句将中止。使用IGNORE时,仍未插入行,但未发出错误。
#5
0
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?
如果你的意图是插入如果它是一个新的记录或更新记录,如果它已经存在那么如何做一个UPSERT?
Check out - http://vadivel.blogspot.com/2011/09/upsert-insert-and-update-in-sql-server.html
退房 - 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]
我们可以尝试直接更新记录,而不是检查记录是否存在。如果没有匹配的记录,则@@ RowCount将为0.基于此,我们可以将其作为新记录插入。 [在SQL Server 2008中,您可以使用MERGE概念]
EDIT: Please note, I know this works for MS SQL Server and I don't know about MySQL or ORACLE
编辑:请注意,我知道这适用于MS SQL Server,我不知道MySQL或ORACLE
#1
7
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.
如果插入因索引违规而失败,则最多比检查记录存在的速度慢一些。 (两者都需要检查索引是否包含该值。)如果插入成功,则发出两个查询要比发出一个查询慢得多。
#2
0
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?
您可以使用INSERT IGNORE,这样如果密钥已经存在,则只会忽略insert命令,否则将插入新行。这样,您需要发出单个查询,该查询检查重复值以及插入新值。仍然要小心INSERT IGNORE,因为它将每个错误变成警告。阅读此帖子的插入忽略重复键忽略?
#3
0
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.
我认为INSERT IGNORE INTO ....可以在这里使用,要么插入要么忽略它。如果使用IGNORE关键字,则执行INSERT语句时发生的错误将被视为警告。例如,如果没有IGNORE,则复制表中现有UNIQUE索引或PRIMARY KEY值的行会导致重复键错误,并且语句将中止。使用IGNORE时,仍未插入行,但未发出错误。
#4
0
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可以使用REPLACE而不是INSERT来覆盖旧行。
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.
REPLACE与INSERT的工作方式完全相同,只是如果表中的旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值,则在插入新行之前删除旧行。
Else use the INSERT IGNORE as it will either inserts or ignores.
否则使用INSERT 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.
复制表中现有UNIQUE索引或PRIMARY KEY值的行会导致重复键错误,并且语句将中止。使用IGNORE时,仍未插入行,但未发出错误。
#5
0
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?
如果你的意图是插入如果它是一个新的记录或更新记录,如果它已经存在那么如何做一个UPSERT?
Check out - http://vadivel.blogspot.com/2011/09/upsert-insert-and-update-in-sql-server.html
退房 - 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]
我们可以尝试直接更新记录,而不是检查记录是否存在。如果没有匹配的记录,则@@ RowCount将为0.基于此,我们可以将其作为新记录插入。 [在SQL Server 2008中,您可以使用MERGE概念]
EDIT: Please note, I know this works for MS SQL Server and I don't know about MySQL or ORACLE
编辑:请注意,我知道这适用于MS SQL Server,我不知道MySQL或ORACLE