HI,
I got a query that needs optimizing, but after reading some articles on optimizing queries I still cant see a manner in which to optimize this query.
The main problem with the query is that it needs to get a max date from a table, grouped by 5 other columns from that table, using the result to determine a price for that group on those max dates.....
Hoping that someone can help me make this query execute a bit faster. The query.
I got a query that needs optimizing, but after reading some articles on optimizing queries I still cant see a manner in which to optimize this query.
The main problem with the query is that it needs to get a max date from a table, grouped by 5 other columns from that table, using the result to determine a price for that group on those max dates.....
Hoping that someone can help me make this query execute a bit faster. The query.
Code:
select ap_relatienr, ap_rekening_soort, ap_rekening_munts, ap_saldo_positie,
ap_symbool, ap_optietype, ap_expiratiedatum, ap_exerciseprijs,
be_symbool, be_optietype, be_expiratiedatum, be_exerciseprijs,
hq.kr_datum, hq.kr_symbool, hq.kr_midden, hq.kr_soort, hq.kr_optietype,
hq.kr_exerciseprijs, hq.kr_expiratiedatum, hq.kr_tijd
from
(
epp40bnck.aktuele_posities left outer join epp40bnck.beleggingsinstrument
on ap_symbool = be_symbool and
ap_optietype = be_optietype and
ap_expiratiedatum = be_expiratiedatum and
ap_exerciseprijs = be_exerciseprijs
)
left outer join
(
SELECT mq.kr_datum, mq.kr_symbool, mq.kr_midden,
mq.kr_soort, mq.kr_optietype, mq.kr_exerciseprijs,
mq.kr_expiratiedatum, mq.kr_tijd
FROM epp40bnck.koersen mq JOIN
(
SELECT MAX(kr_datum) AS
sq_datum, kr_symbool, kr_soort, kr_optietype,
kr_exerciseprijs, kr_expiratiedatum
FROM epp40bnck.koersen sq
WHERE kr_soort = 'SLOT'
GROUP BY kr_symbool, kr_soort, kr_optietype,
kr_exerciseprijs, kr_expiratiedatum
)
sq ON mq.kr_datum = sq_datum
AND mq.kr_soort = sq.kr_soort
AND mq.kr_symbool = sq.kr_symbool
AND mq.kr_optietype = sq.kr_optietype
AND mq.kr_exerciseprijs = sq.kr_exerciseprijs
AND mq.kr_expiratiedatum = sq.kr_expiratiedatum
WHERE mq.kr_soort = 'SLOT'
) hq
on ap_symbool = hq.kr_symbool and
ap_optietype = hq.kr_optietype and
ap_expiratiedatum = hq.kr_expiratiedatum and
ap_exerciseprijs = hq.kr_exerciseprijs
where ap_relatienr=ap_ref_relatie
and ap_relatienr >= 100000
and ap_relatienr < 600000 and ap_saldo_positie <> 0
and ap_rekening_soort in (0,1000)