SQL sub query runs too slow.
Hi all
I have this query that runs very slow, the prob;em seems to be related to this part of the query
*(select TO_CHAR(MAX(t.PLAYED), 'dd.mm.yyyy HH:MI:SS') from ticket t where t.GAME_PARTICIPANT = p.ID) as LAST_ASP*
I thought of rewriting that part of the query to a subquery in the where clause somewhere, but not sure how to do that and still present it's column value in the output as per "LAST_ASP".
and not sure if it will be more efficient to do so.
Currently, I think this colum subquery will run execute for every condition met and it seems to be the bottle neck in the query, took more than 30 Mins.
The number of rows in this table is about 30 Million.
Select p.ID as PARTICIPANTID,
decode(concat(pp.SALUTATION_CODE,p.LANG),'1de','Dear Sir','2de','Dear Madam','1fr','Cher Monsieur','2fr','Ch<E8>re Madame','1it','Egregio Signor','
2it','Gentile Signora','NA') as SALUTATION_TEXT,
pp.FIRSTNAME,
pp.LASTNAME,
decode(p.VERIFIED, 0, p.EMAIL, 1, p.EMAIL, 2, p.ISL_EMAIL, 3, p.EMAIL,'NA') as EMAIL,
p.LANG, TO_CHAR(pp.BIRTHDAY, 'dd.mm.yyyy') as BIRTHDAY,
pa.STREET as Street, pa.HOUSENUMBER as NR,
pa.ZIP as PLZ, pa.CITY as ORT,
(select TO_CHAR(MAX(t.PLAYED), 'dd.mm.yyyy HH:MI:SS') from ticket t where t.GAME_PARTICIPANT = p.ID) as LAST_SPA,
decode(p.verified, 0, 'verified', 1, 'Not verified', 2, 'Not Migrated', 3, 'Rejected', 4, 'changed to another participant', 5,
'MoRo-Migrated', 6, 'ISL-User Asset', 'unknown') verified,
decode(p.state, 0, 'eligible', 1, 'closed', 2, 'blocked', 'unknown') state
from PARTICIPANT p
left join PERSINFO pp on p.ID = pp.PLAYER_ID
left join ADDRESS pa on p.ID = pa.PLAYER_ID
where p.ENABLED = 1
AND pp.STATUS = 1
AND pa.STATUS = 1
AND TO_CHAR(pp.BIRTHDAY, 'mm') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -1), 'mm')
Your help in solving this will be very much appreciated.
Thank you