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

2 replies [Last post]
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
       )

CompShack's picture
User offline. Last seen 6 years 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

oddsnends's picture
User offline. Last seen 10 years 48 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.