Menu
I am using SQL Server procedures and I have a habit of using of Print statements in the stored procedures to differentiate the code of procedure.
I have almost 200-250 procedures in my DB. Should print statement affect the performance? I am working on multi-user Windows application.
869
A handful of PRINT statements will have a negligible effect on performance - PRINT s in loops that are executed many thousands of times, however, may well cause performance issues.
Table size: If your query hits one or more tables with millions of rows or more, it could affect performance. Joins: If your query joins two tables in a way that substantially increases the row count of the result set, your query is likely to be slow. There's an example of this in the subqueries lesson.
Stored procedures can't "show messages". Then can return either an OUTPUT parameter OR a record set. It's up to your application to determine what to do with the results. That said, you can "print" a message which is similar to simply selecting some text: see stackoverflow.com/questions/6912102/…
I found when running the below on my desktop that commenting out the print knocked about 15 seconds off the execution time meaning the average impact was 15µs in my simple test. RAISERROR WITH NOWAIT added an average of just over double that.
DECLARE @date DATETIME2
DECLARE
@count INT
SET @count = 1
SET @date = SYSUTCDATETIME()
WHILE @count < 1000000
BEGIN
--RAISERROR ('%d',0,1, @count) WITH NOWAIT
--PRINT @count
SET @count = @count + 1
END
SELECT DATEDIFF(MICROSECOND, @date, SYSUTCDATETIME()) / 1000000.
A handful of PRINT statements will have a negligible effect on performance - PRINTs in loops that are executed many thousands of times, however, may well cause performance issues.
It's unlikely that if you're experiencing performance problems with your queries that PRINT is the culprit - however, if in doubt, try some experiments!
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
