hello,

I am working with session PDSO (tdsls4223m000) in Greig (Baan 5b). The session takes too long to process even if I have only one SO to process.

I did run the scripts in profiler mode and came to know that the function process.sales.orders() is taking long time.

The function has the following SQL in it, which is causing the issue.

Can any body suggest how to improve/modify the query for better performance?

If you need more information please let me know.

SQL Query

select tdsls401.*, tdsls402.*, tdsls400.*, tdsls060.wpos,
tccom110.*, tdisa001.*, tcibd001.*, tcmcs002.*, tdsls094.*
from tdsls401 for update, tdsls402, tdsls400, tdsls060,
tccom110, tdisa001, tcibd001, tcmcs002, tdsls094
where tdsls401._index5 = {PROC.DEL.SLS}
and tdsls401.ofbp inrange :ofbp.f and :ofbp.t
and tdsls401.orno inrange :orno.f and :orno.t
and tdsls401.pono inrange :wf.pono.f and :wf.pono.t
and tdsls401.sqnb inrange :wf.sqnb.f and :wf.sqnb.t
and tdsls060._index1 = {tdsls400.sotp, PROC.DEL.SLS}
and tdsls401.ofbp refers to tccom110
and tdsls401.item refers to tdisa001
and tdisa001.item refers to tcibd001
and tdsls400.ccur refers to tcmcs002
and tdsls400.sotp refers to tdsls094
and tdsls401.pmsk(tdsls060.acno;1) = "y"
and ((tdsls401.ttyp > "" and
tdsls401.invn > 0) or
(tdsls401.dqua = 0 and
tdsls401.bqua = tdsls401.oqua and
tdsls401.oqua <> 0) or
(tdsls094.cnsr = tcyesno.yes and
tdsls401.dldt <> 0) or
(tdsls094.reto = tcyesno.yes and
tdsls401.dldt <> 0 and
tdsls401.oqua <> 0 and
tdsls401.dqua = 0 and
tdsls401.bqua = 0))
and tdsls401.oltp <> tdgen.oltp.total
and tdsls401.cmba refers to tdsls402 unref clear
and tdsls401.orno refers to tdsls400
and tdsls400.sotp inrange :sotp.f and :sotp.t
and tdsls400.cofc inrange :cofc.f and :cofc.t
order by tdsls401._index5 with retry
selectdo
endselect


Thank you very much,
Babu

When you are selecting one or few orders, this query will indeed take very long, because index5 is by step, BP, order... Rather select by index1 which is by order number. In fact, the query has been optimized already in standard BaaN with solution number 136331 in Jul-2003. This is the optimized version:


select tdsls401.*, tdsls402.*, tdsls400.*, tdsls060.wpos,
tccom110.*, tdisa001.*, tcibd001.*, tcmcs002.*, tdsls094.*
from tdsls401 for update, tdsls402, tdsls400, tdsls060,
tccom110, tdisa001, tcibd001, tcmcs002, tdsls094
where tdsls401._index1 inrange {:orno.f, :wf.pono.f, :wf.sqnb.f}
and {:orno.t, :wf.pono.t, :wf.sqnb.t}
and tdsls401._index5 inrange {PROC.DEL.SLS, :ofbp.f}
and {PROC.DEL.SLS, :ofbp.t}
and tdsls060._index1 = {tdsls400.sotp, PROC.DEL.SLS}
and tdsls401.ofbp refers to tccom110
and tdsls401.item refers to tdisa001
and tdisa001.item refers to tcibd001
and tdsls400.ccur refers to tcmcs002
and tdsls400.sotp refers to tdsls094
and tdsls401.pmsk(tdsls060.acno;1) = "y"
and ((tdsls401.ttyp > "" and
tdsls401.invn > 0) or
(tdsls401.dqua = 0 and
tdsls401.bqua = tdsls401.oqua and
tdsls401.oqua <> 0) or
(tdsls401.dqua = 0 and
tdsls401.bqua = 0 and
tdsls401.oqua <> 0 and
tdsls401.dldt <> 0) or
(tdsls094.cnsr = tcyesno.yes and
tdsls401.dldt <> 0) or
(tdsls094.reto = tcyesno.yes and
tdsls401.dldt <> 0 and
tdsls401.oqua <> 0 and
tdsls401.dqua = 0 and
tdsls401.bqua = 0))
and tdsls401.oltp <> tdgen.oltp.total
and tdsls401.cmba refers to tdsls402 unref clear
and tdsls401.orno refers to tdsls400
and tdsls400.sotp inrange :sotp.f and :sotp.t
and tdsls400.cofc inrange :cofc.f and :cofc.t
order by tdsls401._index1 with retry


This code will run fast. However, since this is already implemented in standard Baan, I advice you to install solution 136331.


Regards,
Eli Nager

Thank you very much for the information.

Regards, Babu