Hello

Given the following code, I want to select all standard items with "SYSTEM" as the first 6 digits of the "search key 1" field, and all sales orders for those paticular items.

Anybody have any ideas on tightening the code to make it run faster?

Originally when I wrote the script I selected projects "" to "" (null to null), so that only non project associated standard items would be selected.

When it was in this form it ran quite fast, about 3 minutes total.

Then I found out sometimes we have project associated standard items that needed to be selected. When I changed the code to as follows, it takes about 1.5 - 2 hours depending on system activity that day.

Any ideas would be appreciated.

Thanks.


select tiitm001.*, tccom001.*
from tiitm001, tccom001
where tiitm001._index4 inrange
{"SYSTEM", :item.f}
and {"SYSTEMZZZZZZZZZZ", :item.t}
and tiitm001.cplb refers to tccom001
selectdo
select tdsls041.*, tdsls040.*, tcmcs013.*
from tdsls041, tdsls040, tcmcs013
where tdsls041._index2 inrange
{:cprj.f, :tiitm001.item}
and {:cprj.t, :tiitm001.item}
and tdsls041.odat between :odat.f and :odat.t
and tdsls041.orno refers to tdsls040
and tdsls040.cpay refers to tcmcs013
selectdo
sales.amount = 0
if not isspace(tdsls040.cdel) then
select tccom013.namc:cuno.namc
from tccom013
where tccom013._index1 =
{:tdsls040.cuno, :tdsls040.cdel}
selectdo
endselect
else
select tdsls042.namc:cuno.namc
from tdsls042
where tdsls042._index1 =
{:tdsls040.orno, 0}
selectdo
endselect
endif
select tdsls041.*
from tdsls041
where tdsls041._index1 =
{:tdsls040.orno}
and tdsls041.citg <> "CST"
and tdsls041.item <> "FREIGHT"
and tdsls041.item <> "CSTACP"
selectdo
sales.amount = sales.amount +
tdsls041.amta
endselect
rprt_send()
endselect
endselect

do you have the item # present in tiitm001 also present in tipcs021 ?? (with the same code) ?

minhaaj

no, I don't have the item code present it tipcs021, although I realized that one of the things I could probably do to speed up this process is select where tdsls040.opol = tcittc.standard...

Thanks for asking.

There are 3 possible suggestions I have


(1) (Somewhat risky) Just add another index (custom) on tdsls041 solely by item. I try to avoid adding indexes on standard BaaN tables because I have had them mess up performance in a standard session in one case (heaven knows why) and it increase admin during upgrades.


(2) Assuming you can't use the project/item index you are better of simply do a full table scan on the sale order lines once and then filter out the data. Otherwise you are performing a full table scan once per each item you pull. Better to take one big hit then
filter. The item check will be quick since its indexed.

In general
select tdsls041.*, tdsls040.*, tcmcs013.*
from tdsls041, tdsls040, tcmcs013
where tdsls041._index2 inrange
{:cprj.f, :tiitm001.item}
and {:cprj.t, :tiitm001.item}
and tdsls041.odat between :odat.f and :odat.t
and tdsls041.orno refers to tdsls040
and tdsls040.cpay refers to tcmcs013
selectdo

if check.item( function to query on the item and check
if search key 2 is out of the range range ) then
continue
endif
... reset of logic

function long check.item( domain tcitem check.item)
{

do your query here and return appropriate value

}

(3) Run 1 query and populate in a "temporay table*" all the relevant project/item combinations. Note this will only work well
if you have a quick easy way to create a item project combination table. Otherwise #2 makes more sense. If is a possiblity than let me know and I will expand on how to do this. Unfortunately I don't think it will be.

*By temporary table I mean create your own custom table that stores item/project (we use a generic table that hold the first key value as the type of data stored) but you can use a table explicit built for your purpose. You would need to clear and refresh this table each time you ran it. It is also good to use a user id for one of keys.

nneilitz,

Thanks, your right, I guess I wasn't thinking - It's probably better to select the sales table ONCE and filter out the items I need, then select the table for each item I need.

option #2 it is.

Thanks again.

Alright, so changing the code to the following works amazingly fast considering, when running the session directly.

When putting it into a job it seems to take about the same amount of time as it did before.

(it takes about 10 minutes to run directly, in a job it had been taking about 1 hour, and still is, even with these changes)

Any ideas?

