Logic/Branch Ordering in PL/SQL

PL/SQL evaluates CASE and ELSIF statements from the top down. As a result, placing the most likely outcomes at the top reduces the amount of evaluations that must be done, improving the performance of your code. This article demonstrates the affect of branch ordering on the performance of CASE and ELSIF statements.

CASE
ELSIF

CASE

The following example compares the performance of two CASE statements. The first will always complete after the first evaluation, while the second will complete at the final evaluation.

SET SERVEROUTPUT ON
DECLARE
 l_loops NUMBER := 1000000;
 l_value VARCHAR2(1) := 'A';
 l_start NUMBER;
BEGIN
 -- Time CASE first.
 l_start := DBMS_UTILITY.get_time;
 FOR i IN 1 .. l_loops LOOP
 CASE l_value
 WHEN 'A' THEN
 NULL;
 WHEN 'B' THEN
 NULL;
 WHEN 'C' THEN
 NULL;
 WHEN 'D' THEN
 NULL;
 WHEN 'E' THEN
 NULL;
 END CASE;
 END LOOP;
 DBMS_OUTPUT.put_line('CASE first : ' ||
 (DBMS_UTILITY.get_time - l_start));
 -- Time CASE first.
 l_start := DBMS_UTILITY.get_time;
 FOR i IN 1 .. l_loops LOOP
 CASE l_value
 WHEN 'B' THEN
 NULL;
 WHEN 'C' THEN
 NULL;
 WHEN 'D' THEN
 NULL;
 WHEN 'E' THEN
 NULL;
 WHEN 'A' THEN
 NULL;
 END CASE;
 END LOOP;
 DBMS_OUTPUT.put_line('CASE last : ' ||
 (DBMS_UTILITY.get_time - l_start));
END;
/
CASE first : 5
CASE last : 20
PL/SQL procedure successfully completed.
SQL>

As expected, the code is measurably faster if the most likely outcomes are placed at the top of the list.

ELSIF

The following example is similar to that shown previously, but the CASE statements have been replaced by ELSIF lists. The first will always complete after the first evaluation, while the second will complete at the final evaluation.

SET SERVEROUTPUT ON
DECLARE
 l_loops NUMBER := 1000000;
 l_value VARCHAR2(1) := 'A';
 l_start NUMBER;
BEGIN
 -- Time ELSIF first.
 l_start := DBMS_UTILITY.get_time;
 FOR i IN 1 .. l_loops LOOP
 IF l_value = 'A' THEN
 NULL;
 ELSIF l_value = 'B' THEN
 NULL;
 ELSIF l_value = 'C' THEN
 NULL;
 ELSIF l_value = 'D' THEN
 NULL;
 ELSIF l_value = 'E' THEN
 NULL;
 END IF;
 END LOOP;
 DBMS_OUTPUT.put_line('ELSIF first: ' ||
 (DBMS_UTILITY.get_time - l_start));
 -- Time ELSIF last.
 l_start := DBMS_UTILITY.get_time;
 FOR i IN 1 .. l_loops LOOP
 IF l_value = 'B' THEN
 NULL;
 ELSIF l_value = 'C' THEN
 NULL;
 ELSIF l_value = 'D' THEN
 NULL;
 ELSIF l_value = 'E' THEN
 NULL;
 ELSIF l_value = 'A' THEN
 NULL;
 END IF;
 END LOOP;
 DBMS_OUTPUT.put_line('ELSIF last : ' ||
 (DBMS_UTILITY.get_time - l_start));
END;
/
ELSIF first: 5
ELSIF last : 18
PL/SQL procedure successfully completed.
SQL>

Once again, the code is measurably faster if the most likely outcomes are placed at the top of the list.

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)