patternsqlMinor
Print message comes out in batches
Viewed 0 times
messagebatchesprintcomesout
Problem
I have simple query
When I look at messages, the
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?
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
ENDWhen 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
ENDso 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
ENDCode 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
ENDContext
StackExchange Database Administrators Q#65197, answer score: 4
Revisions (0)
No revisions yet.