Ask Question
Help Others
Home › Forums › PeopleSoft › PeopleSoft Technical

You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Query Tuning

  • Post reply
2 replies [Last post]
Mon, 04/21/2008 - 2:21pm
Guest's picture
Guest

Hello

My users have one public query, they have not been able to run this query for three weeks.The query finally ran after almost 2 hrs (should only take a few minutes.)It has been in use for 5 yrs and running in few minutes. I was finally able to get it to run today, but it took almost 2 hrs to run.

SELECT a.TYPE, a.emplid, c.NAME, a.amount,
       TO_CHAR (b.termination_dt, 'YYYY-MM-DD'), a.run_id, a.erncd,
       TO_CHAR (a.earns_begin_dt, 'YYYY-MM-DD'),
       TO_CHAR (a.earns_end_dt, 'YYYY-MM-DD')
  FROM ps_abc_empl a,
       ps_employment b,
       ps_f_qrysec_fst_vw b1,
       ps_personal_data c
 WHERE b.emplid = b1.emplid
   AND b.empl_rcd = b1.empl_rcd
   AND c.emplid = b1.emplid
   AND b1.rowsecclass = 'DPALL'
   AND (    a.run_id = :1
        AND a.TYPE IN ('XYZ', 'LMN', 'CFB', 'RVU')
        AND a.emplid LIKE '30%'
        AND b.termination_dt < TO_DATE (:2, 'YYYY-MM-DD')
        AND a.emplid = b.emplid
        AND a.erncd IN
               ('AB', 'DF', 'ZN', 'LM', 'XT', 'AL', 'XE', 'AZ', 'QR', 'PO',
                'Q4', 'GB', 'NH', 'SA', 'WA')
        AND b.emplid = c.emplid
       )

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
Top
Mon, 04/21/2008 - 3:17pm
#1
CompShack's picture
CompShack
User offline. Last seen 42 weeks 3 days ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Query Tuning

It has been in use for 5 yrs and running in few minutes. I was finally able to get it to run today, but it took almost 2 hrs to run.

That tells me something has changed. Are you in the process of upgrading? The reason why I ask is because the ps_employment table used to be an actual table in older peoplesoft versions (8.12 for example) but now it is a view (8.49). This might be one reason why.

Another reason could be data related, ps_employment and ps_personal_data could get rather large. It is very hard to figure out why your query is taking a lot longer to run but something I've noticed is that your where clause has variables passed to it:

AND A.EMPLID LIKE '30%'
AND B.TERMINATION_DT < TO_DATE(:2,'YYYY-MM-DD')

I bet it will take a lot less time if you have emplid = '123456789' for example. I'm not saying this is what you need to do, but this might be another reason why it is taking longer.

I would also suggest taking (AND A.EMPLID = B.EMPLID AND B.EMPLID = C.EMPLID) and put them right after the "Where". Query tuning requires analysis for tables and how data is accessed. Something I can't do on my end.

Good luck!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Top
  • reply
Tue, 04/22/2008 - 8:24am
#2
oddsnends's picture
oddsnends
User offline. Last seen 2 years 15 weeks ago. Offline
Joined: 03/28/2008
Posts: 16
Re: Query Tuning

If you're still having trouble pinning down the delay after using CompShack's suggestions - you may want to try selecting from each table individually just to see if one of them is causing the issue. Then I would join back one table at a time (running after each join) and see when it begins to hang.

Top
  • reply

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.