Performance Tuning part -7-Print Statement

Do not use the print statement in production (If required, use variable to capture the message for logging). Developers often use print statement to debug the code but these statements must be removed or commented out before deployment.

Print Statement takes extra time to write the client

Demo:

IF EXISTS (Select 1 FROM sys.tables where name='Table1')
DROP TABLE Table1
IF EXISTS (Select 1 FROM sys.tables where name='Table2')
DROP TABLE Table2
IF EXISTS (Select 1 FROM sys.tables where name='Table3')
DROP TABLE Table3
GO
CREATE TABLE Table1 (ID INT)
CREATE TABLE Table2 (ID INT)
CREATE TABLE Table3 (ID INT)
GO
CREATE OR ALTER PROCEDURE PrintTest1
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
DECLARE @ID int=1
DECLARE @MAXID int=1000
WHILE (@ID<=@MAXID)
BEGIN
SET @SQL='The Current Value is: '+CAST(@ID AS VARCHAR)
PRINT @SQL
INSERT INTO Table1 VALUES(@ID)
SET @ID=@ID+1
END
END
GO
CREATE OR ALTER PROCEDURE PrintTest2
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
DECLARE @ID int=1
DECLARE @MAXID int=1000
WHILE (@ID<=@MAXID)
BEGIN
SET @SQL='The Current Value is: '+CAST(@ID AS VARCHAR)
INSERT INTO Table2 VALUES(@ID)
SET @ID=@ID+1
END
END
GO
CREATE OR ALTER PROCEDURE PrintTest3
AS
BEGIN
DECLARE @ID int=1
DECLARE @MAXID int=1000
WHILE (@ID<=@MAXID)
BEGIN
INSERT INTO Table3 VALUES(@ID)
SET @ID=@ID+1
END
END
GO

There are three procedures in above script and all of them are using almost same work. All the procedures are inserting values 1 to 1000 in similar tables using the same insert command.

  1. First procedure is capturing the current value in a variable and printing the message.
  2. Second procedure is capturing the current value in a variable but not printing.
  3. Third procedure is not even capturing the value in the variable.

Now let’s execute the procedures and run the profiler:

 

As you can see from profiler data, procedure with print statement has taken maximum time to complete. second procedure has taken less time than first as it is just capturing the current value in a variable but not printing. Third procedure has taken least time to complete as it is not even capturing the value in variable.

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.