Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2011
    Posts
    25

    Question Unanswered: Improving of a query (multiple joins)

    Hi,

    I have a working Query I need to improve, because it is very, very slow, but the result is exactly what I want.

    Problem is I need to iterate multiple times over the same table, because all Informations I need are stored in the same table.

    So for one ID, there are several rows in the table. Which value stands in the value column depends on the type-column. So I have to iterate over the same table. I solved this with several joins (there are more than in this example, thats the problem :-)

    Ancillary conditions: DB2 9.1/AIX, row with type cpu always exists, so this is the base here and left join is ok - full outer join was even slower...

    Code:
    SELECT
        cpu.id        as ID,
        cpu.date      as DATE, 
        cpu.value     as CPU, 
        memory.value  as MEMORY,
        disk.value    as DISK
    FROM 
        table as cpu 	
        left outer join table as MEMORY
            on (cpu.ID = memory.ID and cpu.DATE = memory.DATE and memory.type = 'MEMORY')
        left outer join table as DISK
            on (cpu.ID = DISK.ID    and cpu.DATE = DISK.DATE    and DISK.type    = 'DISK')
        #...more to come...#
    WHERE 
        cpu.type   = 'CPU'
        and cpu.DATE between date('2011-01-01') and current_date
    GROUP BY 
        cpu.id, cpu.date, cpu.value, memory.value, DISK.value
    ;
    Hope someone experienced could give me a hint on how to improve this query, thanks a lot!

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Try moving the WHERE clause statements to the FROM as a Nested Table:
    Code:
    SELECT
        cpu.id        as ID,
        cpu.date      as DATE, 
        cpu.value     as CPU, 
        memory.value  as MEMORY,
        disk.value    as DISK
    FROM 
         (SELECT col-list
          FROM TABLE 
          WHERE TYPE = 'CPU'
            AND DATE between date('2011-01-01') and current_date
        ) AS CPU
        left outer join table as MEMORY
            on (cpu.ID = memory.ID and cpu.DATE = memory.DATE and memory.type = 'MEMORY')
        left outer join table as DISK
            on (cpu.ID = DISK.ID    and cpu.DATE = DISK.DATE    and DISK.type    = 'DISK')
        #...more to come...#
    GROUP BY 
        cpu.id, cpu.date, cpu.value, memory.value, DISK.value
    ;

  3. #3
    Join Date
    Aug 2011
    Posts
    25
    Hi! Thanks, that is a good hint!

    I included this in my code. But what I don't understand: What is col-list ?

    It works, when I write
    FROM
    (SELECT id, date, value FROM TABLE WHERE...
    ) as CPU

    Writing col-list runs in error. Or did I missunderstand you? :-)

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    col-list is the list of columns you need for use any where else in the query (ON join predicates, Select, Group By, etc. Since you didn't include the entire table or the entire query, I have no ideas what the complete list would be so a 'place holder' of col-list was included.

  5. #5
    Join Date
    Aug 2011
    Posts
    25
    ah ok sorry :-D i misunderstood you.
    Then I need id, date and value as I wrote and that works.

    Performance-Gain ~10% :-) Some seconds, quite good for that small improvement

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    SELECT id
         , date
         , MAX( CASE type
                WHEN 'CPU'    THEN
                     value
                END
              ) AS cpu
         , MAX( CASE type
                WHEN 'MEMORY' THEN
                     value
                END
              ) AS memory
         , MAX( CASE type
                WHEN 'DISK'   THEN
                     value
                END
              ) AS disk
     FROM  table
     WHERE date BETWEEN DATE('2011-01-01') AND current_date
     GROUP BY
           id
         , date
    ;

  7. #7
    Join Date
    Aug 2011
    Posts
    25
    Hi tonkuma,

    thank you very much for your reply (after my last thread, try to make it better now )

    This is incredibly fast!!!

    But to be honest, the syntax is so easy, that I don't understand why it is working...
    Can you explain in 2 words, I somehow don't get it...

    One more question regarding
    Code:
    CASE type
    WHEN 'CPU' THEN
              value
    END
    How can I make it, that I can have multiple conditions in the Case-Statement?

    I have one time: CPU and VCPU, which should be treated as one item.

    I would expect something like:

    Code:
    CASE type
    WHEN ('CPU' or 'VCPU') THEN
              value
    END
    Is this possible? (Didn't work)

    In general, can I do something like this (analog to like ' ' statement)?

    Code:
    CASE type
    WHEN ('%CPU') THEN
              value
    END
    Thank you very much!

  8. #8
    Join Date
    Aug 2011
    Posts
    25
    ok, my brain already was in holiday...

    Code:
    CASE 
                WHEN (type like '%CPU' or type = 'VCPU')     THEN
                     value
                END

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can't write this.
    Code:
    /* Syntx error */
    CASE type
    WHEN ('CPU' or 'VCPU') THEN
              value
    END
    Alternate solutions may be...
    Example 1:
    Code:
    CASE type
    WHEN 'CPU'  THEN
         value
    WHEN 'VCPU' THEN
         value
    END
    Example 2:
    Code:
    CASE
    WHEN type IN ('CPU' or 'VCPU') THEN
         value
    END
    Example 3:
    Code:
    CASE
    WHEN type LIKE '%CPU' THEN
         value
    END

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But to be honest, the syntax is so easy, that I don't understand why it is working...
    Can you explain in 2 words, I somehow don't get it...
    Although not in 2 words,
    suppose intermidiate result before applying GROUP BY and MAX functions, like...
    Code:
    SELECT id
         , date
         , type
         , value
         ,      CASE type
                WHEN 'CPU'    THEN
                     value
                END
                AS cpu
         ,      CASE type
                WHEN 'MEMORY' THEN
                     value
                END
                AS memory
         ,      CASE type
                WHEN 'DISK'   THEN
                     value
                END
                AS disk
     FROM  table
     WHERE date BETWEEN DATE('2011-01-01') AND current_date
     ORDER BY
           id
         , date
    ;
    Result would be like this...
    Code:
    id    date       type   value CPU   MEMORY DISK
    ----- ---------- ------ ----- ----- ------ -----
    1     2011-01-01 CPU    xxx   xxx   -      -
    1     2011-01-01 MEMORY xxx   -     xxx    -
    1     2011-01-01 DISK   xxx   -     -      xxx
    
    1     2011-01-02 CPU    xxx   xxx   -      -
    1     2011-01-02 MEMORY xxx   -     xxx    -
    1     2011-01-02 DISK   xxx   -     -      xxx
    
    2     2011-01-01 CPU    xxx   xxx   -      -
    2     2011-01-01 MEMORY xxx   -     xxx    -
    
    2     2011-01-03 CPU    xxx   xxx   -      -
    2     2011-01-03 MEMORY xxx   -     xxx    -
    
    3     2011-01-02 CPU    xxx   xxx   -      -
    3     2011-01-02 DISK   xxx   -     -      xxx
    
    3     2011-01-03 CPU    xxx   xxx   -      -
    3     2011-01-03 DISK   xxx   -     -      xxx
    Then "GROUP BY id , date" and apply MAX functions for CPU, MEMORY and DISK columns.
    Note that aggregate function MAX(and also MIN, SUM, COUNT) exclude null values.

  11. #11
    Join Date
    Aug 2011
    Posts
    25
    2 words was an old saw badly translated into english ;-)

    Thank you very much, now it's clear to me, thank you!

    If you are interested:

    Query before: 10 minutes, daterange one month
    Query after: 2 minutes, daterange the same



    Edit: and i have some few results more i missed before but didn't noticed! (would have needed full outer join but then the difference would be even much greater!)

Posting Permissions

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