Hi,

Below Query takes 2-3min to fetch data. Is there any way to improve the performance of this Query?

Before execution of this query we assign value to 'show_for_one_pur_off' . And also in run time we change this value and executes find.data to retrieve new data.

Base table of this session is tdpur402.

query.extend.select ("tdpur401.*, whinh210.*")
query.extend.from ("tdpur401, whinh210")
query.extend.where ("tdpur402._index1 refers to tdpur401 and " & "tdpur401.oqua > 0 and " &
"tdpur401.orno refers to tdpur400 and " &
"((:show_for_one_pur_off = tcyesno.yes and " &
"tdpur400.cofc = :tdpur043.cofc) or " &
"(:show_for_one_pur_off = tcyesno.no and " &
"tdpur400.cofc <> '""')) and " &
"whinh210._index4 = {whinh.oorg.purchase," & ":tdpur402.orno,:tdpur402.pono,:tdpur402.sqnb } and " & "whinh210.cdck = tcyesno.yes")

In tracemode i found below query for above query extend.

SELECT tdpur402.*, tdpur401.otbp, tdpur401.ddta, tdpur401.ddte,
tdpur401.item, tdpur400.cofc, tdpur401.cwar, tdpur401.oqua,
tdsls401.shpm, tdsls401.ofbp, tdsls401.item, tdsls401.prdt,
tdsls401.oqua, tdsls401.stad, tdpur401.*, whinh210.*
FROM tdpur402, tdpur401, tdpur400, tdsls401, tdpur401, whinh210
WHERE {tdpur402.orno,tdpur402.pono,tdpur402.sqnb} >= {:tdpur402.orno,:tdpur402.pono,:tdpur402.sqnb} and
(tdpur402._index1 refers to tdpur401 and tdpur401.oqua > 0 and tdpur401.orno refers to tdpur400 and
((:show_for_one_pur_off = tcyesno.yes and tdpur400.cofc = :tdpur043.cofc) or (:show_for_one_pur_off = tcyesno.no and tdpur400.cofc <> '"')) and
whinh210._index4 = {whinh.oorg.purchase,tdpur402.orno,tdpur402.pono, tdpur402.sqnb } and whinh210.cdck = tcyesno.yes) and tdpur402.cmbb REFERS TO tdsls401
ORDER BY tdpur402._index1

Regards,
Satish Reddy