DBA Hub

📋Steps in this guide1/2

Logic/Branch Ordering in PL/SQL

This article demonstrates the affect of branch ordering on the performance of CASE and ELSIF statements.

oracle miscconfigurationintermediate
by OracleDba
17 views
1

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. As expected, the code is measurably faster if the most likely outcomes are placed at the top of the list.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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>
2

ELSIF

The following example is similar to that shown previously, but the statements have been replaced by lists. The first will always complete after the first evaluation, while the second will complete at the final evaluation. Once again, the code is measurably faster if the most likely outcomes are placed at the top of the list. -

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!