Urgent

[14w6119]

Hello

This query is taking 12 minutes to execute because of the correlated subquery.I need to rewrite it so that it takes very less time.Can Some one help please ?

select count(*)


from service_requests sr

, appointment_statuses appst

, appointments app

, service_request_processing srp

, paths p

where sr.service_request_id = app.service_request_id

and app.appointment_id = appst.appointment_id

and sysdate between appst.eff_start_dt and nvl(appst.eff_end_dt, sysdate)

and appst.appointment_status_type in ('Show','Pending')

and not exists ( select 'x'

from service_request_task_statuses srts

, service_request_tasks srt

, invoice_items_log iil

where srt.service_request_id = sr.service_request_id

and srt.task_id = 24

and srt.service_request_task_id = srts.service_request_task_id

and srts.task_status_name = 'Appt.Invoice'

and srts.task_object_pk_value = iil.invoice_items_log_id

and iil.appointment_id = app.appointment_id

and sysdate between srts.eff_start_dt and nvl(srts.eff_end_dt, sysdate)

and srts.task_status_value_type = 'Completed')

and srp.service_request_id = sr.service_request_id

and p.path_id = srp.path_id

and substr(p.path_name, 1, 3) = office.officename;

 

[sem]

Why do you think you need to rewrite this query? Adding hint, creating indexes or even gathering statistics may suit.
But this standalone query doesn't conatain enough raw material to improve performance (current execution plan, indexes avalable, appr. number of records in tables etc)

Regards, Dima

http://seminihin.narod.ru

 

[martyc71]

Use a cursor instead to pass sub query condition