HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Print message comes out in batches

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
messagebatchesprintcomesout

Problem

I have simple query

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


When I look at messages, the PRINT command does not return row every loop but shows up as multiple rows at ones. I have added one line and now it shows up 1 line at the time and the the whole process is much faster.

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


so why is it when I have added .5 second wait time to each loop iteration it actually runs faster and allows the print message to come out one at the time?

Solution

PRINT output is buffered. If you replace PRINT ... with RAISERROR ... WITH NOWAIT you will see results as they happen.

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

Code Snippets

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

Context

StackExchange Database Administrators Q#65197, answer score: 4

Revisions (0)

No revisions yet.