Peoplesoft queries - performance

Posted by DBa on Stack Overflow See other posts from Stack Overflow or by DBa
Published on 2010-03-17T16:11:51Z Indexed on 2010/03/19 22:21 UTC
Read the original article Hit count: 180

Filed under:

Hi,

I'm facing a problem with PeopleSoft queries (using Oracle backend database): when a rather complex query involving multiple records is set off by a user, PS does an enforced join of security records, thus producing SQL like this:

select .... from
ps_job a, PS_EMPL_SRCQRY a1, ps_table2 b, ps_sec_rcd2 b1, ps_table3 c, ps_sec_rcd3 c1
where (...security joins a->a1, b->b1, c->c1...) and (...joins of a, b and c...) and
a.setid_dept = 'XYZ';

(let's assume the last condition has a high selectivity and there is an index on the column) Obviously, due to the arrangement of the conditions, first a huge join is created, written to the temp segment, and when the last condition is finally applied, only a small subset is selected. A query formulated in this way is very likely to hit the preset timeout of the APPSRV, and even of the QRYSRV. When writing the query manually, I would rather move the most selective condition to the start, thus limiting the amount of the data being handled, to a considerable level.
Any ideas on how to make PS behave like this? Actually, already rewriting "Oracle-styled" SQL to ANSI SQL seems to accelerate the queries - however, PS writes Oracle-style queries...

Thanks in advance
DBa

© Stack Overflow or respective owner

Related posts about PeopleSoft