执行delete语句怎么显示有两个耗时巨大的OrderBy计划？

[wufanglu]

环境：sql2000
执行语句：delete from ps_ReadCard where RCDate<='2005.05.31' --影响 1,205,310 行(耗时 7分41秒)
ps_ReadCard表(打卡记录表) 主键(聚cu4索引)：打卡日期, 打卡时间, 卡号
另有个索引: 打卡日期, 卡号

不知怎么发图片，查询分析器中，执行计划大概如下(从后往前写，"===="是显示的两行同步执行的)：
02% ps_ReadCard.PK_ps_ReadCard (SEEK: 打卡日期<'2005.05.31')
00% Top
00% Table Delete/Delete
05% Table Spool/Eager Spool ==== 08% Table Spool/Eager Spool
44% Sort(ORDER BY: 打卡日期, 打卡时间, 卡号) ==== 40% Sort (ORDER BY: 打卡日期，卡号, Bmk1000) --没Bmk1000这个字段，可能是sql执行过程中自动产生的
00% Index Delete/Delete ==== 00% Index Delete/Delete
01% Sequence Cost
00% DELETE

发现执行计划中出现了两个Sort，且这两个Sort就占了 84%(44%+40%)。

后面加了个索引仅包括打卡日期，执行计划最后面的改为了:
01% ps_ReadCard.IX_ps_ReadCard_DateOnly
但中间仍然显示有 3个28%=84% 的Sort：
28% Sort(ORDER BY: 打卡日期, 打卡时间, 卡号) ==== 28% Sort(ORDER BY: 打卡日期，卡号, Bmk1000) ==== 28% Sort (ORDER BY: 打卡日期, Bmk1000)


看其执行过程好像是有几个索引就会产生几个大OrderBy操作，
真正费时的不是在删表记录，而是删索引时产生的排序操作。
如何使其不产生排序？

[feixianxxx]

delete from ps_ReadCard where RCDate<='2005.05.31' --影响 1,205,310 行(耗时 7分41秒)

一百多万的数据一次删除 耗时肯定很长 而且是有条件筛选的删除

你的字段打卡日期上的索引在删选时候起到了作用 但是同时DELETE操作也带来了索引的维护...

所以也有这个说法：经常进行INSERT UPDATE DELETE操作的数据表上不适合建立太多索引。。

现在的处理方法就是1L说的方法 或者分批删除

[obuntu]

100多万数据的删除，还想多快呀。

[wufanglu]

这样啊，嗯，新增与删除动作与索引在效率上是有冲突的。

那干cui4就:
1. 删触发器(早就删了) --0秒钟
2. 把ps_readcard表重命名为ps_readcard_100802_00 --0秒钟
3. select * into ps_readcard from ps_readcard_100802_00 where RCDate>='2008.01.01' --三分钟
4. 主键+索引+触发器 之 --十几分钟

[feixianxxx]

[Quote=引用 4 楼 wufanglu 的回复:]

这样啊，嗯，新增与删除动作与索引在效率上是有冲突的。

那干cui4就:
1. 删触发器(早就删了) --0秒钟
2. 把ps_readcard表重命名为ps_readcard_100802_00 --0秒钟
3. select * into ps_readcard from ps_readcard_100802_00 where RCDate>='2008.01.01' --三分钟
……
[/Quote]

这样的转移方法要看合适不合适的..
如果你的表删除后的数据不算太多 那你可以选择导入新表 因为select into 操作是快的
其次 你原表上的索引触发器之类都要重建。。
这个时候其实也是要看你现在表的数据多不多

如果很多 那建立的时间你也看到了 比你删除花的多 得不偿失~

[wufanglu]

实际比预计快多了，现只保留2009年含后的数据，陈年数据太多了，想批次调整数据真是浪费生命。

之前是直接 delete ps_ReadCard where RCDate<='2008.12.31'，花了一个多小时，出来的结果是：error，数据库Log已满、tempdb的Log也满(即硬盘空间都塞满了)。
分段删时发现时间都花在整理索引上。

之前用 select top 0 * into ps_ReadCard_100728_00 from ps_ReadCard 加 insert into ps_ReadCard_100728_00 select * from ps_ReadCard where RCDate>='2009.01.01'要很久，
即 insert into from 格式很慢，直接用 select into from 非常快。

