nested query performance alternatives

Question

From performance perspective , is this the best way to write the following query concerning the nested query :

---

SELECT a.meg,a.currency
FROM alt6sal a 
WHERE  a.meg_code IN (1,2)
AND a.sal_year = (SELECT MAX(ia.sal_year) FROM alt6sal ia WHERE a.emp_num = ia.emp_num )
AND a.sal_mon = (SELECT  MAX(ia.sal_mon) FROM alt6sal ia  WHERE a.emp_num = ia.emp_num AND a.sal_year = ia.sal_year)

Answer 1

If you can avoid correlated subquery, the better the performance, example of non-correlated subquery:

SELECT a.meg,a.currency
FROM alt6sal a 

join 
(
    select ia.emp_num, max(ia.sal_year) as sal_year_max
    from alt6sal ia
    group by ia.emp_num
) the_year_max
on a.emp_num =  the_year_max.emp_num and a.sal_year = the_year_max.sal_year_max

join 
(
    select ia.emp_num, ia.sal_year, max(ia.sal_mon) as sal_mon_max
    from alt6sal ia
    group by ia.emp_num, ia.sal_year
) the_month_max
on a.emp_num = the_month_max.emp_num and a.sal_year = the_month_max.sal_year
and a.sal_mon = the_month_max.sal_mon_max

WHERE  a.meg_code IN (1,2)

Analogous non-correlated JOINS for OR instead of AND, use LEFT JOIN then filter-in non-null

SELECT a.meg,a.currency
FROM alt6sal a 

left join 
(
    select ia.emp_num, max(ia.sal_year) as sal_year_max
    from alt6sal ia
    group by ia.emp_num
) the_year_max
on a.emp_num =  the_year_max.emp_num and a.sal_year = the_year_max.sal_year_max

left join 
(
    select ia.emp_num, ia.sal_year, max(ia.sal_mon) as sal_mon_max
    from alt6sal ia
    group by ia.emp_num, ia.sal_year
) the_month_max
on a.emp_num = the_month_max.emp_num and a.sal_year = the_month_max.sal_year
and a.sal_mon = the_month_max.sal_mon_max

WHERE  a.meg_code IN (1,2)
       and 
       (the_year_max.ia_emp_num is not null 
        or the_month_max.ia_emp_num is not null)

Answer 2

You can try this -

SELECT meg, currency
FROM
(
SELECT a.meg,a.currency, 
dense_rank() over (PARTITION BY a.emp_num ORDER BY a.sal_year desc) year_rank,
dense_rank() over (PARTITION BY a.emp_num ORDER BY a.sal_mon desc) mon_rank
FROM alt6sal a 
WHERE  a.meg_code IN (1,2)
)
WHERE year_rank = 1
AND mon_rank = 1;

Answer 3

The performance of any suggestion here will depend a lot of :
- version of your Informix engine (syntax probably will not work with version <11.50)
- filtered Indexes
- amount of data
- table/indexes statistics updated

This will force the database create a temporary table first with all sal_year and then join with the main table...

Suggestion 1)

SELECT a.meg,a.currency
FROM alt6sal a
    ,(SELECT emp_num, MAX(ia.sal_year) sal_year FROM alt6sal ia group by 1 ) as a2
WHERE  a.meg_code IN (1,2)
AND a.sal_year = a2.sal_year and a.emp_num = a2.emp_num
AND a.sal_mon = (SELECT  MAX(ia.sal_mon) FROM alt6sal ia  WHERE a.emp_num = ia.emp_num AND a.sal_year = ia.sal_year)

Suggestion 2)

SELECT a.meg,a.currency
FROM alt6sal a
    ,(SELECT aa.emp_num, MAX(aa.sal_year) sal_year FROM alt6sal aa where aa.meg_code in (1,2) group by 1 ) as a2
    ,(SELECT ab.emp_num, ab.sal_year, max(ab.sal_mon) sal_mon  FROM alt6sal ab  where ab.meg_code in (1,2)group by 1,2 ) as a3
WHERE  a.meg_code IN (1,2)
AND a.sal_year = a2.sal_year and a.emp_num = a2.emp_num
and a.sal_mon  = a3.sal_mon AND a.sal_year = a2.sal_year and a.emp_num = a2.emp_num
;