Re: Peoplesoft V6/V7 and informix tuning
Of course there are all the usual Informix performance tuning techniques to
try (onconfig parameters, update statistics,...). Here is an example of
something else to consider for subqueries.
The orginal query is
select JB.EMPLID, JB.EMPL_RCDN, JB.EMPL_CLASS, JB.JOBCODE
from PS_JOB JB
where JB.LOCATION = ? AND JB.EMPL_TYPE IN ('E', 'H') AND JB.EMPL_CLASS IN
('P', 'G', 'R') AND JB.EMPL_STATUS in ('A', 'P') AND JB.EFFDT =
(select max(jb2.effdt) from ps_job jb2 where jb2.emplid = JB.EMPLID
and
jb2.empl_rcdn = JB.EMPL_RCDN and effdt <= ?) AND JB.EFFSEQ =
(select max(jb3.effseq) from ps_job jb3 where jb3.emplid =JB.EMPLID and
jb3.empl_rcdn = JB.EMPL_RCDN and jb3.effdt = JB.EFFDT)
ORDER BY EMPLID
Replace it with the following.
select JB.EMPLID, JB.EMPL_RCDN, JB.EMPL_CLASS, JB.JOBCODE, jb.effdt,jb.effseq
FROM PS_JOB JB
WHERE JB.LOCATION = "CONV" AND JB.EMPL_TYPE IN ('E', 'H') AND
JB.EMPL_CLASS IN
('P', 'G', 'R') AND JB.EMPL_STATUS in ('A', 'P')
INTO TEMP mike_temp;
create index mike_temp_idx1 on mike_temp ( emplid, empl_rcdn);
select JB.EMPLID, JB.EMPL_RCDN, JB.EMPL_CLASS, JB.JOBCODE ,jb.effdt,jb.effseq
FROM mike_temp JB
where JB.EFFDT =
(
select max(jb2.effdt) from mike_temp jb2
where jb2.emplid = JB.EMPLID
and jb2.empl_rcdn = JB.EMPL_RCDN
and effdt <= "1999-01-01" AND JB.EFFSEQ =
(
select max(jb3.effseq) from mike_temp jb3
where jb3.emplid = JB.EMPLID
and jb3.empl_rcdn = JB.EMPL_RCDN
and jb3.effdt = JB.EFFDT
)
)
ORDER BY jb.EMPLID
;
The basic idea is to move the (small) subset of rows particular to the query
to a temp table. Then, do subqueries against the subset instead of entire
table.