JOIN performance

[Former Member]

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

SQL_HASH_figures.txt.zip

EXPLAIN_PLAN.txt.zip

[lbreddemann]

What sort of join-hints did you use?

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

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