Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Unanswered: DB2 case query precalculation?

    Hi everybody.

    This question will probably sound strange to some of you, but i have a problem that can be best described with this case query:

    SELECT CASE WHEN (88=72) THEN (SELECT COUNT(*) FROM
    SYSIBM.SYSTABLES AS T1,SYSIBM.SYSTABLES AS T2,SYSIBM.SYSTABLES AS T3)
    ELSE 1819 END FROM SYSIBM.SYSDUMMY1;

    Now, could somebody be so kind and explain why on earth case statement precalculates the first subquery (for TRUE) causing a noticeable delay while it's obvious that it won't be the resulting one?

    Thing is that because of this I can't put two heavy loaded subqueries into CASE statement without significant lagging.

    Please help.

    KR

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It's because the entire statement must be compiled before it starts executing. CASE expression is not evaluated until the results are being returned to the client, but by that time all subqueries' results must be available.

    If you feel it's not correct, you could submit an enhancement request to IBM.

    If you depend on conditional execution of queries, I think you should consider writing a stored procedure.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an equivalent query which was executed quickly.
    (Tested on DB2 Express-C 9.7.4 for Windows.)

    Execution time was 0.04 sec(= 23.37.58.555000 - 23.37.58.515000)
    Execution time using CASE expression was 1 min 39.814 sec(= 23.40.16.994000 - 23.38.37.180000)

    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES current_timestamp;
    
    SELECT (SELECT *
             FROM (SELECT COUNT(*)
                    FROM  SYSCAT.TABLES AS T1
                        , SYSCAT.TABLES AS T2
                        , SYSCAT.TABLES AS T3
                  )
             WHERE 88 = 72
            UNION ALL
            SELECT 1819
             FROM  SYSIBM.SYSDUMMY1
             WHERE NOT(88 = 72)
           )
     FROM  SYSIBM.SYSDUMMY1
    ;
    
    VALUES current_timestamp;
    ------------------------------------------------------------------------------
    VALUES current_timestamp
    
    1                         
    --------------------------
    2011-06-26-23.37.58.515000
    
      1 record(s) selected.
    
    
    SELECT (SELECT * FROM (SELECT COUNT(*) FROM  SYSCAT.TABLES AS T1 , SYSCAT.TABLES AS T2 , SYSCAT.TABLES AS T3 ) WHERE 88 = 72 UNION ALL SELECT 1819 FROM  SYSIBM.SYSDUMMY1 WHERE NOT(88 = 72) ) FROM  SYSIBM.SYSDUMMY1
    
    1          
    -----------
           1819
    
      1 record(s) selected.
    
    
    VALUES current_timestamp
    
    1                         
    --------------------------
    2011-06-26-23.37.58.555000
    
      1 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CASE expression is not evaluated until the results are being returned to the client, but by that time all subqueries' results must be available.
    I tested on DB2 Express-C 9.7.4 for Windows.

    Example 3 is interesting.

    Example 1: This worked.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT CASE
           WHEN 88 = 72 THEN
                '0' + 'a'
           ELSE '0' + '1'
           END
     FROM  SYSIBM.SYSDUMMY1
    ;
    ------------------------------------------------------------------------------
    
    1                                         
    ------------------------------------------
                                             1
    
      1 record(s) selected.
    Example 2: But, this got error(SQL0420N).
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT CASE
           WHEN 88 = 72 THEN
                (SELECT '0' + 'a'
                  FROM  SYSIBM.SYSDUMMY1
                )
           ELSE (SELECT '0' + '1'
                  FROM  SYSIBM.SYSDUMMY1
                )
           END
     FROM  SYSIBM.SYSDUMMY1
    ;
    ------------------------------------------------------------------------------
    
    1                                         
    ------------------------------------------
    SQL0420N  Invalid character found in a character string argument of the 
    function "DECFLOAT".  SQLSTATE=22018
    Example 3: This worked.
    Code:
    SELECT CASE
           WHEN 88 = 72 THEN
                '0' + (SELECT 'a' FROM SYSIBM.SYSDUMMY1)
           ELSE (SELECT '0' + '1'
                  FROM  SYSIBM.SYSDUMMY1
                )
           END
     FROM  SYSIBM.SYSDUMMY1
    ;
    ------------------------------------------------------------------------------
    
    1                                         
    ------------------------------------------
                                             1
    
      1 record(s) selected.
    Last edited by tonkuma; 06-26-11 at 12:29. Reason: Add Example 3:

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guessesd the result of Example 3:, like...
    Result of subquery (SELECT 'a' FROM SYSIBM.SYSDUMMY1) was got before evaluation of CASE expression.
    But, result-expression '0' + (...) might be evaluated only at the time corresponding WHEN condition was true.

    n_i,
    Is this true?

  6. #6
    Join Date
    Jun 2011
    Posts
    3
    thank you tonkuma ))

    you've saved the day

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tonkuma View Post
    Result of subquery (SELECT 'a' FROM SYSIBM.SYSDUMMY1) was got before evaluation of CASE expression.
    But, result-expression '0' + (...) might be evaluated only at the time corresponding WHEN condition was true.

    n_i,
    Is this true?
    I can't say for sure, but I think this is correct: SELECT is built into the execution plan, but a simple expression is evaluated along with the CASE expression at the very end, just before the result set is returned to the client.

Tags for this Thread

Posting Permissions

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