CODEWITHSUNDEEP
sqlperformancesubqueryinformix
Anyname Donotcare
Anyname Donotcare

Reputation: 11403

How to replace correlated subquery with non-correlated subquery?

i have a query with bad performance because of correlated subquery and i want to replace it wiz non-correlated subquery .How to do this :

My query :

select a.emp_num, a.name , b.cont_date 
from main_emp a INNER JOIN main_cont b
ON a.emp_num = b.emp_num AND a.calc_year = b.calc_year 

join 
(
    select emp_num,calc_year, max(bb.cont_date) AS max_date from main_cont bb
    GROUP BY emp_num,calc_year
)   bb_max
on a.emp_num =  bb_max.emp_num and a.calc_year = bb_max.calc_year and b.cont_date = bb_max.max_date

where
( 0 = ( select count(*) from main_serv x where x.emp_num = a.emp_num and x.calc_year = a.calc_year ) 
    or b.cont_date >  ( select max(y.ser_date) from main_serv y where y.emp_num = a.emp_num and y.calc_year = a.calc_year) ) -- The problem here
and a.calc_year = 2015
order by 1

Now i want to convert this subquery :

  ( 0 = ( select count(*) from main_serv x where x.emp_num = a.emp_num and x.calc_year = a.calc_year ) 
        or b.cont_date >  ( select max(y.ser_date) from main_serv y where y.emp_num = a.emp_num and y.calc_year = a.calc_year) )

To

Join like this :

 join 
    (
        select emp_num,calc_year, max(bb.cont_date) AS max_date from main_cont bb
        GROUP BY emp_num,calc_year
    )   bb_max
    on a.emp_num =  bb_max.emp_num and a.calc_year = bb_max.calc_year and b.cont_date = bb_max.max_date

But i don't how to do this because i have ((0 =(subquery) OR (subquery))

Upvotes: 2

Views: 1460

Answers (2)

Andriy M
Andriy M

Reputation: 77707

I believe this part of your WHERE clause

( 0 = ( select count(*) from main_serv x where x.emp_num = a.emp_num and x.calc_year = a.calc_year ) 
    or b.cont_date >  ( select max(y.ser_date) from main_serv y where y.emp_num = a.emp_num and y.calc_year = a.calc_year) )

could be rendered as

There are no rows in main_serv
OR
There are no rows in main_serv where ser_date is equal to or greater than b.cont_date.

I believe the second part of the disjunction covers the complete condition, because when there are no rows in main_serv, there will certainly be no rows matching the second part of the condition, and if there are any rows in main_serv, it will be the second part of the condition that will determine the result. So, I would rewrite the entire disjunctive part like this:

not exists (
  select *
  from main_serv as x
  where x.emp_num = a.emp_num
    and x.calc_year = a.calc_year
    and x.ser_date >= b.cont_date
)

This subquery is still correlated to the main query but it will not perform any aggregation and may overall be an improvement over your version anyway.

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Like this:

SELECT 
  a.emp_num, 
  a.name, 
  b.cont_date 
FROM main_emp a 
INNER JOIN main_cont b ON a.emp_num = b.emp_num AND a.calc_year = b.calc_year 
INNER JOIN 
(
    SELECT emp_num,calc_year, max(bb.cont_date) AS max_date 
    from main_cont bb
    GROUP BY emp_num,calc_year
)   bb_max
on a.emp_num =  bb_max.emp_num and a.calc_year = bb_max.calc_year 
                               and b.cont_date = bb_max.max_date
INNER JOIN 
( 
   SELECT emp_num, calc_year, count(*) AS count, max(ser_date) AS MaxDate 
   from main_serv 
   WHERE calc_year IS NOT NULL
   GROUP BY emp_num, calc_year
) x ON x.count = 0 OR b.cont_date > x.MaxDate 
    AND x.emp_num = a.emp_num 
    AND x.calc_year = a.calc_year
where a.calc_year = 2015
order by 1

Upvotes: 1

Related Questions