Baanboard.com

Baanboard.com (http://www.baanboard.com/baanboard/index.php)
-   Performance & Tuning (http://www.baanboard.com/baanboard/forumdisplay.php?f=61)
-   -   Performance problems (http://www.baanboard.com/baanboard/showthread.php?t=26241)

soportevzla 26th January 2006 23:18

Performance problems
 
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).
Code:

-----------+--------------+
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:
Code:


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?

dave_23 27th January 2006 19:28

Are you level 1 or level 2 driver?

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

Dave

Dikkie Dik 31st January 2006 10:33

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


All times are GMT +2. The time now is 01:22.


vB.Sponsors
©2001-2017 - Baanboard.com - Baanforums.com