哪个是删除插入与 if-update else-insert 的最佳选择? [英] Which is the best choice in delete-insert vs if-update else-insert?

问题描述

更新:

我的错...我在这些表上有一个主键...我的意思是目前没有进一步的索引表.我们可能在看到性能后的未来，因为我们对数据有太多过滤器检索数据 上次我们运行数据库时，索引没有显示出太大的改进调整.

My bad...I have an primary key on those tables..I meant no further indexing currently on the tables. We might have it in the future after seeing the performance and since we have too many filters on the data in retrieving data it did not show much improvement on indexing last time we ran database tuning.

我有一个超过数百万条记录的 4 张大表.现在有一个经常调用的存储过程并更新这些表.这是场景 -

I have a 4 huge tables over millions of records. Now there is stored procedure which is called frequently and updates these table. Here is the scenario -

现在，如果今天存在条目，我需要为今天更新它，否则如果用户没有条目，我需要继续为用户插入一个条目.现在有两种方法可以执行这些操作，因为只有一个 proc 可以执行此操作 -

Now if entry exists for today I need to update it for today and else if entry is not there for the user I need to go ahead and insert an entry for the user. Now there are two ways to go about carrying out these since there is a single proc that does this -

第一种方式 -

IF EXISTS(TABLE1)
--UPDATE where condn
ELSE
--INSERT
IF EXISTS(TABLE2)
--UPDATE where condn
ELSE
--INSERT
IF EXISTS(TABLE3)
--UPDATE where condn
ELSE
--INSERT
IF EXISTS(TABLE4)
--UPDATE where condn
ELSE
--INSERT

第二种方式-

DELETE from TABLE1 where condn
DELETE from TABLE2 where condn
DELETE from TABLE3 where condn
DELETE from TABLE4 where condn

INSERT TABLE1 ENTRY
INSERT TABLE2 ENTRY
INSERT TABLE3 ENTRY
INSERT TABLE4 ENTRY

现在第二种方式看起来更简单，但可能更耗时……我不确定哪种方式最好.任何人都可以帮助或指导我在这里..谢谢！

Now the second way looks simpler but it might be more time consuming ...I am not sure which way is the best here. Can anyone please help or guide me out here..thanks!

推荐答案

if-exists-then-update-else-insert 方法可能比 delete-insert 方法代码更多em>，但是(取决于在表上定义的索引数量和类型)服务器的工作要少得多.

The if-exists-then-update-else-insert approach may be more code than delete-insert, but (depending on how many and what kind of indexes are defined on the tables) it is a lot less work for the server.

• DELETE 或 INSERT 操作需要修改每个索引，期间.
• UPDATE 操作只需要修改其字段在此实例中已更新的索引.

因此，除非您使用更新修改记录的每个索引字段，否则越长的方法越有效.

So unless you modify every indexed field of the record with your updates, the longer approach is the more efficient one.

您的问题更新表明，除了主键(我假设它是一个聚集键)之外，您目前没有任何索引.所以比较:

Your question update says that currently you do not have any indexes apart from the primary key (which I assume is a clustered key). So in comparison:

当该行已经存在时，它是 1 次聚集索引查找(假设您通过它们的 PK 找到记录)并且:

When the row already exists, it's 1 clustered index seek (assuming you find records by their PK) and:

• delete-insert:4次写操作(删除行、删除PK索引行、插入行、插入PK索引行)
• check-update/insert:1 次写入操作(更新行)

• delete-insert: 4 write operations (delete row, delete PK index row, insert row, insert PK index row)
• check-update/insert: 1 write operation (update row)

当该行不存在时，它是 1 次聚集索引查找并且:

When the row does not yet exist, it's 1 clustered index seek and:

• delete-insert:2 次写入操作(插入行，插入 PK 索引行)
• check-update/insert:2 次写入操作(插入行，插入 PK 索引行)

• delete-insert: 2 write operations (insert row, insert PK index row)
• check-update/insert: 2 write operations (insert row, insert PK index row)

存在的索引越多，删除/插入对已经存在的行的执行就越差.此外，由于可避免的写入操作，它可能导致非聚集索引变得不必要的碎片.

The more indexes exist, the worse will delete/insert perform for rows that already exist. Plus it can cause non-clustered indexes to become unnecessarily fragmented due to avoidable write operations.

这篇关于哪个是删除插入与 if-update else-insert 的最佳选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！