Re: ouch
Date: Wed, 31 Aug 2011 23:06:32 +0200
Message-ID: <4E5EA258.1000709_at_roughsea.com>
Sure.
SQL> set timing on SQL> variable some_string varchar2(100) SQL> begin
2 :some_string := 'Lazy developers will kill me';
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.16
SQL> declare
2 dummy varchar2(100);
3 begin
4 for i in 1 .. 50000
5 loop
6 dummy := substr(:some_string, 2, 10);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.52
SQL> declare
2 dummy varchar2(100);
3 begin
4 for i in 1 .. 50000
5 loop
6 dummy := substring(:some_string, 2, 10);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.52
Apparently ~ 3 times slower.
Given the obvious lack of mastery of PL/SQL (I have already met many developers who thought that select into from dual was the only way to assign a value to a variable in PL/SQL), I suspect it was some quick and dirty port from a DBMS that has a substring() function, such as MySQL or Sybase/SQL Server. You should teach your developers grep and sed.
-- Stephane Faroult RoughSea Ltd <http://www.roughsea.com> Konagora <http://www.konagora.com> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> On 08/31/2011 10:31 PM, Uzzell, Stephan wrote:Received on Wed Aug 31 2011 - 16:06:32 CDT
>
> 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