RSS Feed

Method R Discussion Group

** The following note was sent to us and posted here with permission of the sender.  **

Dear Hotsos team,

I want to place some comments on the article by Karen Morton:

While I very much agree with validating and discarding old coding habits I find the choosen example extremely unlucky.

If I have a select returning a single value, I would very much expect this select to succeed and very seldom or never to fail.

Recoding the given example with this target in mind, you get a totally different picture:

function fx1 is getting faster with less misses while function fx2 is deteriorating with less misses:

VDC1M@TST> @tst/xt2
fx1 0% hits

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.15
fx1 10% hits

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.79
fx1 33% hits

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.18
fx1 50% hits

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.53
fx1 100% hits

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.77
fx2 0% hits

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.30
fx2 10% hits

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.27
fx2 33% hits

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.72
fx2 50% hits

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.90
fx2 100% hits

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.44

I did not do a detailed breakdown of spent time, but the general execution times are more or less consistent over multiple tests.

Test setup:

Oracle EE 10.2.0.1

HP 9000/800/N4000-55 
HP-UX B.11.11 
CPUs: 8 Memory: 16384 MB

The fastest execution is consistently for fx1 with no misses due to the very reasons stated in the article,
it's just less work NOT to set up a cursor and NOT having to do stack unwinding in exception handling.

Thanks so much for your comments and excellent counter-point to the test used in the article.  You perfectly supported the next to the last sentence in the article ("Just because one technique worked best in one situation doesn't mean it will work best in all situations.") with your example.

The intent of the article was to say that we should make sure to use coding techniques appropriate to the situation we find ourselves in.  In the case tested in the article with each SELECT failing with a NO_DATA_FOUND, then the choice to code without an exception handler is more efficient.  And, as you pointed out in your test where you expect the SELECT to succeed more often that fail, then the exception handler option is the best.

So, in the end, I think we both agree that the bottom-line is that we should develop code according to the expectations/behaviors of our application and not simply always do the same thing by rote.  The key is to test and prove which technique works best for your needs and not get trapped into the habitual use of a particular technique that may be under-performing in some situations.

Thanks for taking the time to review the article and respond!

For those who'd like to read the original article that prompted Werner's response, the full text can be accessed in our Newsletter archives at https://portal.hotsos.com/education/hotsos-newsletters/2007-newsletters/2007-issue14.

Here are the test scripts Werner used in his response:

xt.sql
create table t
(
    k number,
    n number
)
/
insert into t values (1,1)
/
commit
/
create or replace function fx1
(p number)
return number
is
    x number;
begin
    begin
        select n into x from t where k=p;
        return x;
    exception
        when no_data_found then return 0;
    end;

end fx1;
/
create or replace function fx2
(p number)
return number
is
    x number;
begin
    for row in (select n from t where k=p) loop
        return row.n;
    end loop;
    return 0;
end fx2;
/



xt2.sql
set timing on

prompt fx1 0% hits
declare
    x    number;
begin
    for i in 1 .. 100000 loop
        x := fx1(mod(i,10)+2);
    end loop;
end;
/
prompt fx1 10% hits
declare
    x    number;
begin
    for i in 1 .. 100000 loop
        x := fx1(mod(i,10)+1);
    end loop;
end;
/
prompt fx1 33% hits
declare
    x    number;
begin
    for i in 1 .. 100000 loop
        x := fx1(mod(i,3)+1);
    end loop;
end;
/
prompt fx1 50% hits
declare
    x    number;
begin
    for i in 1 .. 100000 loop
        x := fx1(mod(i,2)+1);
    end loop;
end;
/
prompt fx1 100% hits
declare
    x    number;
begin
    for i in 1 .. 100000 loop
        x := fx1(mod(i,1)+1);
    end loop;
end;
/
prompt fx2 0% hits
declare
    x    number;
begin
    for i in 1 .. 100000 loop
        x := fx2(mod(i,10)+2);
    end loop;
end;
/
prompt fx2 10% hits
declare
    x    number;
begin
    for i in 1 .. 100000 loop
        x := fx2(mod(i,10)+1);
    end loop;
end;
/
prompt fx2 33% hits
declare
    x    number;
begin
    for i in 1 .. 100000 loop
        x := fx2(mod(i,3)+1);
    end loop;
end;
/
prompt fx2 50% hits
declare
    x    number;
begin
    for i in 1 .. 100000 loop
        x := fx2(mod(i,2)+1);
    end loop;
end;
/
prompt fx2 100% hits
declare
    x    number;
begin
    for i in 1 .. 100000 loop
        x := fx2(mod(i,1)+1);
    end loop;
end;
/