数据库效率那个更高

[wangbinwork]

假设我现在有一套主细表 细表内数据量在800-1000万条纪录左右
现在我要修改其中1条纪录 使用以下两种方式


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


请问 那种方式效率更高？？？

[nzperfect]

第一种.

[nzperfect]

同意:w2jc
而且update 比 delete insert 产生的日志要小的多.

[nzperfect]

对qianjin036a 的说法,不敢苟同.

[fcuandy]

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


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

个人看法,仅供参考.

[kelph]

只有对唯一索引的update,就是sql中说的deferred update，才做delete + insert的操作

否则都是direct update ,效率要高于delete + insert

[w2jc]

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

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

[数字化从业者老高]

[Quote=引用 23 楼 kelph 的回复:]
只有对唯一索引的update,就是sql中说的deferred update，才做delete + insert的操作

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

[ivan_ren]

与被update的字段是否是索引列有关系，是，第一种方式效率要搞些，反之则差不多

[yinqi025]

推荐用第一种,你这样直接物理进行删除会使索引困难,
有些地方还是要用到楼主说的第二种情况
例:
权限模块中的 用户与角色 用名的角色是不固定的个数的,但是还是介意加一个标识列,select的时候加上where条件,
不过还是要看开发中具体情况而定

[Plife472]

NO 1

[-晴天]

不是试试的问题,在sqlserver内核里,update语句本来就是由delete和insert两个操作来实现的.
还记得instead of触发器中使用deleted表和inserted表吗?那就是为update操作准备的.

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

如果要说到11楼涉及的索引的问题,那只能问MS,它们在进行这两组操作的时候,分别是如何进行索引的了.

[-晴天]

回8楼:
关系约束中删除子表的问题,只是我们从外部去看.系统内部完全可以在删除-插入两种操作完成之后再进行完整性检查,因为完整性检查本身就是一段程序,系统操作时,完全没有必要在内部操作的过程中插入这段程序来运行一下.

[-晴天]

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

[lhsxsh]

第一种

[parss]

第一种应该效率高点。

[w2jc]

回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

[懒得去死]

[Quote=引用 11 楼 w2jc 的回复:]
当然是第一种效率高了。
第二种方法不管怎么说都强制SQL Server去更新相关的索引，而第一种方法则如7楼所说，对索引的影响可大可小。

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


[/Quote]

同意！

[闪破风浪]

[Quote=引用 12 楼 qianjin036a 的回复:]
不是试试的问题,在sqlserver内核里,update语句本来就是由delete和insert两个操作来实现的.
还记得instead of触发器中使用deleted表和inserted表吗?那就是为update操作准备的.

如果要进行update操作,系统将进行delete操作和insert操作,涉及到创建insert表,接口数据向insert表传送,操作表中的数据向delete表中的转移,完整性约束检查,insert表中数据向操作表转移,delete表的删除这些步骤.
如果要进行delete操作加上insert操作,…
[/Quote]

[jetty_xiang]

12楼分析得很正确

[wangbinwork]

谢谢大家的回复，下面将我的测试信息公布出来。
我的测试环境是
1.分别建立了2个数据库 （防止数据库有缓存问题的存在）
2.在2个数据库中分别建立了1个结构相同的表，字段数是30个。
3.做了一支程序，分别向两个表中插入300万条记录（查完耗时40分钟 晕）

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

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

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

至于楼上朋友所说的日志问题，理论上应该是第二种会高一些没有测试。