Differences in Performance 7.22.UC1 and 7.30.UC3

In article <362F72DB.3639@gate.net>, Tim Jones <tojones@gate.net> writes >We are running PeopleSoft Financials 6.0 on 7.30UC HP-UX 10.20. > >This past weekend we upgraded from HP-UX 10.10 Infx 7.22.UC1 to the >above 7.3 environment. > >Env A: 7.3 HP-UX 10.20 >Env B: 7.22 HP-UX 10.10 > >When we ran the following sqr in Env B, response time was real good. >(notice the commented out ORACLE line, so that Informix would run the >IFDEF statement for the WHERE clause L.ROWID IN (SELECT...) ) > >-------------------------------------------------------start of SQR >BEGIN-SELECT ON-ERROR=CANNOT-SELECT >L.BUSINESS_UNIT >L.INVOICE >SUM(L.NET_EXTENDED_AMT) &l.net_extended_amt > let $business_unit = rtrim(&l.business_unit,' ') > let $invoice = rtrim(&l.invoice,' ') > let #invoice_amt_pretax = &l.net_extended_amt > do UPDATE-INVOICE-AMOUNT >from PS_BI_LINE L >!RI Added the INFORMIX piece for optimization >!#IFDEF ORACLE >#IFDEF INFORMIX > where L.ROWID IN > (SELECT DISTINCT LN.ROWID > from PS_BI_LINE LN, PS_INTFC_BI I > where I.BUSINESS_UNIT = LN.BUSINESS_UNIT > and I.INVOICE = LN.INVOICE > and I.TRANS_TYPE_BI = 'LINE' > and I.LOAD_STATUS_BI = 'DON' > and I.PROCESS_INSTANCE = #process_instance) >#ELSE > where EXISTS(SELECT 'X' > from PS_INTFC_BI I > where I.BUSINESS_UNIT = L.BUSINESS_UNIT > and I.INVOICE = L.INVOICE > and I.TRANS_TYPE_BI = 'LINE' > and I.LOAD_STATUS_BI = 'DON' > and I.PROCESS_INSTANCE = #process_instance) >#ENDIF >GROUP by L.BUSINESS_UNIT,L.INVOICE >END-SELECT >-------------------------------------------------------------end of SQR > >But after the upgrade to Env A, the above code ran astronomically slow. >More than 10 hours.. Ahhh!!!! But when I remove the IFDEF INFORMIX >statement so it would hit the ELSE statement... WHERE EXISTS (SELECT >'X'....), now it is running at a very good speed. > >Can someone explain to me, what 7.30U3 has in it that caused this to >happen... > Sub-query flattening.... >Thanks >Tim Jones >tjones@romac.com -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care