Hi,

I have the following query in one of our customizations


select tdpur400.* , tdpur401.* , tcibd001.dsca , tdpur906.rdep

from tdpur400 INNER JOIN tdpur401 on tdpur400._index1 inrange {:1} and {:2} and tdpur400.otbp inrange :3 and :4 and tdpur400.ccon inrange :5 and :6 and tdpur400.cofc inrange :7 and :8 and tdpur400.hdst inrange :9 and :10 and tdpur400.odat inrange :11 and :12 and tdpur400.refa inrange :13 and :14 and tdpur401.cwar inrange :21 and :22 and tdpur401.orno = tdpur400.orno and tdpur401.cprj inrange :15 and :16 and tdpur401.item inrange :17 and :18 and (tdpur401.ddta = 0 or tdpur401.ddta inrange :19 and :20) and tdpur401.clyn <> tcyesno.yes INNER JOIN tcibd001 on tdpur401.item = tcibd001.item
LEFT OUTER JOIN tdpur906 on tdpur906.rdep inrange :23 and :24 and tdpur906._index1 = tdpur401._index1


I need to optimize this query. The problem that I see is joining of so many heavy tables. I can simply get the result using nested queries. I am sure of getting some performance benefit, with this approach, thoug it will lead to a lot of code change.

The main query I have with respect to this is will there be a difference if I remove most of the filters from the joins and put it in where clause and only keep the joining conditions in the script. (Refer below script)


select tdpur400.* , tdpur401.* , tcibd001.dsca , tdpur906.rdep

from tdpur400 INNER JOIN tdpur401 on tdpur401.orno = tdpur400.orno INNER JOIN tcibd001 on tdpur401.item = tcibd001.item
LEFT OUTER JOIN tdpur906 on tdpur906._index1 = tdpur401._index1

where tdpur400._index1 inrange {:1} and {:2} and tdpur400.otbp inrange :3 and :4 and tdpur400.ccon inrange :5 and :6 and tdpur400.cofc inrange :7 and :8 and tdpur400.hdst inrange :9 and :10 and tdpur400.odat inrange :11 and :12 and tdpur400.refa inrange :13 and :14 and tdpur401.cwar inrange :21 and :22 and tdpur401.cprj inrange :15 and :16 and tdpur401.item inrange :17 and :18 and (tdpur401.ddta = 0 or tdpur401.ddta inrange :19 and :20) and tdpur401.clyn <> tcyesno.yes and tdpur906.rdep inrange :23 and :24



As according to me putting all the filter condition on the join may be creating the performance issue.

Any suggestions are welcome.

Thanks,

Cyril Chacko

I think nested queries instead of joins will improve the performance. Recently we had good results with that.

Hi,

Sorry, I have been busy with wor. I forgot to close this case.

For this case, I did not break up the query as the query itself was only taking 55% of the time. Runnig a profile on the sessions showed that there were two other queries that were running for a long time as well.

With some inventive thinking I reduced the running time of these function be 60-80%, reducing the time for running the script be 20-40% in all.

Breaking up the query into nested queries would require me to write almost 20-30 additional queries, which was too cumbersome. So was rejected and will only be taken up, if further optimization is required.

Regards,

Cyril Parathazham