Performance on large table join return top result

[former_member269047]

Hi all,

      We're execution POC on a public sector customer.

      In their enviorement, they have a SQL like this:

      select top 100 * from table A join table B on A.party_id=b.party_id  ( No filter here）

      table A: about 40Million records,    table B: 1.2 billion records,   it's in a 6 nodes cluster. each table parttion by HASH 40 across all nodes.

      It take 11 mins to finish this SQL, and query nodes memory almost used up.

      While other Database, such as Oracle can use hint first_rows, this wll return top 100 result without query on all table. It only takes serval seconds on other DB.

      My questions here, does this any Statement optimization in HANA can return top 100 result as quickly as it can.

[Former Member]

Sam I suggest you take advice from the HANA COE if you have a PoC in play. With this setup, you need advice from a HANA expert.

I can't see any realistic business scenario where that query would be used, so whoever is asking for it is just asking it as a science experiment. There are no hints like first_rows on the HANA DB because HANA is optimized for real-world scenarios.

You could always model it as:

select top 100 * from table A join (select top 10000000 * from table B) B on A.party_id=b.party_id

But that's just as fake as your first question (though it will respond much faster...). Best is to instead find the real queries that the business will ask.

However if you have a 6 node cluster then your design is all off. HASH 40 is far too many partitions, you need 6 - one for each node. Also for your master data, don't use a partition. Instead use the REPLICA functionality ALTER TABLE - SAP HANA SQL and System Views Reference - SAP Library

For the scenario you describe, all queries should be sub-second. Check Abani's blog for some interesting advice.