Multiple left joins with aggregation on same table causes huge performance hit in HANA

[former_member366416]

I am joining two tables on HANA and, to get some statistics, I am LEFT joining the items table 3 times to get a total count, number of entries processed and number of errors, as shown below.

This is a dev system and the items table has only 1500 items. But the query below runs for 17 seconds. When I remove any of the three aggregation terms (but leave the corresponding JOIN in place), the query executes almost immediately.

I have also tried adding indexes on the fields used in the specific JOINs, but that makes no difference.

Also, the query is only slow if, for the two latter aggregations, ( COUNT DISTINCT RP2.GUID and COUNT DISTINCT RP3.GUID), there are actually records. When the count is 0 (because no items have yet been selected), the query executes quickly.

I am attaching the execution trace for this statement as well: xmlplv.txt (Just change the extension).

select rk.guid, rk.run_id, rk.status, rk.created_at, rk.created_by, 
count( distinct rp.guid ), 
count( distinct rp2.guid ), 
count( distinct rp3.guid )
    from zbsbpi_rk as rk
    left join zbsbpi_rp as rp
      on rp.header = rk.guid
    left join zbsbpi_rp as rp2
      on rp2.header = rk.guid
     and rp2.processed = 'X'
    left join zbsbpi_rp as rp3
      on rp3.header = rk.guid
     and rp3.result_status = 'E'
    where rk.run_id = '0000000010'
    group by rk.guid, run_id, status, created_at, created_by

This is the execution plan of the long-running statement.

This is th execution plan when taking out one of the aggregations:

[lbreddemann]

Looking at the 17 seconds fo the three BWAggregatePops in the slow case, it's clear that there is something else happening, that takes an awful lot of time. Even with thousands of records to aggregate, this plan shouldn't be this slow.

The execution plan might be sub-optimal, but it's not the root cause here!

[former_member366416 · Accepted solution]

I have rewritten the query as below, joining the subsequent left joins on the item table to the original item join (and making it a right join, as there are no other conditions on it anyway) and this has solved the problem:

  select rk.guid, rk.run_id, rk.status, rk.created_at, rk.created_by,
    count( distinct rp.guid ), 
    count( distinct rp2.guid ), 
    count( distinct rp3.guid )
    from zbsbpi_rk as rk
    join zbsbpi_rp as rp
      on rp.header = rk.guid
    left join zbsbpi_rp as rp2
      on rp2.guid = rp.guid
     and rp2.processed = 'X'
    left join zbsbpi_rp as rp3
      on rp3.guid = rp.guid
     and rp3.result_status = 'E'
    where rk.run_id = '0000000010'
    group by rk.guid, run_id, status, created_at, created_by

[BenedictV · Accepted solution]

Hello Martin,

I see that you have taken the first left join and turned it into a inner join. But wont that give you a different result between both the queries.

[lbreddemann · Accepted solution]

Spot on Benedict. This only works if the original query was wrong and not according to the actual data model.

[former_member366416 · Accepted solution]

Hello Benedict, you are right. Not sure how I missed that. When taking the original query and simply changing the first left join to an inner join, it also solves the performance issue. Thanks for spotting that.

It still doesn't explain though, why the three left joins cause the query to take so long. (Just ran it again, took 124 seconds).

It still works though because the results of the first join are a superset of the two subsequent ones.

Moreover, I have now run this a few times and found that the latter query (as in my answer) is consistently faster; ~60ms vs ~150ms.

[lbreddemann]

You might want to check with explain plan or planviz what's happening, when your query runs.

Gut feeling tells me, that the triple outer join is not the best approach here - especially when done before aggregation.

Could be a good idea to aggregate first and join later...

[former_member366416]

Thanks for the reply, Lars. The funny thing is that, as explained, when I take any of the aggregations out, but leave the joins in, it's quick. I just attached the execution trace (I don't know how to read it).

How would you aggregate first and join later? Can you give me an example of what you mean? Thanks, Martin

[lbreddemann]

The first join (the one you simply changed into an INNER JOIN, which changes the meaning of your data model) joins over your grouping column.

So instead of

select rk.guid, rk.run_id, rk.status, rk.created_at, rk.created_by,count(distinct rp.guid )
from zbsbpi_rk as rk
    left outer join zbsbpi_rp as rp
      on rp.header= rk.guid;

You could do

select rk.guid, rk.run_id, rk.status, rk.created_at, rk.created_by, rp.cnt_guid 
from zbsbpi_rk as rk
    left outer join 

(SELECT header, count(distinct guid) as cnt_guid) 
FROM zbsbpi_rp
GROUP BY header) rp
      on rp.header= rk.guid;