Comments in a Stored Procedure

[bowl3y]

Has anyone ever heard of a stored procedure having too many comments? While investigating the slowness of a SP I discovered that if i removed everything but the code the procedure went from 10 seconds to 2??? any ideas?

the comments were about 70 lines long the entire sp was less than 100... after removing the comments the code was 27 lines long...

Bowley

 

[gmmastros]

I have never heard that comments would affect the execution time of a procedure.

When stored procedures are created, SQL Server creates an execution plan for it. This execution plan is stored in the database. Whenever you run the stored procedure, SQL Server will use the stored execution plan instead of re-creating it. This saves you time, making the procedure faster to run.

If I had to guess, I would say that the execution plan SQL Server generated for your stored procedure became 'less than optimal'. I'm not really sure why this happens. By removing the comments (are re-creating the stored procedure), you caused SQL Server to re-calculate an execution plan (which is probably better than the original one).

I'd be willing to bet that if you added the comments back in, the procedure would still execute in 2 seconds (instead of 10).

Also... If you'd like, you can post the entire procedure so we can take a look at it. There may be other ways to improve the performance.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RobertT687]

I'd like to add a question along these same lines.
What action short of recompiling the procedure should be taken to make sure procedures are using more current execution plans?
Is updating statistics sufficient?

Thanks in advance.