-- tb表中有字段 tr1,tr2,tr,ID;其中有数据约为40W条,但是既然花了8分钟也没有跑完下面的这段代码
-- 实在不知道怎么回事,请大家帮看看,SET NOCOUNT ON
DECLARE @tr VARCHAR(20),@iTotal BIGINT,@id BIGINT;
SET @iTotal = 0;
SET @id = 0;WHILE (EXISTS(SELECT top 1 ID FROM tb))
BEGIN SELECT TOP 1 @tr=tr,@id=ID
FROM tb IF (@tr='001')
BEGIN
SET @iTotal = @iTotal + 1;
END DELETE FROM tb WHERE ID=@idEND;
SET NOCOUNT OFFPRINT 'Total: '+ CONVERT(varchar(20),@iTotal)
GO
-- 写成游标模式,11秒就运行完毕了!!!
DECLARE @tr VARCHAR(20),@iTotal BIGINTSET @iTotal = 0;DECLARE Test_cursor CURSOR
FOR SELECT tr FROM tb OPEN Test_cursor
FETCH NEXT FROM Test_cursor
INTO @trIF (@tr='001')
BEGIN
SET @iTotal = @iTotal + 1;
ENDWHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM Test_cursor
INTO @tr IF (@tr='001')
BEGIN
SET @iTotal = @iTotal + 1;
END
END
PRINT 'Total : '+ CONVERT(varchar(20),@iTotal)CLOSE Test_cursor
DEALLOCATE Test_cursor
GO
select @cnt=count(1) from tb where tr='001';
delete from tb;
print 'Total: '+ltrim(@cnt);
LZ 的比较方法本身就有问题,因为完全没有体现 SQL 语句的特点和优势。如果想要了解游标这方面的内容,建议 LZ 看看
Inside Microsoft SQL Server 2005: T-SQL Programming
Chapter 3: Cursors