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.
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 |
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.