Re: Informix/Peoplesoft Question

In article <34F3411D.70DF@romac.com>, Romac International <tojones@romac.com> writes >> ----------------------------------------------------------------- >The following "where statement" comes from the BIIF0001.sqr. We have >had no real performance with this type of statement both in development >and production environments. We are using 7.22UC1, 4 cpu, HP-UX 10.10. >In this case, the exist statement does return true on the first row >found. >------------------------------------------------------------ >from PS_DST_CODE_TBL C4 >where C4.EFFDT = (SELECT MAX(EFFDT) from PS_DST_CODE_TBL > where SETID = C4.SETID > and DST_ID = C4.DST_ID > and EFFDT <= $asoftoday > and EFF_STATUS = 'A') >and EXISTS (SELECT 'X' FROM PS_INTFC_BI_HTMP I > [$WHERE_FIXED_I] > and I.SETID = C4.SETID > and I.DST_ID_AR = C4.DST_ID > and I.ACCOUNT = ' ' > and I.PROCESS_INSTANCE = #process_instance) >END-SELECT Someone somewhere needs to do some serious schema changes. The SELECT MAX(..) is evaulated once per row in the table C4 i.e. probably several thousand queries get executed. Try running it in dbaccess with SET EXPLAIN ON applied. -- 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