hi all
i wanted to fetch the most recent record from sales order (tdsls041 and tdsls040) with last inserted line of an
header(latest header).I have item ,customer
and forecast date to match with at the entry. the below code is working fine but it takes more than 2 minutes.
can anybody guide to optimize this... ?

thanks in advance

select tdsls041.*, tdsls040.orno, tdsls040.cotp
from tdsls041
where tdsls041._index9 = {:isdrp001.item, :isdrp001.cuno}
and tdsls041.cdat.c = :isdrp001.fdat
and tdsls041.oqua > 0
and tdsls040._index1 = {tdsls041.orno}
and tdsls040.cotp = "SLS"
order by tdsls041.orno desc, tdsls041.pono desc

try changing it to:

select tdsls041.*, tdsls040.orno, tdsls040.cotp
from tdsls041,tdsls040
where tdsls041._index9 = {:isdrp001.item, :isdrp001.cuno}
and tdsls041.cdat.c = :isdrp001.fdat
and tdsls041.oqua > 0
and tdsls040._index1 = {tdsls041.orno}
and tdsls040.cotp = "SLS"
order by tdsls041.orno desc, tdsls041.pono desc

Refers to statement could be (a little bit) more efficient:


select tdsls041.*, tdsls040.orno, tdsls040.cotp
from tdsls041, tdsls040
where tdsls041._index9 = {:isdrp001.item, :isdrp001.cuno}
and tdsls041.cdat.c = :isdrp001.fdat
and tdsls041.oqua > 0
and tdsls041.orno refers to tdsls040
and tdsls040.cotp = "SLS"
order by tdsls041.orno desc, tdsls041.pono desc


But I think that improvement will be very little, the main problem is that you give an ordering descending on index1 and the database driver usually uses also this index to get the data, so the index9 you use in select will be not used.

thank you ,

but this is taking same time, can you recode in any different way to optimize it ?

thanks in advance

Try adding "as set with 1 records" if you want to select only 1 record.

Use sls041._index1 in order by clause and select clause , equate the same with sls040.index1.

Or alternatively embed sls040 select in tdsls041 selectdo . Skip same order no checking with sls040 .

Hi Hitesh,
thank you very much, the code is optimized very much.. it has reduced the time from 3 minutes to just 9 to 12 seconds in fetching records.
one thing i wanted to get cleared. what exactly does internally happen by changing to order by index ?
and also i wanted to fetch the last order number and last position of that order number first. Using order by index is working fine, In fetching last position of the order but what if there are multiple orders for the same conditions ?
i wanted to fetch the last order inserted (tdsls041.orno) and last position (tdsls041.pono) of that order . will this work at that case also .. ?

thank you once again for your great help.

Earlier sqL did not generate enough hints for db to use readily available index for 2 tables . The hints generated with that resulted in multiple product (with high % of negative records / rejections at db level) and subsequent separate order by algorithm . When used index in order by , db need not do order by separately.

U can use 'desc' clause with index also . So ur requrement can very well be served with order by tdsls041._index1 desc .