none
Stored Procedure Comments Performance Hit?

    Question

  • Hi,  I think I remember reading somewhere that excessive comments inside a stored procedure can have a negative impact on performance.  Does anyone know if this is true and can provide an explanation?

    Thank You
    Friday, April 18, 2008 4:33 PM

Answers

  • I believe it has a miniscule impact on performance just because it has to parse more lines of code.  Obviously, it will take a little longer to compile 100 lines of code with than 100 lines of code and  50 lines of comments.  The bottom line it does not affect the procedure enough to avoid commenting code. 

     

    Additionally, once the sp is cached there should be no difference in the query performance because the guts of the query is coming directly from the query plan.

     

    Friday, April 18, 2008 4:53 PM
  • Having comments in code that describe the purpose of the code, and why things are done a specific way, don't impact the performance enough to not comment the code.  However, having debug comments that use a print statement to output debugging information will have a more drastic impact because the procedure is now outputing information that has to be sent across the SQL Connection back to the client.  In my last company, we saw minutes of difference in run times for billing processes when these printed debug comments were turned off, versus when they were turned on during development.  If at all possible, you should keep printed comments back to the client to a minimum.  Also once a procedure is compiled, and cached, the impact of 100's of lines of comments should be minimal, unless you explicitly define WITH RECOMPILE or intermix DML and DDL in the procedure, which will force a recompile of the procedure each time the procedure changes from DML operations to a DDL operation.  This is why it is a best practice to build any temp tables and indexes at the top of the procedure, so it only recompiles one time.

     

    Saturday, April 19, 2008 7:25 PM
  • Adding to what Jonathan said, it is a good practice to start every stored procedure with SET NOCOUNT ON which will instruct SQL Server NOT to output the execution statistics (messages like '10 rows affected', etc etc)

    Sunday, April 20, 2008 5:43 PM

All replies

  • I believe it has a miniscule impact on performance just because it has to parse more lines of code.  Obviously, it will take a little longer to compile 100 lines of code with than 100 lines of code and  50 lines of comments.  The bottom line it does not affect the procedure enough to avoid commenting code. 

     

    Additionally, once the sp is cached there should be no difference in the query performance because the guts of the query is coming directly from the query plan.

     

    Friday, April 18, 2008 4:53 PM
  • Having comments in code that describe the purpose of the code, and why things are done a specific way, don't impact the performance enough to not comment the code.  However, having debug comments that use a print statement to output debugging information will have a more drastic impact because the procedure is now outputing information that has to be sent across the SQL Connection back to the client.  In my last company, we saw minutes of difference in run times for billing processes when these printed debug comments were turned off, versus when they were turned on during development.  If at all possible, you should keep printed comments back to the client to a minimum.  Also once a procedure is compiled, and cached, the impact of 100's of lines of comments should be minimal, unless you explicitly define WITH RECOMPILE or intermix DML and DDL in the procedure, which will force a recompile of the procedure each time the procedure changes from DML operations to a DDL operation.  This is why it is a best practice to build any temp tables and indexes at the top of the procedure, so it only recompiles one time.

     

    Saturday, April 19, 2008 7:25 PM
  • Adding to what Jonathan said, it is a good practice to start every stored procedure with SET NOCOUNT ON which will instruct SQL Server NOT to output the execution statistics (messages like '10 rows affected', etc etc)

    Sunday, April 20, 2008 5:43 PM