Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Toronto, Canada

    Unanswered: db2 case statement performance

    I was doing some performance tuning and came upon and interesting case. It had do do with case statements. What I found was regardless if the true portion of the case was found, the not true portions are executed as well. For me, this is causing performance and load issues I had not realized was the case....

    take my simple example:

    values ( case when current date=current date then 1
    else (select count(*) from schema.bigTable) end );

    In the statement above, the value returned is 1 because current date will always = current date. If you run this statement, the count in the else will be executed regardless of the fact if the statement above is true. Assuming you use a relatively big table, the case is obvious. I find this counter intuitive as there is no reason to execute the statement in the else. The above was tested on both 10.1 and 10.5 versions of LUW DB2.

    I'd love to hear comments and work-arounds.

  2. #2
    Join Date
    Apr 2012
    Provided Answers: 27
    This is a FAQ. Plain SQL evaluates all branches of a case expression in this context. Use an alternative construct or context.

  3. #3
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 6
    I think IBM would be interested in how you prove this happening. From my recollection it was taught by all the big name speakers in paid classes and user group meetings was to code your most frequently occurring conditions at the beginning of your case statement as it breaks out of the case as soon as a condition evaluates as true.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts