PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Correlated Subqueries Performance Tuning

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.

Add a Comment

Your email address will not be published. Required fields are marked *