Software Development

Do comments slow down PL/SQL?


I'm not sure where this started, but at work there's this rumor going around that if you put too many comments in an Oracle PL/SQL package, it impacts performance. That is, the more comments in your code the slower it runs.

I could kind of understand if the language was interpreted because the comments would have to be read in by the interpreter and thus could have an impact on performance. But PL/SQL gets compiled in the database, so I did not see how comments could be a problem.

There's only one way to really tell, of course, and that's to run experiments. So, doing my best MythBusters imitation, I decided to take a crack at it.

I started by creating a table called TABLE1 (sorry, I was lazy and just used the table name that SQLDeveloper gave me). The table had three fields: a varchar to store the name of the method and two timestamp fields for the start and end times. The DDL looked something like this:

create table TABLE1

( methodname VARCHAR2(255 BYTE),

starttime TIMESTAMP (6),

endtime TIMESTAMP (6)

)

Then I created a package with two methods. The basic code in both methods was identical. Here's the code from the version without comments:

procedure comments_n AS

counter integer := 0;

starttime timestamp;

BEGIN

starttime := current_timestamp;

while counter < 9999999 loop

counter := counter + 1;

end loop;

insert into Table1(methodname, starttime, endtime)

values ('comments_n', starttime, current_timestamp);

commit;

END comments_n;

The second method, called comments_y, was identical, except I liberally sprinkled single-line and multi-line comments everywhere. And, yes, I put comments inside the loop since that's where most of the time will be spent in this method.

I then called the methods with a little anonymous block, like this:

begin

pkg_timingtest.comments_n;

pkg_timingtest.comments_y;

end;

The first five times through I called the procedure without comments first; then I edited this anonymous block so that the procedure with comments got called first. I re-ran it another five times. When that was done, I averaged up the time differences and grouped them by the method name:

select

methodname,

avg ((extract(second from (endtime - starttime))) * 1000) as diff

from table1

group by methodname;

I repeated this experiment several times. On some runs, the method with comments would average faster times than the one without. In those cases where the method with comments was slower on average, it was by just two or three milliseconds, which is not a significant percentage when the average runtime was over 2,400 milliseconds.

I consider this myth busted.

18 comments
drvereshjobs
drvereshjobs

It seems true, that some PL/SQL code takes more time. I tried various methods of optimizing huge oracle database(some TBytes) using PL/SQL, but I did not get any fruitful results over huge oracle databse, rather PL/SQL code was taking more time than SQL operations in Java code. I suspect that, does DBMS takes more time in retrieving more records from a various cluster of databases(A single database system over multiple storage/harddisk units)?? But, I could able to reduce some time by moving some db operations in database via PL/SQL code from Java to do: - Batching multiple DML SQL statements - Increasing record fetch bandwidth - Decrease COMMIT hit rate - Single value return(PL/SQL function to return one value by reflecting over a time consuming SELECT statement) In some cases, PL/SQL adds more value than SQL and Java. But sometimes, it may take more turns by improper ways of writing database operations in PL/SQL code. Veeresh D. Software Engineer Bangalore, INDIA http://drveresh.googlepages.com

C_Tharp
C_Tharp

If you don't like to write comments, think that code is hard to write and should be hard to understand, create expertise through obscurity of your work, or have a sinister side, this myth is useful. How inconsiderate of you to bust a useful myth! Quit fighting and join the rumor mill.

jan.de.vos
jan.de.vos

Obviously, you should also have tested cases where the two procedures are in different packages or not in any package, or not called from the same procedure. This all might have an impact! (honestly, I don't believe for a second that those comments will have any influence on performance :-)

noly_big_boy
noly_big_boy

I totally agree with you. I always comment most of my scripts for reference later.

DanLM
DanLM

Even if it did slow it down, which I don't beleive for a second. The value of comments being there far outweighs any perceived performance issues that you might think you have. dan

chun.mail.05
chun.mail.05

Answer depends on how is the source code being 'executed'. It they are interpreted on the fly, each time, then comments would slow down the execution process. However, with compilers, comments are just a few milli-seconds overhead once but not at execution time. My understanding is that PL/SQL code is compiled, therefore the answer is that comments do not slow down execution time.

Justin James
Justin James

You're first thoughts at the very beginning were mine. I am really curious if you asked these people why they would even consider this to be possible, and if so, what reason they gave for it. J.Ja

Joey Indolos
Joey Indolos

This put a knowing smile on my face. Many programmers I know dislike documentation in any form, so I can just hear the cries of "but boss, it will slow down the code!"

chris
chris

I'm working at a University maintaining some in-house developed accounting software written in PL/SQL and I can say I would not care how slow it ran if it was commented (or even documented.

ali.erhan
ali.erhan

PL/SQL code is not always compiled. Therefore comments can effect the performance. The effect of the comments depends on your plsql_code_type database parameter. If it is set to "interpreted", the comments can effect PL/SQL performance.

DCR-Mo
DCR-Mo

I wonder what the results would be like if the code was constucted with dynamic PL/SQL that is executed via the ExecuteImmediate functionality. This type of code is not precompiled, but built and executed "on the fly". I wonder if this might be the context in which the old wives tale was rooted?

RexWorld
RexWorld

Nobody could tell me the origin of this story, it seems to have been lore passed down from previous engineers. It's possible I suppose that older versions of PL/SQL did in fact have some kind of issue with comments in the code. To be fair, there's plenty of similar old wives' tales floating around. Like the one that says you can't build real-world apps out of Java. I still hear that all the time, yet I use JDeveloper daily and it's a terrific app written in Java.

RexWorld
RexWorld

Oh, that's a good thought. Yeah, I should do a follow up using Execute Immediate instead. You could be right. Thanks!

gary.hewett
gary.hewett

>> Like the one that says you can't build real-world apps out of Java. I still hear that all the time, yet I use JDeveloper daily and it's a terrific app written in Java. We have a 100% Java app that is starting to break into the highly competitive courier/parcel/trucking world. Built and maintained with Eclipse and delivered in a suite of Desktop, Web, Mobile and "background agent" components. I've never heard so much as a single comment about it being "different" from the other apps nor have we ever made an excuse about it being Java. If anything the exact opposite is true. We love the fact that we can deploy on Windows AND Apple without changing a single line of code.

Justin James
Justin James

A friend of mine ages ago tried claiming that '67 Camaros had this magic potion called "traction juice", and that "traction juice" squirters located near the rear tires would spray down the tires when needed. I was never quite sure if the squirters were triggered by an action the driver took, or if they were automatic. Obviously, this is total and complete nonsense. After a while, I realized that someone ages ago had started calling the additive you put in locking differentials' (aka PosiTrac) lubricant "traction juice", and someone had heard "traction juice" and "rear" (since the differential on a RWD vehicle is often simply called the "rear") and had wildly misunderstood. However. Building real world apps out of Java is still dicey in many situations. The number one issue is (still) the JVM situation. You can't count on the user having your JVM installed, and enough IT departments balk at the installation of an unknown JVM to be cautious. Another issue, is that the JVM itself is a pig (so is the .Net CLR for that matter) on resource usage. If everyone wrote their apps in Java, but used different JVMs, half our RAM (if not more) would disappear to support having all of those JVMs in memory. At least with .Net, once the pig is loaded, everyone shares it. The problems with Java as a major application language are (still) sadly rooted in issues that are more business and less technical. The language itself is capable, and so are the J2EE and J2SE frameworks, more than enough to build real-world applications. I may also add, nearly a decade of truly horrid Java applications (think: the Oracle installer or Oracle Enterprise Manager) have done more to hurt Java than anything else out there. It will take 10 years to take the taste of some of the Java apps I've used out of my mouth. Thanks to the JVM, everyone is always aware of when they use a Java app, too. In the .Net world, it is hard to tell the difference between a bad .Net app and a bad C++ app. :) J.Ja

Joey Indolos
Joey Indolos

The people who circulated this rumor of comment slowdowns probably had their programming beginnings in MBASIC. Oops! Shouldn't have said that, I'm just giving away my age :)

Justin James
Justin James

Rex - You make a good point about the embedded JRE's. They do make sense... as long as desktop Java never makes significant inroads. As soon as you start getting enough Java desktop apps out there for there to be a good chance of having multiple JRE instances loaded up... well, RAM makers, Intel and AMD would love to see that day. ;) J.Ja

RexWorld
RexWorld

I'm going to disagree with you about the JVM issue. That's what an embedded JRE is for--you can for example get a version of JDeveloper that's bundled with its own JRE. No need to install anything, just explode the ZIP file and double-click the jdeveloper.exe icon. It runs from the embedded JRE just fine. I do agree that this is not ideal for multiple apps-- multiple JRE's running simultaneously would definitely become a resource hog.