Skip to Content

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

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:

explan1.png (52.2 kB)
explan2.png (69.5 kB)
xmlplv.txt (127.2 kB)
Add comment
10|10000 characters needed characters exceeded

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

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 06, 2017 at 10:20 AM

    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
    
    Add comment
    10|10000 characters needed characters exceeded

    • Martin Ceronio Benedict Venmani Felix

      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.

  • Apr 06, 2017 at 06:59 AM

    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...

    Add comment
    10|10000 characters needed characters exceeded

    • 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;