I need report which connects lot with materials used on project (production orders), so I have to take sam data from two tables without direct reference. Tables are tdinv700 Inventory transactions by item and tdltc102 Lot transaction for lot tracking.

My session script looks like this and it is sooo, sooo slow:

functions:

function read.main.table()
{
select tdinv700.*, tiitm001.*, tiitm100.*, tcmcs003.*,
tipcs020.*, tdltc102.*
from tdinv700, tiitm001, tiitm100, tcmcs003, tipcs020, tdltc102
where tdinv700.cprj between :cprj.f and :cprj.t
and tdinv700.orno between :orno.f and :orno.t
and tdinv700.item refers to tiitm001
and tdinv700.cmba refers to tiitm100
and tdinv700.cwar refers to tcmcs003
and tdinv700.cprj refers to tipcs020
| dodo dudo
and tdinv700.orno = tdltc102.orno
and tdinv700.pono = tdltc102.pono
and tdinv700.trdt = tdltc102.trdt
order by tdinv700.cprj, tdinv700.orno, tdinv700.pono, tdinv700.item
selectdo
rprt_send()
endselect
}


How to make it faster in this case when there is no direct connection between tables.

Thanks

Of course, I will throw out all the stars * from select which I don't need ...

Firstly you are not using indices... so the result is full table scans. Change your select statement's where clause to:


where tdinv700._index2 inrange {tckost.prd.mat.issue,:Orno.f}
and {tckost.prd.mat.issue,:Orno.t}
and tdinv700.cprj inrange :cprj.f and :cprj.t


and then add on all your refers to clauses

and then lets see what the result is...

Regards,
Rupert

Thanks,
but I didn't get much with index_2 cause, my first form field (which people need as information) is Project (.cprj) which is not in index fields. (Not in index_1 too.)

Any other suggestion?

1. Solution using tdilc301
Are you logging ilc history (table tdilc301)? Find this out by looking at parameter tdilc000.logt. If it's yes then we should look at using tdilc301 as our main table.

2. Solution using tdinv700
Also what you can do is nest the select on tdltc102. (this is a good idea as we can then see which query is taking the most time)


where tdinv700._index2 inrange {tckost.prd.mat.issue,:Orno.f}
and {tckost.prd.mat.issue,:Orno.t}
and tdinv700.cprj inrange :cprj.f and :cprj.t
and tdinv700.item refers to tiitm001
and tdinv700.cmba refers to tiitm100
and tdinv700.cwar refers to tcmcs003
and tdinv700.cprj refers to tipcs020
|order by tdinv700.cprj, tdinv700.orno, tdinv700.pono, |tdinv700.item
selectdo
select tdltc102.*
from tdltc102
where tdltc102._index5 = {tdltc.tord.prod.rm, :tdinv700.orno, :tdinv700.pono}
and tdltc102.trdt = :tdinv700.trdt
selectdo
rprt_send()
endselect
endselect


P.S. try with/without the order by - that might have an effect on the query execution and use the report input fields for sorting instead... and try compiling in debug mode and then stepping through the nested query - where is the most time spent?

I am including an attachment and link that might help when others run into something like this. I am attaching the Performance Optimization guide which may help you see when queries should be broken down like Rupert suggested. Here is a link (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7665&highlight=Performance) with another document that I just found. hope this helps.

Mark

1. solution:

tdilc000.logt in ILC parameter table is yes, but there is no data in table tdilc301 which I need and get thrue "slow script" ?!


2. solution:

Nested SQL query was something I started from ....
It was too slow so I had to kill my report after 10-15 minutes.
I'll try your code, cause there are some differences ...

:(
I waited so long - I had to kill report again.

I'll try to debug to see where is the catch -
Trace said that there is endless loop in nested SQL.

Regarding point one of my previous post are you maybe running 'tdilc3211m000' in a job somewhere with the 'delete' option set? This would explain the empty table. We use tdilc301 exclusively for queries like yours because of the availability of all the data from the various modules on one table.
Our philosophy is: If it moves - measure it.
Before turning it on make sure you can afford the disk space!

P.S. what hardware are you running on?

Regards,
Rupert

No, we don't delete archive - not in job nor directly. In table tdilc301 I have some records, but not for items that are issued on projects.

P.S. We are on AIX RS6000 box with cluster and mirror with lots of space and much more resources then our Bisam needs.

Thanks a lot for all the help
;)

Best regards

Did you consider the table tdltc104? There are the project, production order and item.

But, I need Lot tracking for project.
Table tdltc104 will be just another table in query.
But, maybe you are right - if it will be my main table and if I catch lot from 102, maybe it will be faster !

I'll try and post results.

Thanks

Try this:
1. Remove ALL refers to and use the field names
2. Nest the query related to tiitm001 and tiitm100 seperately

Also use the PROF functionality in baan to find out which query and which table takes the maximum time.

Hope this helps

Sorry,
PROF functionality - what do you mean by that?

Sorry
What I mean't was as follows:
Informix/ SQL server has this variable called INFPROF/SQLPROF.

One can set this variable for example as -- -set MSQLPROF=0.001 -keeplog -logfile <name> <sessionname>

The above will create a log file and display all the queries being fired table wise and the time taken for the same where the query took more than 0.001 second in the above case.

Quite a few reports have been optimised by me using this - also certain baan reports not optimised have been reported to PEG.

Hope this will help