The tdsls045 table consist of around 6,10,000 records.

I am in process of developing a cusomized sales related report wherein one of the input criteria is invoice date.

The following basic query takes approximately 4-5 minutes to fetch the first record,which is very very slow. I checked it through (-- -set TT_SQL_TRACE=0240 ) option.
All subsequent records are fetched in resonable time. I did not find any index which includeds the item and invoice date fields hence no index is suitable.

To improve the speed I also optimized tdsls045 and tiitm001 table at database level (in informix) but the time taken to fetch the first record is still very slow.
select tdsls045.item, tdsls045.cwar, tdsls045.dqua, tdsls045.pric
tiitm001.dsca, tiitm001.copr
from tdsls045, tiitm001
where tdsls045.item inrange :item.f and :item.t
and tdsls045.cwar inrange :warehouse.f and :warehouse.t
and tdsls045.invd inrange :desp.date.f and :desp.date.t
and tdsls045.item refers to tiitm001
order by tdsls045.item
selectdo
rprt_send()
endselect

Any suggestions as to how to speed up the query ....?

Hi Vineet

Try this

select tdsls045.item, tdsls045.cwar, tdsls045.dqua, tdsls045.pric
tiitm001.dsca, tiitm001.copr
from tdsls045, tiitm001
where tdsls045._index8 inrange {" ", :item.f} and {"ZZZZZZ", :item.t}
and tdsls045.cwar inrange :warehouse.f and :warehouse.t
and tdsls045.invd inrange :desp.date.f and :desp.date.t
and tdsls045.item refers to tiitm001
order by tdsls045.item
selectdo
rprt_send()
endselect

I have modified the item selection part by using index 8.

Regards

Hi,

I have already tried index8 earlier but there is no improvement.

In general I find that, other than index1 all other indexes are relatively slow.

Yesterday when I was viewing some records in tdinv700 through ttaad4500, when I used index3 and tried to browse to next record it took almost 10 mins to go to next record.

Would like to add that going to the next record is not much of problem, but going to previous record is very very slow.

Indeed in GTM , duplicate indices take very high time independent of database driver . However in code if the sql uses duplicate index , it's no problem.

In ur case , using index 8 of sls045 may not be helping bcos user must be inputting invoice date leaving from to item global values.

If u notice almost all indices in Baan standard ar unique indices (with exceptions like tdinv700 3rd index) .

If viewing records with ttaad4500 is too slowly maybe you must rebuild the index.

We have added a few extra indices onto tdsls045, one of them being invoice date, to speed up certain reports.

This has definitely speeded them up.

Ed

p.s. our tdsls045 has 150,000 records.

We use the index 5 with ckor = 3 of tdsls051 table to retreive tdsls045 invoiced records.

This works very well for us. Our RDBMS is oracle 8 and we have close to 2 mil tdsls045 records and 5 mill tdsls051 records.