Dear All,

please see the following code. in this script I have debugged it. As sson as select statement is encountered it hangs. then I have changed my script to the IInd option it is working fine. I want to know where i was going wrong in the Ist script.

I want to use the First one because it uses index and it will be faster.
tfgld106 is a standard table and has index5 as tfgld106.dim2, tfgld106.leac,coo2 {which is combined field for document nos etc}

Ist Script.

extern domain tfgld.dimx dim2.f, dim2.t

function get.op.bal()
{
select tfgld106.*
from tfgld106
where tfgld106._index5 inrange {:dim2.f,:tfgld008.leac} and {:dim.t,:tfgld008.leac}
and tfgld106.trun > 0
and tfgld106.dcdt < :date.f
and tfgld106.oyer = :i.year
selectdo
if tfgld106.dbcr = tfgld.dbcr.debit then
run.amnt = run.amnt + tfgld106.amnt
else
run.amnt = run.amnt - tfgld106.amnt
endif
endselect
calculate.bal.amnt()
}

IInd Script

extern domain tfgld.dimx dim2.f, dim2.t

function get.op.bal()
{
select tfgld106.*
from tfgld106
where tfgld106._index3 inrange {:tfgld008.leac} and {:tfgld00.leac}
and tfgld106.dim2 >= :dim2.f and tfgld106.dim2 <= :dim2.t
and tfgld106.trun > 0
and tfgld106.dcdt < :date.f
and tfgld106.oyer = :i.year
selectdo
if tfgld106.dbcr = tfgld.dbcr.debit then
run.amnt = run.amnt + tfgld106.amnt
else
run.amnt = run.amnt - tfgld106.amnt
endif
endselect
calculate.bal.amnt()
}


Thanks and regards to all in advance

:confused:

Hi,

What are the values of dim2.f & dim2.t which are passed to the program? Are you sure they are not full range ? If they span the full range, then the query will surely take a lot of time to return the records.In such cases its is better not to use a index which uses these values but to go for alternate index like in your second query.

Bye

I don't know what your answer is to Bigjack's comment, but he is for sure right; if your ledger account2 selection is a full range, then it makes sense that the session is very slow in performance (thus not hanging like you suggested, just extremely slow) for it will have to perform a full table scan. The table tfgld106 is one of the very large tables in BaaN.

Another remark, just to verify: in your first example you write
where tfgld106._index5 inrange {:dim2.f,:tfgld008.leac} and {:dim.t,:tfgld008.leac}
I assume you mean dim2.t rather than dim.t...?

Regards,
Eli Nager

Thanks Sir,
Yes I Was using it for full range and I have waited for ouer 5 hours. where as the iind case gives output within seconds.

But this means my session will become slow. because i can't use the index5.

Thanks again.
Kuldeep Sharma

Just an idea

Try a order by index5 in the first sql. For my experience the baan optimizer sometimes is crazy and gives a hint to index3 because all your where clauses are in index3 also and the index number is lower... . In your case the result would be a full table scan.
Sometimes the order by-clause helps.

hth

Regards
Christof