Re: Differences in Performance 7.22.UC1 and 7.30.UC3

> >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... > Most likely subquery flattening. While flattening is a performance enhancement with correlated subquerries, it has been shown to be a problem with un-correlated ones. As an expriment, you might want to set the environment variable NO_SUBQF to 1 before starting the engine. That will disable the feature. In 7.31, the usage of subquery flattening will be just a bit more selective in when it will be used and when it will not. Madison Pruet