Peoplesoft queries - performance

Question

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

Answer 1

The only fix I know of to force it to do the joins the way it should, instead of the way it does (and avoid the dreaded Merge Join Cartesian) is to create a view that does the join correctly.

• Create a record with the correct fields.
• Make it type SQL view.
• Paste in your SQL that now works.
• Add it to the query security tree.
• Refresh the security cache.