Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Performance & Tuning

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #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
Sponsored Links
  #2  
Old 27th January 2006, 18:28
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
Are you level 1 or level 2 driver?

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

Dave
Reply With Quote
  #3  
Old 31st January 2006, 09:33
Dikkie Dik's Avatar
Dikkie Dik Dikkie Dik is offline
Guru
 
Join Date: Sep 2002
Location: Netherlands
Posts: 585
Dikkie Dik is on a distinguished road
Baan: Triton 3.0 and higher - DB: All - OS: All
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
__________________
BTW: this post has been made on my personal view. My employer might not share my point of view.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Performance problems in tdinv0250m000 and MPS Erik Konrath Distribution, Transportation & Warehousing 6 30th March 2009 08:38
Baan Oracle* Driver Performance Problems pshaw1 Performance & Tuning 7 25th February 2004 15:27
MS SQL SP 3 performance problems astrom Operating Systems & Databases 18 19th August 2003 12:20
Performance problems in session "Update Purchase Statistics" Erik Konrath Performance & Tuning 2 12th February 2003 11:14
Purchase Statistics Performance problems Erik Konrath Distribution, Transportation & Warehousing 1 4th February 2003 07:19


All times are GMT +2. The time now is 23:35.


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