User login
Sponsored links
Recent blog posts
- How to resubmit on Integration Broker message with DONE status
- Message cannot be changed. Message referenced in runtime tables (Doc ID 660969.1)
- PeopleSoft Object Type List
- PeopleSoft Data Integrity Error (124,85)
- Unwanted Save Warnings in Display Only Pages
- Breadcrumb SQL to find the Navigation path for the component in the PIA
- Application Designer Index naming convention
- How to create Trigger in DB2
- Emplid increment automatically
- How can we romove multiple duplicate rows of data printing under each Print command in SQR Program?
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
- Login to post comments
Mon, 04/21/2008 - 2:21pm
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
)
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
Unanswered Forum Posts
| Title | Under | Posted on |
|---|---|---|
| Component interface Error: no rows exist for the specified keys | PeopleSoft Technical | 03/15/2019 - 3:54am |
| ADD 24 months starting from current month.(peoplesoft) | PeopleSoft Functional | 07/29/2018 - 8:44pm |
| TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
| how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
Stay updated
CompShack is a user driven Dev2Dev IT community. Developers can ask questions, help answer questions, or just browse code and articles contributed by users. No registration required.
You can register if you want to contribute code samples, share your knowledge at your own blog, and become adored in the community!
Poll
New forum topics
- Component interface Error: no rows exist for the specified keys
- ADD 24 months starting from current month.(peoplesoft)
- TRC values dropdown
- OHS is rendering XML instead of the view or data
- Search Results
- PS Query - Need Dependency and Prunable examples
- PS HCM AWE - Transaction routing to Requestor for approval, when he is the Delegatee by his 1 up Manager
- how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode
Recent comments
- Re: PeopleCode Check Number and Check Letter Functions
2 years 22 weeks ago - Re: nVision Tabular Report through PIA with prompts
2 years 27 weeks ago - Re: PeopleSoft PeopleTools Meta-Tables
2 years 27 weeks ago - Re: PS Query - Need Dependency and Prunable examples
2 years 29 weeks ago - Re: Search Results
2 years 30 weeks ago - Re: How to schedule appengine using peoplecode
2 years 30 weeks ago - Re: Mark worked in Worklist page
2 years 32 weeks ago - Re: Calculate the sum for a grid field with out using row ...
2 years 33 weeks ago - Re: Invalid SNAC client version
2 years 36 weeks ago - Re: Trace Application Engine Processes Using Process Definitions
2 years 42 weeks ago
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 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
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.