July 30, 2012 at 9:46 am
I have a stored procedure that has a lot of comments. There are actual comments, and old blocks of code that have been commented out. The procedure is more than twice as long because of these comments.
I understand that sql server doesn't execute commented lines, but those lines have to be stored somewhere, don't they? Should I get rid of these comments? I could simple save the text of the procedure elsewhere, but my real concern is performance.
Can too many comments in a SQL Server 2000 stored procedure slow down that procedure?
July 30, 2012 at 9:54 am
No, definitely not.
Only the active code gets passed to the optimiser, so the comments don't go anywhere.
If it's slow it's something else, usually a join or the where clause.
Too much old code can clutter a proc though, from the point of view of reading it. You could delete it if it's not wanted any more, or perhaps move it all to the end.
Relevant comments & explanations are a GOOD thing 🙂
July 30, 2012 at 9:58 am
Are you suggesting there is no point where too many comments can slow it down, or that the point is so large its impractical to consider? What if the stored proc was 1 million lines, with only 1% of that actually functioning code?
July 30, 2012 at 10:00 am
laurie-789651 (7/30/2012)
Relevant comments & explanations are a GOOD thing 🙂
I do understand this point. I guess I'm wondering about the wisdom of leaving a lot of old code there in comment form.
July 30, 2012 at 10:07 am
Within reason, you can put as much comments code as you like.
Personally, I don't like too much old code. I'd keep it if a) I thought it might be reused, or b) I thought it helped explain a change e.g. "This looks good but it didn't work because.....'
I don't keep all old code because it just clutters the proc & often adds nothing to understanding it. If you're using source control on your procs, that's another good reason for not keeping it.
If it's definitely not likely to be wanted again, I usually get rid of it; or if I keep it, I usually get rid of it later.
Don't forget, comment code doesn't even get to the optimiser, so there's no way it can slow down execution of a proc. The processes for handling stored procedure text are very powerful & optimised. Having said that, if you wanted a hundred thousand lines of comments, you probably need a separate document as it would make it hard to find the code. 🙂
July 30, 2012 at 10:11 am
maw74656 (7/30/2012)
Are you suggesting there is no point where too many comments can slow it down, or that the point is so large its impractical to consider? What if the stored proc was 1 million lines, with only 1% of that actually functioning code?
only the code that gets interpreted into commands gets compiled. comments have no effect at all.
there's been multiple threads on this same subject over the years; we built various test harnesses that used REPLICATE TO put a ton of fake comments into the body of a stored proc, let me see if i can find it again, but there no impact.
see this post for example, prove it to yourself:
http://qa.sqlservercentral.com/Forums/Topic369476-360-1.aspx
Lowell
July 30, 2012 at 10:14 am
maw74656 (7/30/2012)
Are you suggesting there is no point where too many comments can slow it down, or that the point is so large its impractical to consider? What if the stored proc was 1 million lines, with only 1% of that actually functioning code?
There might be a point where is slows down the compile of the procedure, but once it is compiled into an execution plan there will be no difference.
I think the example of a million lines of code with 99% comments is a bit of a strawman. I have written a lot of stored procedures and I comment liberally, but a procedure over 2,000 lines is fairly rare, and most are much shorter.
July 30, 2012 at 12:42 pm
Michael Valentine Jones (7/30/2012)
maw74656 (7/30/2012)
Are you suggesting there is no point where too many comments can slow it down, or that the point is so large its impractical to consider? What if the stored proc was 1 million lines, with only 1% of that actually functioning code?There might be a point where is slows down the compile of the procedure, but once it is compiled into an execution plan there will be no difference.
I think the example of a million lines of code with 99% comments is a bit of a strawman. I have written a lot of stored procedures and I comment liberally, but a procedure over 2,000 lines is fairly rare, and most are much shorter.
I just finished up at a place where a procedure with less than 2000 lines of code was rare.
😀
That said, once compiled it should not add too much to the compile time. I wonder if there is an eventual residual effect to how many procs can be stored in cache due to extreme cases of 99% comments in code.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2012 at 1:57 pm
SQLRNNR (7/30/2012)
Michael Valentine Jones (7/30/2012)
maw74656 (7/30/2012)
Are you suggesting there is no point where too many comments can slow it down, or that the point is so large its impractical to consider? What if the stored proc was 1 million lines, with only 1% of that actually functioning code?There might be a point where is slows down the compile of the procedure, but once it is compiled into an execution plan there will be no difference.
I think the example of a million lines of code with 99% comments is a bit of a strawman. I have written a lot of stored procedures and I comment liberally, but a procedure over 2,000 lines is fairly rare, and most are much shorter.
I just finished up at a place where a procedure with less than 2000 lines of code was rare.
😀
That said, once compiled it should not add too much to the compile time. I wonder if there is an eventual residual effect to how many procs can be stored in cache due to extreme cases of 99% comments in code.
I don't think the comments contribute to the plan cache at all.
A far worse problem for the plan cache is when each query is dynamically generated by front end code and the plan cache ends up filled with thousands or hundereds of thousands of plans that are only executed once.
July 30, 2012 at 3:46 pm
Just for references - I just tested out a fairly extreme case.
if exists (select object_id from sys.procedures where name='MattnoCmt')
drop procedure MattnoCmt
if exists (select object_id from sys.procedures where name='MattCmt')
drop procedure MattCmt
go
dbcc freeproccache
dbcc dropcleanbuffers
go
create proc MattnoCmt
as
BEGIN
select AVG(rn) b, COUNT(rn) j from tallyplus
end
--comments to repeat
/*aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb*/
I will spare you the MattCmt script, but suffice it to say it's the same code as mattNoCmt, with 100K lines of comments. I was going to go for 1M, but the SSMS editor seemed to choke on anything much larger than 120K lines.
I then ran this:
set statistics time on
print 'cmt'
exec mattcmt
print 'nocmt'
exec mattnocmt
set statistics time off
and the statistics look something like:
cmt
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 72 ms, elapsed time = 72 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 78 ms.
nocmt
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
any further executions then yielded same exec times etc...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 14, 2012 at 12:19 pm
EDIT: removed, posted in wrong thread.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply