请各位sql大虾指点迷径
blp 2002-10-10 12:53:47 DROP TABLE TestCursor
CREATE TABLE TestCursor(
Id int NOT NULL,
Amount int Primary key (id))
DECLARE @id int
set @id = 1
while @id<10000
begin
insert testcursor values (@id,@id)
set @id = @id +1
end
/*遍历更新*/
DECLARE test CURSOR FOR SELECT * FROM TestCursor FOR UPDATE
OPEN test
Fetch Next From test
WHILE @@FETCH_STATUS = 0
BEGIN
Update TestCursor set Amount = Amount * 10 WHERE CURRENT OF test
IF @@FETCH_STATUS =0
BEGIN
Fetch Next From test
END
END
close test
DEALLOCATE test
遍历更新一遍需要大约需要13秒,即使去除更新的这一句,也需要大约9秒
可是用直接用Update TestCursor set Amount = Amount * 10 是<1s,用游标怎么会反而这么慢。现在有个存储过程,需要用到类是上面的遍历更新。这个速度是太慢,是不是我的写法有问题,请各位sql高手帮忙