Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

Former Member
Not what you were looking for? View more on this topic or Ask a question