Re: ouch

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: SUzzell@xxxxxxxxxx
  • Date: Wed, 31 Aug 2011 23:06:32 +0200

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:

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*


  • Follow-Ups:
  • References:
    • ouch
      • From: Uzzell, Stephan

Other related posts: