cancel
Showing results for 
Search instead for 
Did you mean: 

Performance about inner join in HANA

Former Member
0 Kudos

Hi All,

     Please explain the performance of inner join(sub-query) for more than 4 tables in HANA. I want to create a dynamic inner join(Sub-query) in HANA, what will be the performance, if inner join happens more than 4 tables?

Which is advisable Inner Join or Sub-query ?

Thanks & Regards,

Yesudoss P

Message was edited by: Tom Flanagan

Message was edited by: Tom Flanagan

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Krishna,

Thanks for your reply.

Basically i want to know the performance of inner join for more than 4 tables. I am going to write the inner joins for more than four or five tables, whether it will affect the DB server or not?

Thanks & Regards,

Yesudoss P

Former Member
0 Kudos

Hello Yesudoss,

Be it 4 or more tables, i would still suggest you to go with the join.

Here are a couple of case to illustrate how it works,

case_1)SELECT emp_name

FROM emp

WHERE emp_id IN (SELECT emp_id FROM dept WHERE

dept_id= 2);

case_2)SELECT emp.emp_name

FROM emp INNER JOIN dept ON emp.dept_id=

dept.dept_id

WHERE dept.deptid= 2;

In the above scenario if i want to see the list of employees of a particular department the query processor will stop the search as soon as it finds the first one but in the first case the the sub query generates multiple rows for the emp_name

This way if the query has 4 (or more) tables included the scan of all those tables would be a little expensive.

Lets see what experts opinions are, on this issue.

Regards,

Krishna.

rindia
Active Contributor
0 Kudos

Hi Yesudoss,

Performance would be better, if modeling is done with some care:

1. Join all your corresponding tables in Attribute view.

2. If some tables do not have common attribute then use another Attribute view.

3. Apply filter in Attribute view, if applicable, so that filter can be pushed down at the earliest level.

4. Now include your Attribute views to join with Analytic view.

After you are done with creating information view, run the PlanViz and check how your data is processing and which engine is consuming what time.

Performance can be still improved depending on your modeling requirements.

Details for PlanViz can be found in "Show me the timelines, baby!"  by Lars Breddemann.

Regards

Raj

Former Member
0 Kudos

Hello Yesudoss,

I basically didn't get your question "inner join(sub-query) ??" is it dat you want to append a join in a sub query or you are trying to compare the performance of a join and a sub-query!!

I would suggest you to go with the join, because of the columnar storage the runtime for nested Selects is much much more than the runtime for the joins.

Regards,

krishna