代码之家 › 专栏 › 技术社区 › littlegreen

具有快进光标循环的存储过程开始快结束慢

query-optimization cursor sql-server-2008 tsql sql

2

littlegreen · 技术社区 · 15 年前

我注意到的是,前10%的进程加载和处理非常快(2000行/秒),接近20%的进程已经减慢到大约300行/秒,最后已经减慢到大约60行/秒。

光标变慢了,我认为用快进光标不太可能
处理速度减慢。对于我的“组”,我使用的是表变量,在其中插入、更新和删除。在任何给定时刻,这些变量中最多有10行。
邪恶的魔法

要么是那个计数器坏了,要么是我的百分比计数器坏了:

SET @curprogress = @curprogress + 1
IF (@curprogress - ((@totprogress / 100) * (FLOOR(@curprogress * 100 / @totprogress)))) BETWEEN 0 AND 1 BEGIN
    SET @msg = CAST(FLOOR(@curprogress * 100 / @totprogress) AS VARCHAR)
    RAISERROR('%s%s', 0, 1, @msg, '%...') WITH NOWAIT;
END

有没有人知道该找什么,以及如何继续加速这个查询?

我的代码的符号练习:

WHILE....
-- Fetch new record to be assigned to one of the open declaration sets
FETCH NEXT INTO @row_field1, @row_field2....
IF (@flag2 = 1) AND ((@flag1 = 0) OR (@row_field1 <> @prevrow_field1)) 
BEGIN
    -- Logging info: we are closing a child declaration set
    INSERT INTO @logtable SELECT '--> LOG MESSAGE'
    INSERT INTO @logtable
    SELECT format_message(@row_field1, @calc_field2, field3...)
    FROM @runningtable_sub S LEFT JOIN @runningtable_main M ON S.MainID = M.ID

    -- Update enddate of parent
    UPDATE M SET M.enddate = DATEADD(day,365,S.enddate)
    FROM @runningtable_sub S
    LEFT JOIN @runningtable_main M
    ON S.MainID = M.ID

    -- close and save child
    INSERT INTO outputtable_main
    SELECT @field1, COALESCE(Z.Field1,'NULL'), S.startdate, S.enddate,
        M.Startdate, M.Enddate
    FROM @runningtable_sub S 
    LEFT JOIN @runningtable_main M ON S.MainID = M.ID

    -- delete child from running table
    DELETE FROM @runningtable_sub WHERE S.enddate < @curdate
END

1 回复 | 直到 15 年前

1

0

Peter Radocchia 15 年前

我可以想很多原因,这是放缓,但很难缩小它不知道你的数据基数。

随机观察:

更新费用高昂

要自己解决一些问题,请添加工具:

DECLARE @now DATETIME, @duration INT, @rowcount INT

WHILE....
-- Fetch new record to be assigned to one of the open declaration sets
FETCH NEXT INTO @row_field1, @row_field2....
IF (@flag2 = 1) AND ((@flag1 = 0) OR (@row_field1 <> @prevrow_field1)) 
BEGIN
    PRINT '---------------'

    -- Logging info: we are closing a child declaration set
    INSERT INTO @logtable SELECT '--> LOG MESSAGE'
    SET @now = GETDATE()
    INSERT INTO @logtable
    SELECT format_message(@row_field1, @calc_field2, field3...)
    FROM @runningtable_sub S LEFT JOIN @runningtable_main M ON S.MainID = M.ID
    SELECT @rowcount = @@ROWCOUNT, @duration = DATEDIFF(ms,@now,GETDATE)
    RAISERROR('%i row(s) inserted into @logtable, %i milliseconds',-1,-1,@rowcount,@duration) WITH NOWAIT

    -- Update enddate of parent
    SET @now = GETDATE()
    UPDATE M SET M.enddate = DATEADD(day,365,S.enddate)
    FROM @runningtable_sub S
    LEFT JOIN @runningtable_main M
    ON S.MainID = M.ID
    SELECT @rowcount = @@ROWCOUNT, @duration = DATEDIFF(ms,@now,GETDATE)
    RAISERROR('%i row(s) updated in @runningtable_main, %i milliseconds',-1,-1,@rowcount,@duration) WITH NOWAIT

    -- close and save child
    SET @now = GETDATE()
    INSERT INTO outputtable_main
    SELECT @field1, COALESCE(Z.Field1,'NULL'), S.startdate, S.enddate,
        M.Startdate, M.Enddate
    FROM @runningtable_sub S 
    LEFT JOIN @runningtable_main M ON S.MainID = M.ID
    SELECT @rowcount = @@ROWCOUNT, @duration = DATEDIFF(ms,@now,GETDATE)
    RAISERROR('%i row(s) inserted into outputtable_main, %i milliseconds',-1,-1,@rowcount,@duration) WITH NOWAIT

    -- delete child from running table
    SET @now = GETDATE()
    DELETE FROM @runningtable_sub WHERE S.enddate < @curdate
    SELECT @rowcount = @@ROWCOUNT, @duration = DATEDIFF(ms,@now,GETDATE)
    RAISERROR('%i row(s) deleted from @runningtable_sub, %i milliseconds',-1,-1,@rowcount,@duration) WITH NOWAIT
END

如果您包含了整个代码(包括游标声明),而不仅仅是一个符号摘录,这里可能有人会重新编写它,以便您更高效和/或避免一起使用游标。