数据库效率那个更高

wangbinwork 天津市神州商龙科技有限公司 技术总监  2008-03-09 06:51:16
假设我现在有一套主细表 细表内数据量在800-1000万条纪录左右
现在我要修改其中1条纪录 使用以下两种方式


1. 使用条件直接update细表的纪录
2. 先根据主表内的条件将细表中所有符合主表条件的纪录全部删除 然后在将该符合主表条件的细表内容进行插入


请问 那种方式效率更高???
...全文
332 点赞 收藏 31
写回复
31 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wangbinwork 2008-04-24
谢谢大家的回复,下面将我的测试信息公布出来。
我的测试环境是
1.分别建立了2个数据库 (防止数据库有缓存问题的存在)
2.在2个数据库中分别建立了1个结构相同的表,字段数是30个。
3.做了一支程序,分别向两个表中插入300万条记录(查完耗时40分钟 晕)

然后分别按照以上两种操作方式进行操作,影响的数据行数为70行,最后结果是:

使用update:耗时1分14秒
使用delete+insert:耗时48.7秒

最终从单次操作上看是使用第二种效率要高一些。

至于楼上朋友所说的日志问题,理论上应该是第二种会高一些没有测试。
回复
jetty_xiang 2008-03-24
12楼分析得很正确
回复
闪破风浪 2008-03-11
[Quote=引用 12 楼 qianjin036a 的回复:]
不是试试的问题,在sqlserver内核里,update语句本来就是由delete和insert两个操作来实现的.
还记得instead of触发器中使用deleted表和inserted表吗?那就是为update操作准备的.

如果要进行update操作,系统将进行delete操作和insert操作,涉及到创建insert表,接口数据向insert表传送,操作表中的数据向delete表中的转移,完整性约束检查,insert表中数据向操作表转移,delete表的删除这些步骤.
如果要进行delete操作加上insert操作,…
[/Quote]
回复
懒得去死 2008-03-11
[Quote=引用 11 楼 w2jc 的回复:]
当然是第一种效率高了。
第二种方法不管怎么说都强制SQL Server去更新相关的索引,而第一种方法则如7楼所说,对索引的影响可大可小。

更新数据量一大,或者使用时间一长之后,
第二种方法会明显导致索引碎片,从而降低速度的。


[/Quote]

同意!
回复
w2jc 2008-03-10
回11楼:
索引是不存在什么碎片的,因为它本身就是一种指针.
对表进行更新,或删除加插入,系统都会对盘区中数据页之间进行数据移动,行位置总是会发生变化的.所以操作效率与索引无关.

-------------------------------------------
建议仔细一下这个:
Microsoft SQL Server 2000 索引碎片整理最佳实践
http://www.dycar.net/blog/article.asp?id=158

上面的内容翻译自MS官方的技术文档:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
回复
parss 2008-03-10
第一种应该效率高点。
回复
lhsxsh 2008-03-10
第一种
回复
-晴天 2008-03-10
回11楼:
索引是不存在什么碎片的,因为它本身就是一种指针.
对表进行更新,或删除加插入,系统都会对盘区中数据页之间进行数据移动,行位置总是会发生变化的.所以操作效率与索引无关.
回复
-晴天 2008-03-10
回8楼:
关系约束中删除子表的问题,只是我们从外部去看.系统内部完全可以在删除-插入两种操作完成之后再进行完整性检查,因为完整性检查本身就是一段程序,系统操作时,完全没有必要在内部操作的过程中插入这段程序来运行一下.
回复
-晴天 2008-03-10
不是试试的问题,在sqlserver内核里,update语句本来就是由delete和insert两个操作来实现的.
还记得instead of触发器中使用deleted表和inserted表吗?那就是为update操作准备的.

如果要进行update操作,系统将进行delete操作和insert操作,涉及到创建insert表,接口数据向insert表传送,操作表中的数据向delete表中的转移,完整性约束检查,insert表中数据向操作表转移,delete表的删除这些步骤.
如果要进行delete操作加上insert操作,接口将传递两次数据
要比较这两组操作的效率,恐怕只能从这两种操作的效率去讨论.

如果要说到11楼涉及的索引的问题,那只能问MS,它们在进行这两组操作的时候,分别是如何进行索引的了.
回复
Plife472 2008-03-10
NO 1
回复
yinqi025 2008-03-10
推荐用第一种,你这样直接物理进行删除会使索引困难,
有些地方还是要用到楼主说的第二种情况
例:
权限模块中的 用户与角色 用名的角色是不固定的个数的,但是还是介意加一个标识列,select的时候加上where条件,
不过还是要看开发中具体情况而定
回复
ivan_ren 2008-03-10
与被update的字段是否是索引列有关系,是,第一种方式效率要搞些,反之则差不多
回复
[Quote=引用 23 楼 kelph 的回复:]
只有对唯一索引的update,就是sql中说的deferred update,才做delete + insert的操作

否则都是direct update ,效率要高于delete + insert
[/Quote]

回复
w2jc 2008-03-10
当然是第一种效率高了。
第二种方法不管怎么说都强制SQL Server去更新相关的索引,而第一种方法则如7楼所说,对索引的影响可大可小。

更新数据量一大,或者使用时间一长之后,
第二种方法会明显导致索引碎片,从而降低速度的。

回复
kelph 2008-03-10
只有对唯一索引的update,就是sql中说的deferred update,才做delete + insert的操作

否则都是direct update ,效率要高于delete + insert
回复
fcuandy 2008-03-10
回楼主:
第一种.
第一种和第二种从索引维护的区别上来看,对于普通索引和非索引列,势必都引起更新, 对于主健如果你都更新,那么说明,你的设计有点问题.所以不考滤更新主健的情况.
第二种,先delete再insert有点脱了裤子..的感觉. 不要被触发器干扰


to 14楼朋友:
索引是不存在什么碎片的,因为它本身就是一种指针.
----------------------------
请问索引以是以什么方式存储的? 它不可能凭空出现, 既然SqlServer的索引以磁盘文件的形式出现,那么不可避免会有碎片产生.

个人看法,仅供参考.
回复
nzperfect 2008-03-10
对qianjin036a 的说法,不敢苟同.
回复
nzperfect 2008-03-10
同意:w2jc
而且update 比 delete insert 产生的日志要小的多.
回复
nzperfect 2008-03-10
第一种.
回复
加载更多回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-09 06:51
社区公告
暂无公告