Hi,

I have one script (session) and it reads data from three huge tables 'tfgld106, tfgld418 and tfgld410' and sends data to report. I used indexes to reference each other and tried to run it. But it ran three days and gave above error. Can any one help me how to tune below query to run faster? These three tables are having records 9, 32, 17 millions. Instead of reading ledger accounts with wide open I simplified to read couple ledger accounts at a time and finish the reading tables 'tfgld418 and tfgld410'. But it's still giving above error. May be if I tune the query it reads faster and won't get above error. Please help me on this..

Thanks,

Input parameters:
ledger accounts - leac.f, leac.t
fiscal year - fyer.f, fyer.t
fiscal period - fprd.f, fprd.t

Query:
(xxx - company no)


function read.main.table()
{
read.company.ledger.accounts(XXX)
}

function read.company.ledger.accounts(domain tcncmp comp.in)
{
leac.tmp = leac.f
repeat
select tfgld008.*
from tfgld008
where tfgld008._index1 > {:leac.tmp}
and tfgld008._compnr = :comp.in
order by tfgld008._index1
as set with 5 rows
selectdo
if tfgld008.leac <= leac.t then
leac.tmp = tfgld008.leac
read.company.gl.transactions(comp.in, leac.tmp)
endif
selectempty
leac.tmp = leac.t
endselect
until (leac.tmp = leac.t)
}

function read.company.gl.transactions(domain tcncmp comp.tmp, domain tfgld.leac leac.i)
{
select tfgld106.*
from tfgld106
where tfgld106._compnr = :comp.tmp
and tfgld106._index3 = {:leac.i}
and tfgld106.fyer between :fyer.f and :fyer.t
and tfgld106.fprd between :fprd.f and :fprd.t
selectdo
select tfgld418.*, tfgld410.*
from tfgld418, tfgld410
where (tfgld418._compnr = :comp.tmp and tfgld410._compnr = :comp.tmp)
and tfgld418._index3 = {:tfgld106.ocmp, :tfgld106.otyp,
:tfgld106.odoc, :tfgld106.olin}
and tfgld418.trdt = :tfgld106.dcdt
and tfgld410._index3 = {tfgld418.fcom, tfgld418.ttyp,
tfgld418.docn, tfgld418.lino,
tfgld418.tror, tfgld418.fitr,
tfgld418.trdt}
and tfgld410.orno = tfgld418.orno
and tfgld410.pono = tfgld418.pono
and tfgld410.sern = tfgld418.sern
and tfgld410.trtm = tfgld418.trtm
selectdo
rprt_send()
selectempty
rprt_send()
endselect

endselect
}

There are a few things you can try:
1. Since the tables are large, it is better to process the data one fiscal year at a time.

2. Since the select for the tfgld106 will in general return a very large resultset, you should use the

as prepared set

clause before the selectdo. This will help with the 2555 (snap-shot too old) error.

3. In the select for tfgld418, use can use the

order by tfgld418._index3

in order to force the system to use the index. This may or may not make a difference to the performance.

[You may have to improve your logic regarding the from and to period, unless you plan to run the session for each year individually. If you are running multiple years, and the period is from middle of the first year to the middle of the last year, then the select as you have defined will not return the required records.] For example, you may have to change the query as follows:

and (100 * tfgld106.fyer + tfgld106.fprd) >= :p.from.period.c
and (100 * tfgld106.fyer + tfgld106.fprd) <= :p.to.period.c

where p.from.period.c and p.to.period.c are appropriately computed from the from and to values of the period and the year.

Thanks for suggestion.. I am rinning this session each year individually. I can test this and let you know.

Thanks,
jdk981

Thanks shah_bs.. It really helped me a lot and finished as faster too. I ran one year with one period it took nearly 7 hours and very Impressive. Now all set. Thank you very much..

Thanks,
jdk981