Oppolzer - Informatik / Blog

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)