(I've tweaked the code a tad to match narrow the amount of times tiitm001 is called by the record set returned from tdsls041)

select tdsls041.*
from tdsls041
where tdsls041.odat between :odat.f and :odat.t
and tdsls041.opol = tcittc.standard
and tdsls041.citg <> "CST"
selectdo
select tiitm001.*
from tiitm001
where tiitm001._index1 =
{:tdsls041.item}
and tiitm001.seak between "SYSTEM" and "SYSTEMZZZZZZZZZZ"
selectdo
select tdsls040.*, tcmcs013.*
from tdsls040, tcmcs013
where tdsls040._index1 =
{:tdsls041.orno}
and tdsls040.cpay refers to tcmcs013
selectdo
endselect

sales.amount = 0
if not isspace(tdsls040.cdel) then
select tccom013.namc:cuno.namc
from tccom013
where tccom013._index1 =
{:tdsls040.cuno, :tdsls040.cdel}
selectdo
endselect
else
select tdsls042.namc:cuno.namc
from tdsls042
where tdsls042._index1 =
{:tdsls040.orno, 0}
selectdo
endselect
endif
select tdsls041.*
from tdsls041
where tdsls041._index1 =
{:tdsls040.orno}
and tdsls041.citg <> "CST"
selectdo
sales.amount = sales.amount +
tdsls041.amta
endselect
select tccom001.*
from tccom001
where tccom001._index1 =
{:tiitm001.cplb}
selectdo
rprt_send()
selectempty
tccom001.nama = ""
rprt_send()
endselect
endselect
endselect

Hi,
You dont; experience problem in the code,as I see tdsls041 being selected twice and there no aliases. And I am curioos why are you not selecting in one select tdsls041, tiitm001.

I haven't read the entire reqmt.
Thanks

Arvind

Yes, do use aliases when trying to reselect from at table:


select tdsls041.amta:calc.amta
from tdsls041
where tdsls041._index1 =
{:tdsls040.orno}
and tdsls041.citg <> "CST"
selectdo
sales.amount = sales.amount +
calc.amta
endselect


Also, if it's only slow as a Job still, sometimes the darn jobs hold the old code. Not an administrator, but this does happen. Maybe someone here can explain why, but try stopping / starting the job daemon or if there are any shared memory issues stopping and starting that.

- U can use "as set with 1 rows" for the Item Select as it will always return just 1 record.
- Use index instead of tiitm001.seak.
- Try selecting specific fields instead of tiitm001.* and tdsls041.* etc.
- Why are you querying tdsls041 again within the tiitm001 select ?


just my 2c.

MG.

Hi All, Thanks for the replies!

In answer to your questions:

I'm querying the tdsls041 table again to get the total dollar amount of goods of the order. (not just that one line)

I originally had the tiitm001 item selection using index4 (:seak, :item), however, since I already know the exact item number from the sales order line, I felt it was be faster to select the item using index1, and filter out any non "SYSTEM" items.

For example:

where tiitm001._index1 = {:tdsls041.item}
and tiitm001.seak between "SYSTEM" and "SYSTEMZZZZZZZZZZ"

would be faster then

where tiitm001._index4 inrange
{"SYSTEM", :tdsls041.item} and {"SYSTEMZZZZZZZZZ", :tdsls041.item}

correct?

Using "as set with 1 rows" to select on tiitm001._index1 isn't really neccessary, is it? We already know the exact part number from the sales order line, and index1 of tiitm001 is singular so it should only return one row to begin with.

I will try converting the alias for the second instance of selecting tdsls041. Thanks for the suggestion.

I believe the performance slowdown is mainly in the first loop of tdsls041. I've noticed that through debugging after making the changes to the second revision seen in today's post. It was still quite fast when ran directly by me, but adding it into a job still takes forever!!!

The purpose is to select any standard items that exist on a sales order where the description has "SYSTEM" is the first 6 characters.

As I said in the original post, I've found out that sometimes these standard items have a project number associated to them through the sales order line and therefore table tipcs021 wouldn't work for me.

Regarding the job holding on to the old code, I thought the same thing. So I canceled that job, deleted the session from the job, re-added it, and restarted that job. Same problem.

The only thing I *didn't* do, was stop & restart the job daemon. (trying that now)

As set with 1 rows is officially listed as a performance enhancing statement, even if only 1 row is expected and the index you are using is unique.

Why?? I don't know, it's not logical, but surely some problem with the index differences between Baan and the many databases it runs on. It may even only enhance calls to certain db's, but it's Baan developements position that it enhances.

It's probably a very small enhancement, and your problem is the tdsls041 table without index.

I just can't leave that alone eh. Other statements you have can be combined, but that tdsls041 read is the big slow down.

One more suggestion:

Keep in mind that while entering the sales order lines, it is possible that the operator will change the value of the order date. If they do, you will not catch it if you run this daily for your range.

Not sure of all your uses, and if running by transaction date range would make sense for your needs, but if so, you can start on the Sales Order Lines History table (tdsls051). This has a transaction date index that should speed this up greatly. You can then use the tdsls051 to read back to tdsls041 by index and speed through that initial statement.

You may need the actual order date in your range though, not transaction date, and then this would not work for you.

Sucess!!

I stopped & started the job daemon and the whole thing took 10 minutes.

There is two other sessions in the job and three other segments of code similar to this segment of code, in the same session, but for dealing with the various custom items of type "SYSTEM" on sales orders...

And the whole thing took about 10 minutes, which is exactly what I was looking for.

Thanks everyone!

Lisa:

Thanks for the tip on the transaction date - our use is to dump a "rolling" amount of data to a side system throughout the day - I can't remember the original intentions (what the side system is doing) other then that as it was set up about 2 years ago.

The change made recently to include standard items that were project associated through the sales order signifigantly decreased the processing time from 10 minutes to more then an hour... Lately it's been upwards of 2 or more - or worse, it's been crashing the job daemon.

I will keep the transaction date thing in mind for future reference should the owner request more changes. I hadn't thought about it as I was trying to emulate other sections of code that dealt with customized items very very quick, using a similiar structure...
I might suggest revisiting the project & examining redoing the whole thing using the transaction date.

Thanks again.

I would select tdsls040 (Order Header) information based upon the Header Order Date that way you have less records to start with. This would let you select the Order Lines (tdsls041) only once.

The only problem I believe this may cause is if they add Order Lines way after the Header.

little extra:

range and 'in between' ask more performance than
tiitm001.seak(1;6)="SYSTEM"

Because "carthesion products" are made, put the smallest table in front (in the where clause) so sls040 before sls041

use as many indexes as possible

all bits might help....

Further info: select "as with 1 rows" increases performance when a level 1 driver is used. This is because in cases of

select tiitm001.*
from tiitm001
where tiitm001._index1 between {:item.f } and {:item.t}

a level 1 database driver will get batches of records, as definied by the rds_full and ssts_set_rows parameters in ${BSE}/lib/defaults.

By using "as with 1 rows" causes the database driver to only get one record from the database and not attempt to cache the next few records.