Hi,

Environment details:
Baan4b
Oracle version: Oracle7.X
Driver2

Below Query is taking around 12 to 14mins any suggestions welcome to improve performance of the query.

I am attaching profile file for your reference.

select tdsls045.*,
tiitm001.ctyp, tiitm001.csgs
from tdsls045, tiitm001
where tdsls045.pono < :tdsls000.cspn.4
and not exists (select tdrpl100.sorn
from tdrpl100
where tdrpl100._index9 = {tdsls045.orno, tdsls045.pono})and not exists (select *
from issls930
where issls930._index5 = {tdsls045.orno, tdsls045.pono,tdsls045.srnb}
and issls930.dlnk = isdelink.active)
and tdsls045.ddat <> 0
and tiitm001.item = tdsls045.item
order by tdsls045.orno, tdsls045.pono, tdsls045.srnb

Regards,
Reddy

Run with DBSLOG debug information (search the threads for that) to get the times of the query and thus know where Baan is spending its time on.

It looks like you've got some custome tables in there so i can't tell you 100%

But you're outer query is using tdsls045 Index1 however you're not really limiting index 1 by much, so it is likely doing a full index scan.

Your query on rpl100 should be "ok" but it's stilly to using the _index9. You should use _index1 which is orno, pono, srrb. You should also order by
_index1 just to make sure you're sending the index hint.

You're also using a "not exists" from 2 subqeries. which is fairly bad design, I'd see if i could try to join the tables in another way. It might be better to send them in your selectdo or selectempty

The only other thing you could hope for is to do "analyse table <tablename> compute statistics" on each of the tables in your query (at the oracle level) and hope the optimizer can figure it out.

Dave

Hi,

Environment details:
Baan4b
Oracle version: Oracle7.X
Driver2

Below Query is taking around 12 to 14mins any suggestions welcome to improve performance of the query.

I am attaching profile file for your reference.

select tdsls045.*,
tiitm001.ctyp, tiitm001.csgs
from tdsls045, tiitm001
where tdsls045.pono < :tdsls000.cspn.4
and not exists (select tdrpl100.sorn
from tdrpl100
where tdrpl100._index9 = {tdsls045.orno, tdsls045.pono})and not exists (select *
from issls930
where issls930._index5 = {tdsls045.orno, tdsls045.pono,tdsls045.srnb}
and issls930.dlnk = isdelink.active)
and tdsls045.ddat <> 0
and tiitm001.item = tdsls045.item
order by tdsls045.orno, tdsls045.pono, tdsls045.srnb

Regards,
Reddy

Hello,

(Display or Print Session ????)
as i suppose, you wanna select all salesorder deliveries without the additional positions above tdsls000.cspn.4 (Sales parameters) and without replenishment orders and all deliveries where is a deliverydate.
I don't know the structure of own tables (issls930).
Try to work with variable and functions, not all in one select statement.
If parameter cspn.4 is set to 960:

select tdsls045.*, tiitm001.ctyp, tiitm001.csgs
from tdsls045, tiitm001
where tdsls045._index1 inrange {:orno.f, 0, 0}
and {:orno.t, 950, 99}
and tdsls045.ddat <> 0
selectdo
order = tdsls045.orno
position = tdsls045.pono
serial = tdsls045.srnb

check.tdrpl100()
if condition is ok then

selectempty
maybe do something
endselect

function check.tdrpl100()
{
select tdrpl100.*
from tdrpl100
where tdrpl100._index1 = {:order, :position, :serial}
selectdo
selectempty
here a repl. order does'nt exist
endselect
}

Try to avoid "full table scans"

Regards

Reddy,

Your problem can be in different areas:
- how much is the "not exists" on tdrpl100 filtering?
- how much is the "not exists" on issls930 filtering?

If one of these is filtering more than 50% of the records, I assume that this is causing your performance issue.

If thsese filters filters less than a 10% I advise to do a SQL_TRACE and share the tkprof formatted output.

Hope this helps,
Dick

Hi All, I also check it . I saw something on SQL.
issls930.dlnk = isdelink.active
if DLNK domain type is enumareted then you should use corresponding integer number for your enumareted type.In sql query this is must on Baan Tools. However , timing for your sql may be normal if there ara so many records. Because your query is a bit complicated. Check DLNK domain type.For example if 1 is used for your isdelink.active then change it in your code like
issls930.dlnk = 1 .

Good lucks

if DLNK domain type is enumareted then you should use corresponding integer number for your enumareted type.

IMHO this statement is not correct.

Kind regards,
Dick

Are you sure ? In baan sql
For example
select *
from table
where [condition part]
selectdo
[operation part]
endselect

In condition part of Baan sql you should use int value for enum. Am I right ?
In operation part of baan sql you should use directly your enum like issls930.dlnk = isdelink.active

ok. Dikke Understand it ? If it is not correct me !

It is not correct Suleyman, you should use the constant name, rather than the constant number. The debugger will by the way not even accept the constant numbers (at least in BaanV)..

Regards,
En

I mean constant number for enum.

anyway , I said that because in our system , When I compile my code with enum name , it didn't compile it. But I said that for SQL condition part. Except that you are right. as soon as possible I will check it agin .

Reddy,

Have you been able to make some progress on this topic? I like to hear what the problem was and how you fixed it. Or where we can help you to get it fixed.

Kind regards,
Dick