Re: ouch

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@xxxxxxxxx> 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@xxxxxxxxxx]
> *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

Other related posts: