SQL Fine Tuning

January 4, 2012 at 01:56:02
Specs: Windows XP
How do I Fine Tune the below two queries which is cosuming lots of time to execute. This is being used in OTM application.

i) The buffer gets is as high as 154,326.88 per execution for the first query

SELECT COUNT(tfa.shipment_gid) FROM shipment s, tesco_fresh_appointments tfa, shipment_refnum r WHERE s.shipment_gid=r.shipment_gid AND s.shipment_gid=tfa.shipment_gid AND r.shipment_refnum_qual_gid=:"SYS_B_0" AND r.shipment_refnum_value=:1 AND s.is_to_be_held=:"SYS_B_1" AND tfa.is_approved!=:"SYS_B_2" AND tfa.status=:"SYS_B_3" AND tfa.appointment_time BETWEEN to_date(:2, :"SYS_B_4") -:"SYS_B_5" AND to_date(:3, :"SYS_B_6") +:"SYS_B_7" AND tfa.wk_day=(SELECT wk_day FROM tesco_fresh_appointments WHERE shipment_gid=:4) AND s.servprov_gid=(SELECT servprov_gid FROM shipment WHERE shipment_gid=:5) AND s.dest_location_gid=(SELECT dest_location_gid FROM shipment WHERE shipment_gid=:6)


ii) For the below query the cpu time is high as 29.47 second time per execution and Disk read is 1,856.14 per execution

select COUNT(V.VOUCHER_GID) from voucher v, invoice I, INVOICE_SHIPMENT SI, SHIPMENT S where v.invoice_gid = i.invoice_gid AND SI.INVOICE_GID=I.INVOICE_GID AND SI.SHIPMENT_GID=S.SHIPMENT_GID AND v.amount_to_pay_gid in (:"SYS_B_00", :"SYS_B_01") and v.domain_name = :"SYS_B_02" and v.amount_to_pay != :"SYS_B_03" and EXISTS (select sa.servprov_gid from servprov_alias sa where sa.servprov_gid = i.servprov_alias_value and sa.servprov_alias_qual_gid = :"SYS_B_04") and NOT EXISTS (select servprov_gid from servprov_alias sa where sa.servprov_gid = i.servprov_alias_value and sa.servprov_alias_qual_gid = :"SYS_B_05" and sa.ALIAS IN (:"SYS_B_06", :"SYS_B_07", :"SYS_B_08", :"SYS_B_09", :"SYS_B_10", :"SYS_B_11", :"SYS_B_12", :"SYS_B_13")) and TO_CHAR(utc.get_local_date(V.INSERT_DATE, s.dest_location_gid), :"SYS_B_14")=:"SYS_B_15"


See More: SQL Fine Tuning

Report •

#1
January 4, 2012 at 12:23:20
I only have time to look at the first one. First, have you looked at the indexes??

Second, if you can eliminate the subselects you might be able to improve performance. I cut down the 3 subselects to two. You might be able to do better:

# untested
SELECT COUNT(tfa.shipment_gid) FROM shipment s, tesco_fresh_appointments tfa, shipment_refnum r WHERE s.shipment_gid=r.shipment_gid AND s.shipment_gid=tfa.shipment_gid AND r.shipment_refnum_qual_gid=:"SYS_B_0" AND r.shipment_refnum_value=:1 AND s.is_to_be_held=:"SYS_B_1" AND tfa.is_approved!=:"SYS_B_2" AND tfa.status=:"SYS_B_3" AND tfa.appointment_time BETWEEN to_date(:2, :"SYS_B_4") -:"SYS_B_5" AND to_date(:3, :"SYS_B_6") +:"SYS_B_7" AND tfa.wk_day=(SELECT aa.wk_day FROM tesco_fresh_appointments aa, shipment bb WHERE aa shipment_gid=:4) AND s.servprov_gid= bb.servprov_gid and bb.shipment_gid=:5) AND s.dest_location_gid=(SELECT dest_location_gid FROM shipment WHERE shipment_gid=:6)


Report •
Related Solutions


Ask Question