Hello.
select tditc545.*
from tditc545, tiitm001
where tditc545._index1 inrange {:first.day} and {:last.day}
and tiitm001._compnr = tditc545.ncmp
and tiitm001._index1 = {tditc545.item}
and tiitm001._index8 inrange {:citg.f} and {:citg.t}
selectdo
rprt_send()
endselect

The problem is execution time. The table tditc545 has 80000 rows on one company and 700 on the second and tiitm001 has 40000 on the first and 2000 on the second. If I run the select only on the first company (without the tiitm001._compnr clause), I get a response in 10 seconds. If I run it like it is described above, it runs for 15 minuntes and I cannot understand why.
The table tditc545 has a field called ncmp (company number) and there are items present only on one company and not on the other...

Please help me...

Thank you,
Mihaela.

a (little) speed improvement can be achieved by changing the

and tiitm001._index1 = {tditc545.item}

with

and tditc545.item refers to tiitm001


Have you considered also to change the statement with nested select, like:

select tditc545.*
from tditc545
where tditc545._index1 inrange {:first.day} and {:last.day}
selectdo
select tiitm001.*
from tiitm001
where tiitm001._index1 = {:tditc545.item}
and tiitm001.citg inrange :citg.f and :citg.t
and tiitm001._compnr = :tditc545.ncmp
selectdo
rprt_send()
endselect
endselect

Hi,

Oracle might need the order by statement to improve performance. It seems to help in a lot of selects for larger tables.

Regards,
Klaas

I have tried both your suggestions but they don't improve the running time...
any other ideas, please?... :(

Probleem seems to be in changing company all the time. Would it be possible to do the first select on table tditc545 with an order by on company number field. Then check if company number has been changed and if so, do a db.change.compnr to the new company, subsequently do the second select without using company number (this has been done). This might require some sorting in the report.

You might also try to only send first table to the report, and in the reportscript read item data and check if it has to be printed.

Regards,
Klaas