Re: ouch

From: Andy Klock <andy_at_oracledepot.com>
Date: Wed, 31 Aug 2011 20:44:21 -0400
Message-ID: <CADo_RaNC_9==YJzF=ruEu=mukD8hy_mNow_FhuMptQK-sbn+rg_at_mail.gmail.com>

I agree that this is just wrong, but it did make me attempt trying running it with a frequently neglected feature, native compilation. I recieved the same results running Tim's test:
set timing on
declare
  v_str varchar2(1000);
begin
  for i in 1..100000 loop
    v_str := substring('This stinks',5,1);   end loop;
end;
  8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.79

• substr declare v_str varchar2(1000); begin for i in 1..100000 loop v_str := substr('This stinks',5,1); end loop; end; 8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

However, if you take out the dual call as previously posted:

CREATE OR REPLACE FUNCTION SUBSTRING2
(INSTRING IN varchar2,
STARTPOS IN number,
LENGTH IN number)
RETURN varchar2
IS
  RESULT varchar2(8000);
BEGIN
 return substr(INSTRING, STARTPOS, LENGTH);  10 END;
 11 /

Function created.

Elapsed: 00:00:00.09
SQL> set timing on
declare
  v_str varchar2(1000);
begin
  for i in 1..100000 loop
    v_str := substring2('This stinks',5,1);   end loop;
end;
  8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

It's much better. And if you natively compile the function:

SQL> alter session set plsql_code_type = 'NATIVE';

Session altered.

Elapsed: 00:00:00.00

SQL> alter function substring2 compile;

Function altered.

Elapsed: 00:00:00.06

SQL> set timing on
declare
  v_str varchar2(1000);
begin
  for i in 1..100000 loop
    v_str := substring2('This stinks',5,1);   end loop;
end;
  8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05

It's better still. Not as dramatic as I had hoped but still worthy of mention. Note that native compilation won't help the original code due to those database calls to dual:

SQL> alter function "SUBSTRING" compile;

Function altered.

Elapsed: 00:00:00.34
declare
  v_str varchar2(1000);
begin
  for i in 1..100000 loop
    v_str := substring('This stinks',5,1);   end loop;
end;
  8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.74

On Wed, Aug 31, 2011 at 5:01 PM, Tim Gorman <tim_at_evdbt.com> wrote:

> Stephan,
>
> If you want to demonstrate the utter wrongness of this, try...
>
> SQL> set timing on
> SQL> declare
> 2 v_str varchar2(1000);
> 3 begin
> 4 for i in 1..100000 loop
> 5 v_str := substring('This stinks',5,1);
> 6 end loop;
> 7 end;
> 8 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:03.34
> SQL>
> SQL> declare
> 2 v_str varchar2(1000);
> 3 begin
> 4 for i in 1..100000 loop
> 5 v_str := substr('This stinks',5,1);
> 6 end loop;
> 7 end;
> 8 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.02
>
> Gosh, after seeing that, I had to go rinse my brain out... blech!
>
> Hope this helps...
>
> -Tim
>
>
> -----Original Message-----
> *From:* Uzzell, Stephan [mailto:SUzzell_at_MICROS.COM]
> *Sent:* Wednesday, August 31, 2011 02:31 PM
> *To:* 'Oracle L'
> *Subject:* ouch
>
> Found a gem in one of my databases today:****
>
> ** **
>
> FUNCTION "SUBSTRING"****
>
> (INSTRING IN varchar2,****
>
> STARTPOS IN number,****
>
> LENGTH IN number)****
>
> RETURN varchar2****
>
> IS****
>
> RESULT varchar2(8000);****
>
> BEGIN****
>
> select substr(INSTRING, STARTPOS, LENGTH)****
>
> into RESULT****
>
> from DUAL;****
>
> RETURN(RESULT);****
>
> END;****
>
> ** **
>
> I?m offended by the sheer aesthetics of this. And I want to go yell at the
> developers that this is wrong. But I?d need a better argument than that it
> offends me. Is there some way to quantify the impact (if any) of wrapping a
> built-in function like this?****
>
> ** **
>
> *Stephan Uzzell*****
>
> ** **
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 31 2011 - 19:44:21 CDT