T-SQL 插入或更新
我有一个关于 SQL Server 性能的问题。
假设我有一个表 persons
,其中包含以下列:id
、name
、surname
。
现在,我想在此表中插入一个新行。规则如下:
如果表中不存在
id
,则插入该行。如果
id
存在,则更新。
我这里有两个解决方案:
第一:
update persons
set id=@p_id, name=@p_name, surname=@p_surname
where id=@p_id
if @@ROWCOUNT = 0
insert into persons(id, name, surname)
values (@p_id, @p_name, @p_surname)
第二:
if exists (select id from persons where id = @p_id)
update persons
set id=@p_id, name=@p_name, surname=@p_surname
where id=@p_id
else
insert into persons(id, name, surname)
values (@p_id, @p_name, @p_surname)
什么是更好的方法?似乎在第二个选择中,要更新一行,必须搜索两次,而在第一个选项中 - 只需搜索一次。还有其他解决问题的方法吗?我正在使用 MS SQL 2000。
I have a question regarding performance of SQL Server.
Suppose I have a table persons
with the following columns: id
, name
, surname
.
Now, I want to insert a new row in this table. The rule is the following:
If
id
is not present in the table, then insert the row.If
id
is present, then update.
I have two solutions here:
First:
update persons
set id=@p_id, name=@p_name, surname=@p_surname
where id=@p_id
if @@ROWCOUNT = 0
insert into persons(id, name, surname)
values (@p_id, @p_name, @p_surname)
Second:
if exists (select id from persons where id = @p_id)
update persons
set id=@p_id, name=@p_name, surname=@p_surname
where id=@p_id
else
insert into persons(id, name, surname)
values (@p_id, @p_name, @p_surname)
What is a better approach? It seems like in the second choice, to update a row, it has to be searched two times, whereas in the first option - just once. Are there any other solutions to the problem? I am using MS SQL 2000.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
两者都工作正常,但我通常使用选项 2(mssql 2008 之前的版本),因为它读起来更清晰一些。我也不会强调这里的性能...如果它成为问题,您可以在
exists
子句中使用NOLOCK
。不过,在开始到处使用 NOLOCK 之前,请确保您已经涵盖了所有基础知识(索引和总体架构内容)。如果您知道您将多次更新每个项目,那么考虑选项 1 可能是值得的。选项 3 是不使用破坏性更新。这需要更多的工作,但基本上每次数据更改时都会插入一个新行(从不更新或从表中删除),并且有一个选择所有最新行的视图。如果您希望表包含其所有先前状态的历史记录,那么它很有用,但它也可能有点矫枉过正。
Both work fine, but I usually use option 2 (pre-mssql 2008) since it reads a bit more clearly. I wouldn't stress about the performance here either...If it becomes an issue, you can use
NOLOCK
in theexists
clause. Though before you start using NOLOCK everywhere, make sure you've covered all your bases (indexes and big picture architecture stuff). If you know you will be updating every item more than once, then it might pay to consider option 1.Option 3 is to not use destructive updates. It takes more work, but basically you insert a new row every time the data changes (never update or delete from the table) and have a view that selects all the most recent rows. It's useful if you want the table to contain a history of all its previous states, but it can also be overkill.
选项 1 似乎不错。但是,如果您使用的是 SQL Server 2008,则还可以使用 MERGE,这对于此类 UPSERT 任务可能表现良好。
请注意,您可能希望使用显式事务和 XACT_ABORT 选项此类任务,以便在出现问题或并发更改的情况下保持事务一致性。
Option 1 seems good. However, if you're on SQL Server 2008, you could also use MERGE, which may perform good for such UPSERT tasks.
Note that you may want to use an explicit transaction and the XACT_ABORT option for such tasks, so that the transaction consistency remains in the case of a problem or concurrent change.
您可以使用 @@RowCount 来查看更新是否执行了任何操作。像这样的东西:
You could just use @@RowCount to see if the update did anything. Something like:
我倾向于使用选项1。如果表中有记录,则保存一次搜索。如果没有,你也不会失去任何东西。此外,在第二个选项中,您可能会遇到与锁不兼容相关的有趣的锁定和死锁问题。
我的博客上有更多信息:
http ://sqlblogcasts.com/blogs/piotr_rodak/archive/2010/01/04/updlock-holdlock-and-deadlocks.aspx
I tend to use option 1. If there is record in a table, you save one search. If there isn't, you don't loose anything. Moreover, in the second option you may run into funny locking and deadlocking issues related to locks incompatibility.
There's some more info on my blog:
http://sqlblogcasts.com/blogs/piotr_rodak/archive/2010/01/04/updlock-holdlock-and-deadlocks.aspx
为了更加干燥,我避免将值列表写两次。
name
和surname
列必须可为空。该交易意味着其他用户将永远不会看到“空白”记录。
编辑:清理
Aiming to be a little more DRY, I avoid writing out the values list twice.
Columns
name
andsurname
have to be nullable.The transaction means no other user will ever see the "blank" record.
Edit: cleanup