Re: Informix/Peoplesoft Question
Steve Romankiw wrote:
>
> We are in the process of migrating to Peoplesoft v6.10. We are running
> ODS v7.23 on Solaris 2.5.1
>
> In the Billing portion of Peoplesoft, queries similar to the below are
> used quite extensively:
>
> SELECT ....
> FROM ....
> WHERE EXISTS(SELECT ... FROM ... WHERE...)>
> I am noticing that it is very "expensive" to run this query with the
> EXISTS clause.
>
> Have you noticed any performance penalties by using the EXISTS clause?
> Does the EXISTS clause return TRUE after the first row is returned or
> when the entire result set is returned?
>
> -- SteveR
> ----------------------------------------------------------------
> Steve Romankiw +
> Executive Risk Inc. +
> DBA + email: sromankiw@execrisk.com
> 82 Hopmeadow Street + work: (860) 408-2474
> Simsbury,CT 06070 + fax: (860) 408-2139
> -----------------------------------------------------------------
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