DB2 case query precalculation?

mirekjozic · #1 (**permalink**) 06-26-11, 09:31

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

n_i · #2 (**permalink**) 06-26-11, 10:59

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.

tonkuma · #3 (**permalink**) 06-26-11, 11:14

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.

tonkuma · #4 (**permalink**) 06-26-11, 11:24

Quote:

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.

tonkuma · #5 (**permalink**) 06-26-11, 11:49

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?

mirekjozic · #6 (**permalink**) 06-26-11, 12:24

thank you tonkuma

))

you've saved the day

n_i · #7 (**permalink**) 06-26-11, 15:29

Quote:

Originally Posted by tonkuma

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech