Improving of a query (multiple joins)

odin568 · #1 (**permalink**) 08-31-11, 06:04

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!

Stealth_DBA · #2 (**permalink**) 08-31-11, 06:22

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
;

odin568 · #3 (**permalink**) 08-31-11, 06:36

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? :-)

Stealth_DBA · #4 (**permalink**) 08-31-11, 06:41

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.

odin568 · #5 (**permalink**) 08-31-11, 06:46

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

tonkuma · #6 (**permalink**) 08-31-11, 08:59

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
;

odin568 · #7 (**permalink**) 08-31-11, 09:51

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!

odin568 · #8 (**permalink**) 08-31-11, 09:59

ok, my brain already was in holiday...

Code:

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

tonkuma · #9 (**permalink**) 08-31-11, 10:19

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

tonkuma · #10 (**permalink**) 08-31-11, 10:29

Quote:

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.

odin568 · #11 (**permalink**) 08-31-11, 10:35

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!)

Internet Services

Web Development

Digital Marketing

Consumer Tech