How would you re-write this code(not mine). tpppc215.tetd come from the record being processed, the same with the tdpur046.ninv and tfacp200.appr. To me it looks like this code would always process every tpppc215 record for that project - it may or may not set the flag.

select tpppc215.cdoc
from tpppc215
where tpppc215._index1 = {:tppdm600.cprj}
and (tpppc215.tetd = tpppc.tetd.invoice or
tpppc215.tetd = tpppc.tetd.order.invoice or
tpppc215.tetd = tpppc.tetd.pipo.a)
selectdo
if tpppc215.tetd = tpppc.tetd.pipo.a and
tfacp200.appr = tfacp.matc.pur then
flag = 1
else
o.ninv = str$(tdpur046.invn)
if o.ninv = shiftl$(strip$(tpppc215.cdoc)) and
tfacp200.appr =tfacp.matc.pur then
flag = 1
endif
endif
endselect
if flag = 1 then
paid = i.amoc * ( total.paid / (total.paid + total.unpaid))
unpaid = i.amoc * ( total.unpaid / (total.paid + total.unpaid))
else
paid = total.paid
unpaid = total.unpaid
endif endif


here is what I did

if tfacp200.appr = tfacp.matc.pur then
o.ninv = str$(tdpur046.invn)
| The tpppc215.tetd comes from the tpppc215 record being process. Since it is not part of the select lets
| move it outside the select.
if tpppc215.tetd = tpppc.tetd.pipo.a then
flag = 1
else
| Now lets look directly for o.ninv, but only the first row
select tpppc215.cdoc
from tpppc215
where tpppc215._index1 = {:tppdm600.cprj}
and (tpppc215.tetd = tpppc.tetd.invoice or
tpppc215.tetd = tpppc.tetd.order.invoice or
tpppc215.tetd = tpppc.tetd.pipo.a)
and tpppc215.cdoc = :o.ninv
as set with 1 rows
selectdo
flag = 1
endselect
endif
endif
if flag = 1 then
paid = i.amoc * ( total.paid / (total.paid + total.unpaid))
unpaid = i.amoc * ( total.unpaid / (total.paid + total.unpaid))
else
paid = total.paid
unpaid = total.unpaid
endif

Any better suggestions? Did I miss something?

From what I can see by looking at the data in our system, tpppc215.cdoc is actually a concatenation of Purchase Order Number and Position, separated by a dash, for example, 652537-1, from the Purchase Order Line record.

So, the condition o.ninv = shiftl$(strip$(tpppc215.cdoc) is NEVER going to return TRUE, because o.ninv (from tdpur046.ninv) is actually the Accounts Payable Document Number which is an entirely different number.

Maybe if you can explain a bit more what the flag is all about - a functional definition maybe - at the best of times, tpppc215 is not a good table to read for any reason (!). There must be other ways to determine the flag.

However, if you do have to stick to tpppc215, then a possible entry point from tfacp200 would be to use tfacp200.orno and use _index5 of tpppc215 - this will constrain records in tpppc215 to at least the purchase order number under consideration - still could be many records.

Hi Mark,

You are using the field tpppc215.tetd before selection of table and i think this should be after the selection of table tpppc215 and you are checking o.ninv even if the tpppc215.tetd = tpppc.tetd.pipo.a which is not in the original code.


Regards,
Sai Prasad.

Check this code.


if tfacp200.appr = tfacp.matc.pur then
o.ninv = str$(tdpur046.invn)
select tpppc215.cdoc
from tpppc215
where tpppc215._index1 = {:tppdm600.cprj}
and ((tpppc215.tetd = tpppc.tetd.invoice or
tpppc215.tetd = tpppc.tetd.order.invoice ) and tpppc215.cdoc = :o.ninv)or
tpppc215.tetd = tpppc.tetd.pipo.a)
as set with 1 rows
selectdo
flag = 1
endselect
endif if flag = 1 then
paid = i.amoc * ( total.paid / (total.paid + total.unpaid))
unpaid = i.amoc * ( total.unpaid / (total.paid + total.unpaid))
else
paid = total.paid
unpaid = total.unpaid
endif

Here is the situation - this code is from tpcin9280m000(Create Billable Cost Reports). We are upgrading to current service pack and to Oracle 10g. The run times almost doubled on this session(not always). Infor provided the code for the session - so my DBA's complained about this query being run repeatedly and getting poor performance. So I can't really make some of the changes suggested. Getting this fixed is our last obstacle to going to production. Everything else seems to be okay.

Shah - I thought the same thing, but I did a review of tdpur046 and tpppc215. Both had receipt numbers that matched that did not have purchase order - line number. Example - tdpur046 has 28000008 and tpppc215 has cdoc 28000008. What I did do was a count to see which was more numerous, but what you said is also what I thought - until I start digging at the data. Not sure if you all use this session or have source code but this is in accumulate.bcr.cost and will get run for what looks like all material costs.

As it is my change netted us about a 30% decrease in run times. I have not reviewed the rest of the code, but am positive there are performance gains other places.

We do not use this part of BAAN Projects, so I have never looked into the session code till today. (I know, it is Sunday, and I am here at work cooking a table modification when nobody else is in my way, so while the re-org is going on, I thought I would take a quick peek into the script).

The function you mention (accumulate.bcr.cost) does not exist in the script we have - so you probably have an 'enhanced' version. My script has Last Modification date as 07-26-1999. SO, I am afraid I cannot connect the links and figure out what the 'flag' business is all about and why use tpppc215 to determine that.

That behind us, for improving performance in general, you may want to consider reviewing each and every where clause where you do not see index being used for access and maybe tweak that. There are plenty of places where tables are read without an index - unless all this is corrected in your version of the script.

Alternatively (before you modify the script at all for indexes), have the DBA do his magic (analyze table, whatever incantations they use ...) and that will possibly help.

As far as the original problem goes - if you can figure out why the 'flag' is being set, you can define a different approach entirely, hopefully one that does not need tpppc215.

Your so lucky. :) There must be a hundred modifications since 1999. :) And some of the queries do not have index fields and most queries do not have complete indexes.

I think this code came about because of something we reported; not sure about that though, but it was a change made in Jul which was what we were waiting for.

Thanks everyone.

Maybe I should have been more clear about index use . Partial number of attributes in an indexed access is not incorrect - it will constrain to a number of records that match the number of attributes supplied (in general resulting in more than one rows), and that may be what the logic requires. The important thing is to use the index. There were so many places in the script where there is no index used at all - for example:

where tpppc215.cprj = :tppdm600.cprj


which could be replace with tpppc215._index1 = {:tppdm600.cprj}

The table tpppc215 at our place has about 8.5 million records, and the original where clause (without the index) possibly would be very slow - though I have not confirmed this.

Also, it may be that there is no index to use.

WOW - we only have about 2 million records. Yeah - most of the queries that have an index use the _index syntax. I just wish there were more fields for the index in the script, so we get fewer records. I am going to search through it again looking at a couple of other queries, about all I can do at this point. What fun it is.