View Single Post
  #1  
Old 26th January 2006, 22:18
soportevzla soportevzla is offline
Newbie
 
Join Date: Jan 2006
Posts: 2
soportevzla is on a distinguished road
Baan: BAAN ERP, BAAN IV - DB: ORACLE, INFORMIX - OS: UNIX, NT, LINUX
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?

Last edited by patvdv : 27th January 2006 at 10:38.
Reply With Quote