1. 按sdhdy(大江东去...) 的方法先设置 2. 对URL建索引。 3. 如果还不行,限制一次性删除的行数。 set @@rowcount=100 declare @i int set @i=1 where @i>0 begin DELETE FROM TABLE4 WHERE (URL LIKE 'http://archpsyc.ama-assn.org%') OR (URL LIKE 'http://archfami.ama-assn.org%') OR (URL LIKE 'http://archinte.ama-assn.org% ') OR (URL LIKE 'http://archneur.ama-assn.org%') OR (URL LIKE 'http://archopht.ama-assn.org%') OR (URL LIKE 'http://archotol.ama-assn.org%') OR (URL LIKE 'http://archpedi.ama-assn.org%') OR (URL LIKE 'http://www.annfammed.org%') set @i=@@rowcount end
1:企业管理器->服务器->属性->连接-> 将查询超时该为0或一个较大的数 2: 为什么要用游标?如下即可 delete table4 where (URL LIKE 'http://archpsyc.ama-assn.org%') OR (URL LIKE 'http://archfami.ama-assn.org%') OR (URL LIKE 'http://archinte.ama-assn.org% ') OR (URL LIKE 'http://archneur.ama-assn.org%') OR (URL LIKE 'http://archopht.ama-assn.org%') OR (URL LIKE 'http://archotol.ama-assn.org%') OR (URL LIKE 'http://archpedi.ama-assn.org%') OR (URL LIKE 'http://www.annfammed.org%') GO
数据库连接的commandtimeout的值改为0删除的时候用语句系列,逐步运行: delete table4 where URL LIKE 'http://archpsyc.ama-assn.org%' delete table4 where (URL LIKE 'http://archfami.ama-assn.org%') delete table4 where (URL LIKE 'http://archinte.ama-assn.org% ') delete table4 where (URL LIKE 'http://archneur.ama-assn.org%') delete table4 where (URL LIKE 'http://archopht.ama-assn.org%') delete table4 where (URL LIKE 'http://archotol.ama-assn.org%') delete table4 where (URL LIKE 'http://archpedi.ama-assn.org%') delete table4 where (URL LIKE 'http://www.annfammed.org%')
建议用这个: TRUNCATE TABLE 表名 我试过,超快。
设置DNS查询的超时时间也可以
在一个事务中完成会比较快。 set nocount on begin tran delete table4 where URL LIKE 'http://archpsyc.ama-assn.org%' delete table4 where (URL LIKE 'http://archfami.ama-assn.org%') delete table4 where (URL LIKE 'http://archinte.ama-assn.org% ') delete table4 where (URL LIKE 'http://archneur.ama-assn.org%') delete table4 where (URL LIKE 'http://archopht.ama-assn.org%') delete table4 where (URL LIKE 'http://archotol.ama-assn.org%') delete table4 where (URL LIKE 'http://archpedi.ama-assn.org%') delete table4 where (URL LIKE 'http://www.annfammed.org%') commit tran
2. 对URL建索引。
3. 如果还不行,限制一次性删除的行数。
set @@rowcount=100
declare @i int
set @i=1
where @i>0
begin
DELETE FROM TABLE4
WHERE (URL LIKE 'http://archpsyc.ama-assn.org%') OR
(URL LIKE 'http://archfami.ama-assn.org%') OR
(URL LIKE 'http://archinte.ama-assn.org% ') OR
(URL LIKE 'http://archneur.ama-assn.org%') OR
(URL LIKE 'http://archopht.ama-assn.org%') OR
(URL LIKE 'http://archotol.ama-assn.org%') OR
(URL LIKE 'http://archpedi.ama-assn.org%') OR
(URL LIKE 'http://www.annfammed.org%')
set @i=@@rowcount
end
2:
为什么要用游标?如下即可
delete table4
where (URL LIKE 'http://archpsyc.ama-assn.org%') OR
(URL LIKE 'http://archfami.ama-assn.org%') OR
(URL LIKE 'http://archinte.ama-assn.org% ') OR
(URL LIKE 'http://archneur.ama-assn.org%') OR
(URL LIKE 'http://archopht.ama-assn.org%') OR
(URL LIKE 'http://archotol.ama-assn.org%') OR
(URL LIKE 'http://archpedi.ama-assn.org%') OR
(URL LIKE 'http://www.annfammed.org%')
GO
delete table4
where URL LIKE 'http://archpsyc.ama-assn.org%'
delete table4
where (URL LIKE 'http://archfami.ama-assn.org%')
delete table4
where (URL LIKE 'http://archinte.ama-assn.org% ')
delete table4
where (URL LIKE 'http://archneur.ama-assn.org%')
delete table4
where (URL LIKE 'http://archopht.ama-assn.org%')
delete table4
where (URL LIKE 'http://archotol.ama-assn.org%')
delete table4
where (URL LIKE 'http://archpedi.ama-assn.org%')
delete table4
where (URL LIKE 'http://www.annfammed.org%')
TRUNCATE TABLE 表名
我试过,超快。
set nocount on
begin tran
delete table4
where URL LIKE 'http://archpsyc.ama-assn.org%'
delete table4
where (URL LIKE 'http://archfami.ama-assn.org%')
delete table4
where (URL LIKE 'http://archinte.ama-assn.org% ')
delete table4
where (URL LIKE 'http://archneur.ama-assn.org%')
delete table4
where (URL LIKE 'http://archopht.ama-assn.org%')
delete table4
where (URL LIKE 'http://archotol.ama-assn.org%')
delete table4
where (URL LIKE 'http://archpedi.ama-assn.org%')
delete table4
where (URL LIKE 'http://www.annfammed.org%')
commit tran