Forum Stats

  • 3,751,300 Users
  • 2,250,339 Discussions
  • 7,867,378 Comments

Discussions

CASE or DECODE - what is faster?

pawel
pawel Member Posts: 49 Blue Ribbon
edited Aug 5, 2010 8:08AM in SQL & PL/SQL
when i use them in select clause with varchar2 or number datatype? (Oracle 10.2 EE)

regards

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    I think there would be not much of difference. They are generally not consider for Performance when using in the Select List of a Select Query.
  • Lokanath Giri
    Lokanath Giri Member Posts: 764 Silver Badge
    edited Aug 5, 2010 7:42AM
    Check this thread
    612830

    No much difference, CASE looks better
    SQL> ed
    Wrote file afiedt.buf
    
        DECLARE
           l_start NUMBER;
           l_PRED VARCHAR2(1) :='C';
           X VARCHAR2(1);
         BEGIN
           l_start:=DBMS_UTILITY.get_time;
          FOR i in 1..100000 Loop
           SELECT DECODE( l_PRED,'A',1,'B+',2,'C+',3,'C',1,0) INTO X FROM DUAL;
          END LOOP;
         DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time-l_start);
       END;
    /
    307
    
    PL/SQL procedure successfully completed.
    
    SQL>  DECLARE
         l_start NUMBER;
          l_PRED VARCHAR2(1) :='C';
          X VARCHAR2(1);
        BEGIN
          l_start:=DBMS_UTILITY.get_time;
         FOR i in 1..100000 Loop
        SELECT ( CASE 'C'
                     WHEN 'A'  THEN  1
                     WHEN 'B+' THEN  2
                     WHEN 'C+' THEN  3
                     WHEN 'C'  THEN  1
                     ELSE            0
                 END) INTO X
                   FROM DUAL;
       END LOOP;
        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time-l_start);
       END;
      /
    292
  • 717817
    717817 Member Posts: 429
    edited Aug 5, 2010 8:08AM
    Hi,

    This helped me in understanding the difference between case and decode

    Quoting from an expert amoungst experts Billy Verreynne. here is what he says:

    From what i know, in case of multiple condition checking, Case is simpler to write when compared to Decode. Also Decode can not be used in PL/SQL code where as CASE is possible.

    There is very little performance difference between CASE and DECODE on the same platform. One has to run 100's of 1000's of iterations to see a difference, and even then it is debatable of whether that difference is just due to the CASE vs DECODE.

    There seems to be a difference in performance between CASE and DECODE depending on the type of CPU. On some CPU architecture a DECODE will seem to be just slightly faster. On others, the CASE will seem to be slightly faster.

    The performance difference is so slight that it makes very little sense in using that as primary criteria for whether to use CASE or DECODE. So unless you're calling this statement from a very tight loop doing millions of iterations, the decision should rather be which one, CASE or DECODE, best suits the need.

    Wrong question really.. unless you writing a very tight loop doing 100's of 1000's of iterations using that type of conditional structure. And even then performance difference will not be that significant.

    The right question to ask is which one is more flexible and allows for the programmer that comes after you to read and understand and maintain your code. The CASE statement is in this regard, a lot better than a DECODE statement.

    Looking just at performance (which I suggest you do not do in isolation!!), I get mixed results using 10G Enterprise on different platforms (HP-UX vs SUN AMD) and operating systems (HP-UX vs Linux).

    On the former the DECODE is slightly faster. On the latter the CASE is slightly faster.

    Using the construct you've specified and doing a tight loop of a 100,000 iterations, the elapsed execution times are:
    HP-UX DECODE = 00:00:11.83
    HP-UX CASE = 00:00:12.32
    Linux/AMD DECODE =00:00:02.02
    Linux/AMD CASE = 00:00:01.84

    Obviosuly the CPU architecture plays a major role here. The AMD is considered as the best 64 CPU on the market. The HP-UX PARISC CPU (also 64bit), does not really compare raw performance wise. In addition this is a RISC CPU whereas the AMD CPU is I believe more CISC than RISC.

    Also interesting that the faster Sun AMD/Linux server I used for this benchmark is about 10% the price of the HP-UX server.. and about 5x faster ito raw speed as this benchmark showed. :-)

    An interesting exercise, but one with little real world value. Yes performance is important. But within PL/SQL, not to the level about debating whether a CASE or DECODE is faster. As I've mentioned, I believe the right question being one about readability and maintenance and not performance in this case.


    Hope this helps and all credit goes to the expert billy.

    Edited by: Kevin CK on Aug 5, 2010 4:07 PM

    Edited by: Kevin CK on Aug 5, 2010 4:08 PM
This discussion has been closed.