CODEWITHSUNDEEP
oracle-databaseplsql
N10ram
N10ram

Reputation: 81

Which PL/SQL statement is faster?

Which one is the faster, searched CASE or simple CASE statement?

xx := case a 
       when 'x' then 'b'
       when 'y' then 'c'
       else 'z'
      end;

xx := case
       when a='x' then 'b'
       when a='y' then 'c'
       else 'z'
      end;

Upvotes: 0

Views: 406

Answers (2)

Jon Heller
Jon Heller

Reputation: 36912

Simple CASE and searched CASE statements are equally fast.

Performance comparisons are difficult, even for such a simple question. My answer is based on the three reasons below.

Simple Test

The two anonymous blocks below run the different kind of case statements. They were run 5 times (to throw out any extremes caused by a "cold" system), and alternating the runs (to ensure that system activity would not unfairly affect only one type).

set serveroutput on;

--Simple CASE.
declare
    a varchar2(100) := 'z';
    b varchar2(1);
begin
    for i in 1 .. 40000000 loop
        a := i;
        b := case a when 'x' then 'b' when 'y' then 'c' else 'z' end;
    end loop;
end;
/

--Searched CASE.
declare
    a varchar2(100) := 'z';
    b varchar2(1);
begin
    for i in 1 .. 40000000 loop
        a := i;
        b := case when a='x' then 'b' when a='y' then 'c' else 'z' end;
    end loop;
end;
/

The runs were ridiculously close. Normally I wouldn't expect even the same code to run so uniformly.

--Simple CASE
Elapsed: 00:00:11.04
Elapsed: 00:00:11.00
Elapsed: 00:00:11.04
Elapsed: 00:00:10.99
Elapsed: 00:00:10.99

Average: 11.012

--Searched CASE
Elapsed: 00:00:11.04
Elapsed: 00:00:10.98
Elapsed: 00:00:11.00
Elapsed: 00:00:10.99
Elapsed: 00:00:11.02

Average: 11.006

A Performance Difference Wouldn't Make Sense

The different CASE formats are just syntactic differences. The easiest way to program the parser and compiler would be to translate both forms into the same internal representation, and call the same code for each. If for some reason one method was much faster than the other, at some point the developers would probably copy that code and make them the same.

Other than rare bugs, there are few places where such minor syntactic differences matter. It's tempting to believe there's a difference because we all want simple rules to follow to make our code run faster. Programming would be simple if we could just follow a list of syntax rules: "<> is faster than !=", "count(1) is faster than count(*)", etc. But those examples, although repeated many times, are complete myths.

A Performance Difference Wouldn't Matter Anyway

The time spent evaluating a CASE statement should be irrelevant in almost every an Oracle database. Even if one CASE method was twice as slow as another it should be unnoticeable - why would Oracle process a PL/SQL line millions of times a second anyway? The real work should be done in SQL, and the time to read data from a hard drive is more significant than the minuscule amount of CPU time to evaluate a CASE statement.

Upvotes: 4

Khazratbek
Khazratbek

Reputation: 1656

I think as follow:

Let's think that a is a field on your table. Your table consists of large amount data. So it would be good, if you will create index to your table by a field. In that case first way should work faster, caz' it's looking in a indexed column. A principle is: your case statement knows, that it will work only with column a, so he don't think about other columns. (e.g. your table consists of 20 columns, so your first statement will take only column a and search from it). In second case statement it will work as follows: case statement don't initializing anything, it's waiting for your condition. So every time it reads the condition, he searches for condition matching again and again (it consider the all columns). But if you table does not have really large amount of data, it doesn't affect on you so much.

Upvotes: 0

Related Questions