Correlated Subqueries Performance Tuning TDWORLD | October 26, 2013 | Developer Bible, Performance Tuning, Real Time Scenario | No Comments Correlated Subquery – Performance Tuning A correlated subquery (CS) is a subquery whose outer query results are processed a row at a time, against the subquery result. The subquery result is computed for each row processed. a CS eliminates the need for intermediate or temporary tables a CS is fully integrated with global join planning to minimize costs CS is significantly faster than the query using temporary tables Example: 1. Employee with highest salary in each department SELECT L_Na,Sal, D_No FROM test.employee ee WHERE Sal = (SELECT MAX (Sal) FROM test.employee em WHERE ee.D_No=em.D_No); Answer Set: Last_Name Salary_Amount Dept_No stifler 60000 50 pitt 70000 10 young 60000 50 jones 60000 30 paul 70000 70 penn 60000 60 white 75000 90 nelson 70000 80 pitt 45000 100 lucas 75000 20 phips 65000 40 2. Employees whose salary is greater than the department average salary SELECT L_Na, Sal, D_No FROM test.employee ee WHERE Sal > (SELECT AVG (Sal) FROM test.employee em WHERE ee.D_No= em.D_No); Answer Set: Last_Name Salary_Amount Dept_No paul 70000 70 phips 65000 40 shook 65000 10 pitt 70000 10 jones 60000 30 lucas 75000 20 wall 65000 10 Concatenation & Correlated subqueries performance tuning Concatenation allows to retrieve data correlated to the MIN/MAX function in a single pass Example: 1. Employees with highest salary in each department This can be written as using correlated subquery SELECT D_No, Sal, L_Na, F_Na FROM test.employee ee WHERE Sal IN (SELECT MAX (Sal) FROM test.employee em WHERE ee.D_No=em.D_No GROUP BY D_No) ORDER BY D_No; Answer Set: Dept_No Salary_Amount Last_Name First_Name 10 70000 pitt arun 20 75000 lucas frank 30 60000 jones indiana 40 65000 phips carla 50 60000 stifler tom 50 60000 young nikie 60 60000 penn arun 70 70000 paul ak 80 70000 nelson julie 90 75000 white sam 100 45000 pitt mark It can also be written as SELECT D_No, MAX(Sal || ' ' || L_Na || ',' ||F_Na) FROM test.employee GROUP BY D_No ORDER BY D_No ; Answer Set: Dept_No Maximum(((((Salary_Amount||' ')||Last_Name)||',')||First_Nam 10 70000 pitt ,arun 20 75000 lucas ,frank 30 60000 jones ,indiana 40 65000 phips ,carla 50 60000 young ,nikie 60 60000 penn ,arun 70 70000 paul ,ak 80 70000 nelson ,julie 90 75000 white ,sam 100 45000 pitt ,mark Did you observe the two answer sets? Can you tell the difference and how to overcome it? Please leave your answers in comments. Tags:correlated, Correlated Subqueries, Subqueries, teradata, Teradata Correlated Related Posts Queue Table in Teradata No Comments | Nov 19, 2014 PRPD – Partial Redistribution Partial Duplication No Comments | Apr 10, 2016 Teradata Surrogate Key / Identity Key Generation No Comments | Feb 15, 2016 Login Mechanism Syntax in Teradata No Comments | Jan 23, 2015 About The Author tdworld2013 Add a Comment Cancel reply Your email address will not be published. Required fields are marked *Comment:*Name:* Email Address:* Website: Save my name, email, and website in this browser for the next time I comment.