on 07-30-2014 9:53 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.