What is more efficient, case statement or if elseif etc in PL/SQL
12,613
Solution 1
I guess you are underestimating the query optimizer. In my opinion it's better to concentrate on writing correct and readable sql queries than to micro-optimize things which have a questionable benefit.
In both cases, Oracle will probably do the same operations at execution time.
Solution 2
Although the documentation says "The CASE statement is more readable and more efficient. So, when possible, rewrite lengthy IF-THEN-ELSIF statements as CASE statements.", I didn't find a significant difference between three styles of conditionals.
On the other hand, what makes a difference is data type used in the boolean expression. For example PLS_INTEGER is much more efficient that INTEGER.
See the example below (on my server it gives execution times 103,102,104 ms) :
set serveroutput on
declare
tim integer;
t integer := 1; -- <<< -- try pls_integer instead integer
procedure print(text in varchar2) is begin DBMS_OUTPUT.PUT_LINE(text); end;
begin
tim := dbms_utility.get_time;
for i in 1..10000
loop
for x in 1..10
loop
t := x*10;
case t
when 1 then t := 1;
when 2 then t := 1;
when 3 then t := 1;
when 4 then t := 1;
when 5 then t := 1;
when 6 then t := 1;
when 7 then t := 1;
when 8 then t := 1;
when 9 then t := 1;
when 10 then t := 1;
when 11 then t := 1;
when 12 then t := 1;
when 13 then t := 1;
when 14 then t := 1;
when 15 then t := 1;
when 16 then t := 1;
when 17 then t := 1;
when 18 then t := 1;
when 19 then t := 1;
when 20 then t := 1;
when 21 then t := 1;
when 22 then t := 1;
when 23 then t := 1;
when 24 then t := 1;
when 25 then t := 1;
when 26 then t := 1;
when 27 then t := 1;
when 28 then t := 1;
when 29 then t := 1;
when 30 then t := 1;
when 31 then t := 1;
when 32 then t := 1;
when 33 then t := 1;
when 34 then t := 1;
when 35 then t := 1;
when 36 then t := 1;
when 37 then t := 1;
when 38 then t := 1;
when 39 then t := 1;
when 40 then t := 1;
when 41 then t := 1;
when 42 then t := 1;
when 43 then t := 1;
when 44 then t := 1;
when 45 then t := 1;
when 46 then t := 1;
when 47 then t := 1;
when 48 then t := 1;
when 49 then t := 1;
when 50 then t := 1;
when 51 then t := 1;
when 52 then t := 1;
when 53 then t := 1;
when 54 then t := 1;
when 55 then t := 1;
when 56 then t := 1;
when 57 then t := 1;
when 58 then t := 1;
when 59 then t := 1;
when 60 then t := 1;
when 61 then t := 1;
when 62 then t := 1;
when 63 then t := 1;
when 64 then t := 1;
when 65 then t := 1;
when 66 then t := 1;
when 67 then t := 1;
when 68 then t := 1;
when 69 then t := 1;
when 70 then t := 1;
when 71 then t := 1;
when 72 then t := 1;
when 73 then t := 1;
when 74 then t := 1;
when 75 then t := 1;
when 76 then t := 1;
when 77 then t := 1;
when 78 then t := 1;
when 79 then t := 1;
when 80 then t := 1;
when 81 then t := 1;
when 82 then t := 1;
when 83 then t := 1;
when 84 then t := 1;
when 85 then t := 1;
when 86 then t := 1;
when 87 then t := 1;
when 88 then t := 1;
when 89 then t := 1;
when 90 then t := 1;
when 91 then t := 1;
when 92 then t := 1;
when 93 then t := 1;
when 94 then t := 1;
when 95 then t := 1;
when 96 then t := 1;
when 97 then t := 1;
when 98 then t := 1;
when 99 then t := 1;
when 100 then t := 1;
when 101 then t := 1;
else t := 0;
end case;
end loop;
end loop;
print(dbms_utility.get_time-tim);
tim := dbms_utility.get_time;
for i in 1..10000
loop
for x in 1..10
loop
t := x*10;
case
when t = 1 then t := 1;
when t = 2 then t := 1;
when t = 3 then t := 1;
when t = 4 then t := 1;
when t = 5 then t := 1;
when t = 6 then t := 1;
when t = 7 then t := 1;
when t = 8 then t := 1;
when t = 9 then t := 1;
when t = 10 then t := 1;
when t = 11 then t := 1;
when t = 12 then t := 1;
when t = 13 then t := 1;
when t = 14 then t := 1;
when t = 15 then t := 1;
when t = 16 then t := 1;
when t = 17 then t := 1;
when t = 18 then t := 1;
when t = 19 then t := 1;
when t = 20 then t := 1;
when t = 21 then t := 1;
when t = 22 then t := 1;
when t = 23 then t := 1;
when t = 24 then t := 1;
when t = 25 then t := 1;
when t = 26 then t := 1;
when t = 27 then t := 1;
when t = 28 then t := 1;
when t = 29 then t := 1;
when t = 30 then t := 1;
when t = 31 then t := 1;
when t = 32 then t := 1;
when t = 33 then t := 1;
when t = 34 then t := 1;
when t = 35 then t := 1;
when t = 36 then t := 1;
when t = 37 then t := 1;
when t = 38 then t := 1;
when t = 39 then t := 1;
when t = 40 then t := 1;
when t = 41 then t := 1;
when t = 42 then t := 1;
when t = 43 then t := 1;
when t = 44 then t := 1;
when t = 45 then t := 1;
when t = 46 then t := 1;
when t = 47 then t := 1;
when t = 48 then t := 1;
when t = 49 then t := 1;
when t = 50 then t := 1;
when t = 51 then t := 1;
when t = 52 then t := 1;
when t = 53 then t := 1;
when t = 54 then t := 1;
when t = 55 then t := 1;
when t = 56 then t := 1;
when t = 57 then t := 1;
when t = 58 then t := 1;
when t = 59 then t := 1;
when t = 60 then t := 1;
when t = 61 then t := 1;
when t = 62 then t := 1;
when t = 63 then t := 1;
when t = 64 then t := 1;
when t = 65 then t := 1;
when t = 66 then t := 1;
when t = 67 then t := 1;
when t = 68 then t := 1;
when t = 69 then t := 1;
when t = 70 then t := 1;
when t = 71 then t := 1;
when t = 72 then t := 1;
when t = 73 then t := 1;
when t = 74 then t := 1;
when t = 75 then t := 1;
when t = 76 then t := 1;
when t = 77 then t := 1;
when t = 78 then t := 1;
when t = 79 then t := 1;
when t = 80 then t := 1;
when t = 81 then t := 1;
when t = 82 then t := 1;
when t = 83 then t := 1;
when t = 84 then t := 1;
when t = 85 then t := 1;
when t = 86 then t := 1;
when t = 87 then t := 1;
when t = 88 then t := 1;
when t = 89 then t := 1;
when t = 90 then t := 1;
when t = 91 then t := 1;
when t = 92 then t := 1;
when t = 93 then t := 1;
when t = 94 then t := 1;
when t = 95 then t := 1;
when t = 96 then t := 1;
when t = 97 then t := 1;
when t = 98 then t := 1;
when t = 99 then t := 1;
when t = 100 then t := 1;
when t = 101 then t := 1;
else t := 0;
end case;
end loop;
end loop;
print(dbms_utility.get_time-tim);
tim := dbms_utility.get_time;
for i in 1..10000
loop
for x in 1..10
loop
t := x*10;
if t = 1 then t := 1;
elsif t = 2 then t := 1;
elsif t = 3 then t := 1;
elsif t = 4 then t := 1;
elsif t = 5 then t := 1;
elsif t = 6 then t := 1;
elsif t = 7 then t := 1;
elsif t = 8 then t := 1;
elsif t = 9 then t := 1;
elsif t = 10 then t := 1;
elsif t = 11 then t := 1;
elsif t = 12 then t := 1;
elsif t = 13 then t := 1;
elsif t = 14 then t := 1;
elsif t = 15 then t := 1;
elsif t = 16 then t := 1;
elsif t = 17 then t := 1;
elsif t = 18 then t := 1;
elsif t = 19 then t := 1;
elsif t = 20 then t := 1;
elsif t = 21 then t := 1;
elsif t = 22 then t := 1;
elsif t = 23 then t := 1;
elsif t = 24 then t := 1;
elsif t = 25 then t := 1;
elsif t = 26 then t := 1;
elsif t = 27 then t := 1;
elsif t = 28 then t := 1;
elsif t = 29 then t := 1;
elsif t = 30 then t := 1;
elsif t = 31 then t := 1;
elsif t = 32 then t := 1;
elsif t = 33 then t := 1;
elsif t = 34 then t := 1;
elsif t = 35 then t := 1;
elsif t = 36 then t := 1;
elsif t = 37 then t := 1;
elsif t = 38 then t := 1;
elsif t = 39 then t := 1;
elsif t = 40 then t := 1;
elsif t = 41 then t := 1;
elsif t = 42 then t := 1;
elsif t = 43 then t := 1;
elsif t = 44 then t := 1;
elsif t = 45 then t := 1;
elsif t = 46 then t := 1;
elsif t = 47 then t := 1;
elsif t = 48 then t := 1;
elsif t = 49 then t := 1;
elsif t = 50 then t := 1;
elsif t = 51 then t := 1;
elsif t = 52 then t := 1;
elsif t = 53 then t := 1;
elsif t = 54 then t := 1;
elsif t = 55 then t := 1;
elsif t = 56 then t := 1;
elsif t = 57 then t := 1;
elsif t = 58 then t := 1;
elsif t = 59 then t := 1;
elsif t = 60 then t := 1;
elsif t = 61 then t := 1;
elsif t = 62 then t := 1;
elsif t = 63 then t := 1;
elsif t = 64 then t := 1;
elsif t = 65 then t := 1;
elsif t = 66 then t := 1;
elsif t = 67 then t := 1;
elsif t = 68 then t := 1;
elsif t = 69 then t := 1;
elsif t = 70 then t := 1;
elsif t = 71 then t := 1;
elsif t = 72 then t := 1;
elsif t = 73 then t := 1;
elsif t = 74 then t := 1;
elsif t = 75 then t := 1;
elsif t = 76 then t := 1;
elsif t = 77 then t := 1;
elsif t = 78 then t := 1;
elsif t = 79 then t := 1;
elsif t = 80 then t := 1;
elsif t = 81 then t := 1;
elsif t = 82 then t := 1;
elsif t = 83 then t := 1;
elsif t = 84 then t := 1;
elsif t = 85 then t := 1;
elsif t = 86 then t := 1;
elsif t = 87 then t := 1;
elsif t = 88 then t := 1;
elsif t = 89 then t := 1;
elsif t = 90 then t := 1;
elsif t = 91 then t := 1;
elsif t = 92 then t := 1;
elsif t = 93 then t := 1;
elsif t = 94 then t := 1;
elsif t = 95 then t := 1;
elsif t = 96 then t := 1;
elsif t = 97 then t := 1;
elsif t = 98 then t := 1;
elsif t = 99 then t := 1;
elsif t = 100 then t := 1;
elsif t = 101 then t := 1;
else t := 0;
end if;
end loop;
end loop;
print(dbms_utility.get_time-tim);
end;
/
Author by
Bato-Bair Tsyrenov
I am Student at the begging of the coder life. Love Java and databases, Enjoy Java EE. Find interesting enterprise systems and programming them. Also a fan of Android dev
Updated on June 05, 2022Comments
-
Bato-Bair Tsyrenov almost 2 years
examples are:
CASE WHEN selector = 'value1' THEN S1; WHEN selector = 'value2' THEN S2; WHEN selector = 'value3' THEN S3; ... ELSE Sn; -- default case END CASE;or another:
IF(boolean_expression 1)THEN S1; -- Executes when the boolean expression 1 is true ELSIF( boolean_expression 2) THEN S2; -- Executes when the boolean expression 2 is true ELSIF( boolean_expression 3) THEN S3; -- Executes when the boolean expression 3 is true ELSE S4; -- executes when the none of the above condition is true END IF;