主页 / dba / 问题 / 65197
Accepted
Vladimir Oselsky
Asked: 2014-05-16 08:09:44 +0800 CST

打印信息分批出来

  • 772

我有简单的查询

WHILE ( @counter < 50 )
    BEGIN
        BEGIN TRANSACTION
        PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter);
        WITH    CTE
                  AS (
                       SELECT TOP 50000 * FROM MyTable
                     )
            DELETE FROM CTE OPTION ( MAXDOP 1 )
        SET @counter += 1
        COMMIT TRAN
    END

当我查看消息时，该PRINT命令不会在每个循环中都返回行，而是显示为多行。我已经添加了一行，现在它一次显示了 1 行，整个过程要快得多。

WHILE ( @counter < 50 )
    BEGIN
        WAITFOR DELAY '00:00:00.5' --< This line
        BEGIN TRANSACTION
        PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter);
        WITH    CTE
                  AS (
                       SELECT TOP 50000 * FROM MyTable
                     )
            DELETE FROM CTE OPTION ( MAXDOP 1 )
        SET @counter += 1
        COMMIT TRAN
    END

那么为什么当我为每个循环迭代添加 0.5 秒的等待时间时，它实际上运行得更快，并允许打印消息同时出现？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    PRINT输出被缓冲。如果您替换PRINT ...为，RAISERROR ... WITH NOWAIT您将看到结果。

    DECLARE @msg VARCHAR(MAX);
DECLARE @counter INT;
WHILE (@counter < 50)
BEGIN
    BEGIN TRANSACTION
    SET @msg = 'Counter = ' + CONVERT(VARCHAR(4), @counter);
    RAISERROR (@msg, 0, 1) WITH NOWAIT;
    ;WITH CTE
    AS (
        SELECT TOP(50000) * 
        FROM MySchema.MyTable
        )
    DELETE FROM CTE OPTION (MAXDOP 1);
    SET @counter += 1;
    COMMIT TRAN
END
    • 4

相关问题