Hi all,
I´m having some performance problems with the following query:
I´m trying to find what order (stock) is related for each integration transaction in table tfgld106



function refr.integracion()
{
select tfgld417.*
from tfgld417
where tfgld417.leac =:tfgld106.leac
and tfgld417.trdt =:tfgld106.dcdt
and tfgld417.btno =:tfgld106.obat
and tfgld417.lino =:tfgld106.olin
order by tfgld417.leac
as set with 1 rows
selectdo
|***** If the above conditions are filled the order in tfgld418 are easily find.
select tfgld418.*
from tfgld418
where tfgld418._index1 = {:tfgld417.ocom, :tfgld417.tror, :tfgld417.fitr, :tfgld417.trdt, :tfgld417.trtm}
and tfgld418.ttyp =:tfgld106.otyp
and tfgld418.docn =:tfgld106.odoc
and tfgld418.lino =:tfgld106.olin
as set with 1 rows
selectdo
ref.orno = str$(tfgld418.orno)
selectempty
ref.orno = "tfgld418 Ref no encontr."
endselect
selectempty
|****** If not, I run again the query without field tfgld106.dcdt

select tfgld417.*
from tfgld417
where tfgld417.leac =:tfgld106.leac
and tfgld417.btno =:tfgld106.obat
and tfgld417.lino =:tfgld106.olin
order by tfgld417.leac
as set with 1 rows
selectdo
select tfgld418.*
from tfgld418
where tfgld418._index1 = {:tfgld417.ocom, :tfgld417.tror, :tfgld417.fitr, :tfgld417.trdt, :tfgld417.trtm}
and tfgld418.ttyp =:tfgld106.otyp
and tfgld418.docn =:tfgld106.odoc
and tfgld418.lino =:tfgld106.olin
as set with 1 rows
selectdo
ref.orno = str$(tfgld418.orno)
selectempty
ref.orno = "tfgld418 Ref no encontr."
endselect
endselect
endselect
}



My problem resides when, in section selectempty from outer query, field tfgld417.trdt not match with field tfgld106.dcdt. And then I do a search again without this restriction.

This method (not usign indexes) gives me a poor performance of the query but finally the stock order is found correctly.

I have tryied several combinations of field to use with the only one index in the table tfgld417 but, every single try, the query misses the search of stock order.

On the dababase side, If I use, no index, oracle do a full scan for the table, and gives me a cost of 10442. When I use an index, oracle gives me a cost of 922. That´s why I´m suspecting that my problem resides on tfgld417.


Any ideas?

Thank you in advance.

Well the first thing that popped into my head - is create a back end index. While I don't like this it is possible. Our oracle dbs's have trapped a few queries and have built some indexes in the database to help in a situation like this. I would typically recommend creating a new index on the table, but I am not that familiar with the tfgld tables and do not know what is feasible. In our case it was mainly for programs(like data warehouse) that reach into the baan schema to extract data. So indexes we do not need for baan itself.

Hi Mark,
I have followed your suggestion about to create a new index on db side.
Works like a charm.

Thank you.