--实际耗时：
select * into ps_ReadCard from ps_ReadCard_100728_All where RCDate>='2009.01.01' --影响497,3903行，耗时1分33秒
--建主键: 2分40多秒 (日期，时间，卡号)
--建索引: 1分10多秒 (日期，工号)
--设默认值: 0秒
--加触发器: 0秒

[feixianxxx]

[Quote=引用 6 楼 wufanglu 的回复:]

SQL code
实际比预计快多了，现只保留2009年含后的数据，陈年数据太多了，想批次调整数据真是浪费生命。

之前是直接 delete ps_ReadCard where RCDate<='2008.12.31'，花了一个多小时，出来的结果是：error，数据库Log已满、tempdb的Log也满(即硬盘空间都塞满了)。
分段删时发现时间都花在整理索引上。

之前用 select top ……
[/Quote]
那是因为select into from 不是完整写入日志的 当然飞快

[asus09345]

我删除50万行数据用了3个小时，还是牛逼的硬件配置。
开始还想先删掉索引再处理。

[wufanglu]

删了索引再处理也会很慢，想想在没索引的情况下用where条件，而且还是超大的表，这我想都没想。

[永生天地]

lz的办法很不错，值得推荐

[wufanglu]

异想天开，要是有这样的环境变量就好了：

set index_update off
delete from 打卡记录表 where 打卡日期<='2008.12.31'
drop index
create index --建索引的功能还没用过哩，都是在管理工具中操作的
set index_update on

当然这样很容易出乱子，就像 disable trigger 功能一样，忘了恢复会S得很惨。

[lfzwenzhu]

不要一次性删除所有数据

[wufanglu]

>>SQL Server 2005 是可以禁用索引的（alter index ... disable），保存索引的定义（元数据），阻止使用索引和更新索引。启用索引需要通过重建索引（alter index ... rebuild）实现。

这样和 删了再建 有什区别？

[Johnson]

主键也是聚集索引：打卡日期, 打卡时间, 卡号
因为是联合索引，所以在删除前需要使用这三列排序查找（查找是优先使用了聚集索引）需要删除的数据，会导致性能降低

建议：使用Rowid之类的做为聚集索引，删除联合索引
　　　或者强制使用打卡日期建立的索引进行该删除操作

[Johnson]

还有，这个表上的索引是不是有点多啦

[Ben198651]

学习了

[xman_78tom]

[Quote=引用 14 楼 wufanglu 的回复:]

>>SQL Server 2005 是可以禁用索引的（alter index ... disable），保存索引的定义（元数据），阻止使用索引和更新索引。启用索引需要通过重建索引（alter index ... rebuild）实现。

这样和 删了再建 有什区别？
[/Quote]
索引的定义会被保留，不需要重新写 create index 语句。重要的是，不需要重新定义那些索引选项（如 pad_index、fillfactor 等等）。

[obuntu]

select into 会 minimal logging吗？

谁给个例子？

[xman_78tom]

SQL Server 2005 是可以禁用索引的（alter index ... disable），保存索引的定义（元数据），阻止使用索引和更新索引。启用索引需要通过重建索引（alter index ... rebuild）实现。

[wufanglu]

[Quote=引用 15 楼 yongsheng0550 的回复:]主键也是聚集索引：打卡日期, 打卡时间, 卡号
因为是联合索引，所以在删除前需要使用这三列排序查找（查找是优先使用了聚集索引）需要删除的数据，会导致性能降低

建议：使用Rowid之类的做为聚集索引，删除联合索引
或者强制使用打卡日期建立的索引进行该删除操作

还有，这个表上的索引是不是有点多啦[/Quote]
你说的索引太多了，就是因为我想用联合索引。
我觉得单索引(RowID)对于查询来讲没任何意义，仅方便多表join时少输几个字。
表中有两个索引，是根据不同需要而建的，有时要查[卡号+日期]，有时要查[员工ID+日期]。

[Johnson]

"表中有两个索引，是根据不同需要而建的，有时要查[卡号+日期]，有时要查[员工ID+日期]。"

如果是这样的话，我觉得分别在卡号、员工ID、日期上建立三个索引比较好，联合索引性能不好。