8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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
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...