We have performance problems in sessions tisfc0101s000 (Material to issue for productions orders) and whinh4275m000 (Confirm Shipments/Loads) in a BaaN ERP 5.0c company using Informix 9.40 FC7.

Porting set : 7.1d.08

Service Pack : 16

Porting set version was updated (to version 9.0) with no results, so 7.1 was installed again.

We do not think it has to do with number of rows as tables are not that big yet (see tables length below).

-----------+--------------+
Table | Rows |
-----------+--------------+
whwmd215 29089
whinr140 19167
whinr150 19153
whinh200 207929
whwmd300 14523
whinh220 566193
whltc100 12602
whltc101 1396
whltc105 4317


Session tisfc0101s000 (Material to issue for productions orders) takes a long time (8 minutes) particularely processing items with lot control "By lot" and "FIFO" outbound priority.

Running process with trace we found the query showne below:

Fetch times of Query (QID : 240) Trans 4 (tisfc0207m000) in SqlBreak :
select whinr150.*, whwmd300.*, whinr140.*
from whinr150, whwmd300, whinr140 for update
where whinr150._index2 = {:i.item, :whinh205.cwar}
and (whinr150.clot = :whinh220.clot or :whinh220.lsel = tclsel.any)
and whinr150.pkdf = :i.pkdf
and whinr150.levl = :i.levl
and whinr150.cuni = :i.unit
and whinr150.stra >= :i.search.qty.ist
and whinr150.stra > 0
and whinr150.stka > 0
and whinr150.cmba refers to whinr140
and (whinr140.ball = tcyesno.no and ((not :whinh220.oorg = whinh.oorg.assembly and
whinr140.bout = tcyesno.no and :whinh200.ittp = whinh.ittp.issue) or
(not :whinh220.oorg = whinh.oorg.assembly and whinr140.btri = tcyesno.no and
(:whinh200.ittp = whinh.ittp.transfer or :whinh200.ittp = whinh.ittp.item.transfer)) or
(whinr140.btri = tcyesno.no and :whinh220.oorg = whinh.oorg.assembly)))
and (whinr140.bcyc = tcyesno.no or :i.bcyc = tcyesno.yes)
and (whinr140.stks - whinr140.stkh - whinr140.stka) >= :i.search.qty.iiu
and whinr140.cmba refers to whwmd300 unref clear
and (whinr140.loca = "" or whwmd300.loct = :i.loct or :i.loct = empty)
and (whinr140.loca = "" or (whwmd300.loct <> whwmd.loct.receiving and whwmd300.loct <> whwmd.loct.inspection and
whwmd300.ball = tcyesno.no and ((:whinh220.oorg = whinh.oorg.assembly and ((whwmd300.trto = tcyesno.yes and
whwmd300.btri = tcyesno.no) or (:whinr140.loca = :whinh201.loca))) or (not :whinh220.oorg =
whinh.oorg.assembly and (:whinh200.ittp = whinh.ittp.transfer or
:whinh200.ittp = whinh.ittp.item.transfer) and whwmd300.trto = tcyesno.yes and whwmd300.btri = tcyesno.no) or
(not :whinh220.oorg = whinh.oorg.assembly and :whinh200.ittp = whinh.ittp.issue and
whwmd300.outl = tcyesno.yes and whwmd300.bout = tcyesno.no))))
and (not (not :whinh220.oorg = whinh.oorg.assembly and :whinh200.sfty = whinh.type.warehouse and
:whinh200.stty = whinh.type.warehouse and :whinh200.sfco = :whinh200.stco) or
(not :whinh220.oorg = whinh.oorg.assembly and :whinh200.sflo <> "" and whinr140.loca = :whinh200.sflo) or
(not :whinh220.oorg = whinh.oorg.assembly and :whinh200.sflo = "" and (:whinh200.stlo = "" or
:whinh200.ittp = whinh.ittp.item.transfer or whinr140.loca <> :whinh200.stlo)))
and (whinr150.clot = "" or exists ( select whltc105.*
from whltc105
where whltc105._index1 = {whinr140.cwar, whinr140.item, whinr150.clot}
and (whltc105.ball = tcyesno.no and (:whinh220.lsel <> tclsel.any or
whltc105.stks - whltc105.stkh - whltc105.stka > 0.0) and
((not :whinh220.oorg = whinh.oorg.assembly and
whltc105.bout = tcyesno.no and :whinh200.ittp = whinh.ittp.issue) or
((not :whinh220.oorg = whinh.oorg.assembly and
((whltc105.btri = tcyesno.no and
(not :whinh220.oorg = whinh.oorg.assembly and
:whinh200.sfty = whinh.type.warehouse and
:whinh200.stty = whinh.type.warehouse and
:whinh200.sfco <> :whinh200.stco) and
not(exists ( select whltc105_2.*
from whltc105 whltc105_2
where whltc105_2._index1 = {:whinh200.stco, whltc105.item, whltc105.clot} and
whltc105_2.btrr = tcyesno.yes)))
or ((not :whinh220.oorg = whinh.oorg.assembly and
:whinh200.sfty = whinh.type.warehouse and
:whinh200.stty = whinh.type.warehouse and
:whinh200.sfco = :whinh200.stco) and
whltc105.btri = tcyesno.no and
whltc105.btrr =tcyesno.no)) or
(:whinh200.ittp =whinh.ittp.transfer or
:whinh200.ittp =whinh.ittp.item.transfer and
whltc105.btri = tcyesno.no and
whltc105.btrr = tcyesno.no)))
or (whltc105.btri = tcyesno.no and :whinh220.oorg = whinh.oorg.assembly))
and (:whinh220.revi = "" or
:whltc000.erac = tcyesno.no or
:whwmd000.roei = tcyesno.no or
exists (select whltc100.*
from whltc100
where whltc100._index1 = {whltc105.item, whltc105.clot} and
whltc100.revi = :whinh220.revi))) ))
and (:whinh200.sflo = "" or whinr140.loca = :whinh200.sflo)
order by whinr150.item, whinr150.cwar, whinr150.idat, whwmd300.proo, whinr150.stka, whinr150.clot


Any advice or suggestions?

Are you level 1 or level 2 driver?

Informix reccomends very complex "statistics" for their tables have you generated those?

Dave

Please upgrade to the latest FC level. FC8 contains a lot of performance fixes that made the Informix database less performing on a Ban environment.

Hope this helps,
Dick