Declarations, Blocks, Functions and Procedures in Loops

The declaration of types, variables and cursors within loops is inefficient as all memory management is performed once for each iteration of the loop.  If these declarations are placed outside the loop the associated memory management is only performed once for the entire loop.  The declare_in_loop.sql script compares an internal and external variable declaration.

declare_in_loop.sql

SET SERVEROUTPUT ON
DECLARE
  l_loops   NUMBER := 1000000;
  l_start   NUMBER;
  l_number  NUMBER;
BEGIN
  -- Time internal declaration.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    DECLARE
      l_number  NUMBER;
    BEGIN
      l_number := i;
    END;
  END LOOP;

  DBMS_OUTPUT.put_line('Internal declaration: ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time external declaration.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    BEGIN
      l_number := i;
    END;
  END LOOP;

  DBMS_OUTPUT.put_line('External declaration: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

The output from this script shows that the effect of even a single declaration is measurable.

SQL> @declare_in_loop.sql
Internal declaration: 24
External declaration: 19

PL/SQL procedure successfully completed.

Whilst discussing this issue with a colleague I was asked if placing an anonymous block with no declarations inside a loop was a performance problem. The block_in_loop.sql script is a variation on the previous script which provides an answer this question.

block_in_loop.sql

SET SERVEROUTPUT ON
DECLARE
  l_loops   NUMBER := 1000000;
  l_start   NUMBER;
  l_number  NUMBER;
BEGIN
  -- Time block.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    BEGIN
      l_number := i;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;

  DBMS_OUTPUT.put_line('Block   : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time no block.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_number := i;
  END LOOP;

  DBMS_OUTPUT.put_line('No block: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

The output from this script shows that there is no perceivable impact created by placing an anonymous block within a loop provided it contains no declarations.

SQL> @block_in_loop.sql
Block   : 20
No block: 20

PL/SQL procedure successfully completed.

Based on this information declarations within loops are removed from the code and the result is that procedures and functions are being called within loops that internally contain variable declarations.  Does this have an impact? The function_in_loop.sql script provides the answer to this question by comparing a function call to a regular assignment.

function_in_loop.sql

CREATE OR REPLACE FUNCTION overhead_function(p_number  IN  NUMBER)
  RETURN NUMBER AS
  l_number  NUMBER;
BEGIN
  l_number := p_number;
  RETURN l_number;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_loops   NUMBER := 1000000;
  l_start   NUMBER;
  l_number  NUMBER;
BEGIN
  -- Time function call.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_number := overhead_function(p_number => i);
  END LOOP;

  DBMS_OUTPUT.put_line('Procedure   : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time no function call.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_number := i;
  END LOOP;

  DBMS_OUTPUT.put_line('No Procedure: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

DROP FUNCTION overhead_function;

The results of this script show that the impact of a function call is even greater than a declaration within the loop.

SQL> @function_in_loop.sql

Function created.

Function   : 189
No Function: 20

PL/SQL procedure successfully completed.

Function dropped.

The reason for the increased impact is that there is an overhead associated with each procedure or function call, regardless of the complexity of the call specification.  This overhead can be shown more clearly by the procedure_in_loop.sql script.

procedure_in_loop.sql

CREATE OR REPLACE PROCEDURE overhead_procedure AS
BEGIN
  NULL;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_loops   NUMBER := 1000000;
  l_start   NUMBER;
BEGIN
  -- Time block.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    overhead_procedure;
  END LOOP;

  DBMS_OUTPUT.put_line('Procedure   : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time no block.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('No Procedure: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

DROP PROCEDURE overhead_procedure;

The procedure used by this script accepts no parameters and does no work, so any delay is purely associated with the overhead of calling the procedure.  The results displayed below clearly show that delay.

SQL> @procedure_in_loop.sql

Procedure created.

Procedure   : 128
No Procedure: 3

PL/SQL procedure successfully completed.

Procedure dropped.

One conclusion that may be drawn from all these examples is that the use of declarations, procedures, and functions within loops is erroneous.  Further, it may be concluded that to use procedures and functions at all is wrong as they incur an unnecessary overhead.  In actual fact, the results from all of these examples must be taken in context.  In all cases results of 1,000,000 loops were compared, so the overheads experienced are actually very small.  In addition, the benefits of using procedures and functions for modular programming need no explanation.

The next section will look at the pitfalls in trying to “reinvent the wheel” in relation to Oracle built-in string functions.

Duplication of built-in string functions

Oracle provides many built in string functions for use in SQL and PL/SQL including:

ASCII, CHR, CONCAT, INITCAP, INSTR, LENGTH, LOWER, LPAD, LTRIM, NLS_INITCAP, NLS_LOWER, NLSSORT, NLS_UPPER, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REPLACE, RPAD, RTRIM, SOUNDEX, SUBSTR, TRANSLATE, TREAT, TRIM, UPPER

In many cases these functions are implemented using low-level code that is more efficient than normal PL/SQL.  Care should be taken not to reinvent the wheel as this wastes time and may result in poor performance.  The regular_expression.sql script shows how built-in functions can simply code and improve performance.

regular_experssion.sql

CREATE OR REPLACE FUNCTION good_credit_card (p_credit_card  IN  VARCHAR2)
  RETURN BOOLEAN AS
  l_number        NUMBER;
  ex_bad_card     EXCEPTION;
BEGIN
  l_number := TO_NUMBER(SUBSTR(p_credit_card, 1, 4));
  l_number := TO_NUMBER(SUBSTR(p_credit_card, 6, 4));
  l_number := TO_NUMBER(SUBSTR(p_credit_card, 11, 4));
  l_number := TO_NUMBER(SUBSTR(p_credit_card, 16, 4));
  IF SUBSTR(p_credit_card, 5, 1)  != ' '
  OR SUBSTR(p_credit_card, 10, 1) != ' '
  OR SUBSTR(p_credit_card, 15, 1) != ' ' THEN
    RAISE ex_bad_card;
  END IF;
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_loops         NUMBER := 100000;
  l_start         NUMBER;
  l_credit_card   VARCHAR2(19)  := '1234 1234 1234 1234';
BEGIN
  -- Time manual check.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF NOT good_credit_card(p_credit_card => l_credit_card) THEN
      DBMS_OUTPUT.put_line('Bad Credit Card: ' || l_credit_card);
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line('Manual check      : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time regular expression.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF NOT REGEXP_LIKE(l_credit_card, '[0-9]{4} [0-9]{4} [0-9]{4} [0-9]{4}') THEN
      DBMS_OUTPUT.put_line('Bad Credit Card: ' || l_credit_card);
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular expression: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;
/

DROP FUNCTION good_credit_card;

The script creates a function which validates the format of a credit card number.  It then compares the speed of this function to the same format check performed by a regular expression.  The results from the script are displayed below.

SQL> @regular_expression.sql

Function created.

Manual check      : 186
Regular expression: 1

PL/SQL procedure successfully completed.

Function dropped.

Not only has time been wasted writing the validation code, but system performance has been reduced in the process.

It is important to read the new features manual for each new database version to get an idea of which new built-in functions may be of use.

The next section will look at the improvements in efficiency that can be made by reducing the number of datatype conversions in PL/SQL code.