Oppolzer - Informatik / Blog

The rewrite of the SQL part should have been:

K29.LS_ACTUAL_COMPL_DT <> '' and
K29.LS_ACTUAL_COMPL_DT IS not NULL

'' was missing ...



Am 29.01.2015 um 10:57 schrieb Bernd Oppolzer:
> I looked at the SQL that was attached to this post.
>
> There are many codings that seem somehow strange to me,
> for example:
>
>        CASE
>           WHEN    IFNULL(K06.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>                   CASE
>                    WHEN    IFNULL(K29.LS_ACTUAL_COMPL_DT, '') <> ''
>                            THEN K29.LS_ACTUAL_COMPL_DT
>                    ELSE    ''
>                   END
>           WHEN    IFNULL(K05.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>                   CASE
>                    WHEN    IFNULL(K28.LS_ACTUAL_COMPL_DT, '') <> ''
>                            THEN K28.LS_ACTUAL_COMPL_DT
>                    ELSE    ''
>                   END
>           WHEN    IFNULL(K04.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>                   CASE
>                    WHEN    IFNULL(X66.LS_ACTUAL_COMPL_DT, '') <> ''
>                            THEN X66.LS_ACTUAL_COMPL_DT
>                    ELSE    ''
>                   END
>           WHEN    IFNULL(K03.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>                   CASE
>                    WHEN    IFNULL(X65.LS_ACTUAL_COMPL_DT, '') <> ''
>                            THEN X65.LS_ACTUAL_COMPL_DT
>                    ELSE    ''
>                   END
>           WHEN    IFNULL(K02.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>                   CASE
>                    WHEN    IFNULL(X64.LS_ACTUAL_COMPL_DT, '') <> ''
>                            THEN X64.LS_ACTUAL_COMPL_DT
>                    ELSE    ''
>                   END
>           WHEN    IFNULL(K01.LS_ACTUAL_COMPL_DT, '') <> '' THEN
>                   CASE
>                    WHEN    IFNULL(X63.LS_ACTUAL_COMPL_DT, '') <> ''
>                            THEN X63.LS_ACTUAL_COMPL_DT
>                    ELSE    ''
>                   END
>           ELSE    ''
>          END) <> ''
>
> this is part of the WHERE condition.
>
> If I don't miss anything, every such coding
>
> IFNULL(K29.LS_ACTUAL_COMPL_DT, '') <> ''
>
> can be rewritten to
>
> K29.LS_ACTUAL_COMPL_DT <> and
> K29.LS_ACTUAL_COMPL_DT IS not NULL
>
> and we all know how critical index usage is with respect to functions in the
> where clause.
>
> (BTW: why are all those columns allowed to have NULLs? With a "normal" DB2 design
> they would be NOT NULL ...)
>
> I don't know:
>
> maybe other DBMSes do more effort in algebraic rewrite of complex SQL statements,
> which DB2 leaves to the coder. So the difference in CPU and run time to me would be
> evident from this design decision. This can be complained about, but I would tune
> the SQL first, regardless of the target DBMS. (I have no time to investigate this
> further in this special case, unfortunately; this SQL has just too many things
> which look very strange to me ... some debate with the original author would be
> needed).
>
> I never had a performance problem with DB2 z/OS, 25 years of experience.
>
> But I had serious performance problems with other DBMSes, and these were
> technical performance problems which had nothing to do with SQL coding
> or index usage, but, for example, poor networking implementations etc.
>
> Kind regards
>
> Bernd
>
>
>
>
> Am 28.01.2015 um 22:19 schrieb T.W.:
>>
>> Quite true Kurt.
>>
>> I could try running it later this evening when the system would be more quiet.
>>
>> The email I got this afternoon this is run times they got.
>> SQL Server (SS) has more data too.
>>
>>  SS:  Rows:  506 Execution time:  18 s
>> DB2: Rows:  282 Execution time:  1 min 25s
>>
>> I attached the BMC SQL Performance Explain, SQL and other information.
>>
>> Attachment Links: EXPLAIN.txt (21 k)
>