Developer

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

Editor's Picks