Skip to Content
avatar image
Former Member

JOIN performance

Hello.

I deploy TPC-H test database (scale factor 50) on my test virtual machine (SLES 11SP3, 16VCPU, 48GB RAM)

on HANA SPS09 rev. 96 database.

Linear fullscan performance is amazing, but for query's with join's operation got some not optimal performance

For example (little changed  3.sql  script from TPC-H) :

select  TOP 100
        l_orderkey,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        o_orderdate,
        o_shippriority
from
       tpchusr.customer,
       tpchusr.orders,
       tpchusr.lineitem
where
        c_custkey = o_custkey
        and l_orderkey = o_orderkey 
        and c_mktsegment = 'AUTOMOBILE'
group by
        l_orderkey,
        o_orderdate,
        o_shippriority
order by
        revenue desc,
        o_orderdate
   

Tables rows count :

LINEITEM - 300 M

ORDERS - 75 M

CUSTOMER 7.5 M

Sustained average execution time of this query is 35 sec.

I try to create indexes on join columns, create statistics on all tables, use some JOIN-hint's, but with no luck

I collect and attach EXPLAIN_PLAN, PlanViz graph, and output of  "HANA_SQL_StatementHash_KeyFigures.txt"  script.

Will be very appricated if someone can give any ideas for improvment of this query

Thank's

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 16, 2015 at 11:08 PM

    What sort of join-hints did you use?

    Have you tried to have this executed in the OLAP engine?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello, Lars

      Thank you for response.

      1. I try hints JOIN_THRU_AGGR/FILTER/JOIN and JOIN_SIMPLIFICATION but in all cases runtime is 34-36 sec.

      2. I try to use OLAP engine (if i right understand, using hints like USE_OLAP_PLAN or OLAP_PARALLEL_AGGREGATION is enouch). But runtime is increased to 53 sec for both OLAP-hint's.  Also CPU consumption monitoring (with "nmon" utility) show what 90% of runtime in OLAP-mode is doing on only one VCPU and only last 5 sec. is using all 16VCPU.

      When using JOIN-engine CPU consumption also very unbalanced, but in a less degree than in OLAP-engine

      I attach PlanViz graph for execution with USE_OLAP_PLAN hint.

      Thank you!