Axton,
Thanks for the heads up. It was a while ago and I remember playing with the NOT IN statement and having issues. I was getting different results than with my outer join and so I ended up going with the outer join. I was probably doing something else wrong. Its a "middle of the night" process and 5 minutes was faster than 8 hours, a big enough improvement at the time and accuracy was more important than speed. But if I can make it faster still, that's even better.
I'll have to check out the NOT EXISTS when I get a chance.
Thanks again,
Thad
"Argue for your limitations, and sure enough, they're yours."-- Richard Bach
| "Axton" <[EMAIL PROTECTED]>
Sent by: "Action Request System discussion list(ARSList)" <[email protected]> 11/09/2006 03:36 PM
|
|
** Thad:
You should look in to the NOT EXISTS and NOT IN statements for Oracle. It's a hundred times faster in these cases.
This is a good article on the topic (long):
http://asktom.oracle.com/pls/ask/f?p=4950:8:15476634477893458460::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:442029737684
Your syntax would be something like this:
SELECT ....
FROM [EMAIL PROTECTED] e
WHERE NOT EXISTS (
select null
from shr_people p
where e.emp_id = p.ID)
Compare this to using NOT IN:
SELECT ....
FROM [EMAIL PROTECTED] e
WHERE emp_id not in (
select id
from shr_people)
SQL*Plus will show you the statistics and execution plan if you execute SET AUTOTRACE ON before running the statements.
Axton Grams
On 11/9/06, Thad Esser <[EMAIL PROTECTED]> wrote:
**
When I first got here, that was exactly the problem - the People update escalations would take several hours to run, and interfere with escalations that needed to run on the order of minutes.
To resolve it, I created an SQL view that joined the SHR:People form with our database of record for Employees. Using an outer join, and only returning results where the shr_people part of the join was null (Oracle):
SELECT ....
FROM [EMAIL PROTECTED] e, shr_people p
WHERE e.emp_id = p.ID(+) and p.ENTRY_ID is null
results in a list of just the employees that needed to be created, which is a much smaller number than everyone. I then created a Remedy View form based on the SQL view above, and ran my escalations against that view form. The SQL isn't the most efficient in the world, but it works and is good enough that the escalations now run in 5 minutes versus 8 hours. Doing this allowed us to update nightly, instead of the once a week on weekends that was happening. It also allowed me to put some processing in the SELECT statement, like concatenating 3 phone number fields into one and pulling in Job Titles from yet another table based on Job codes in the EMPLOYEES table.
I also did all of the above for People Updates, this time using the where clause to detect differences (where e.phone_number != p.phone_number OR ...). Again the SQL itself is not the fastest, but you are only dealing with records that have changed instead of everyone, which makes the process as a whole faster.
Anyway, I hope that gives you some ideas.
Thad
"Argue for your limitations, and sure enough, they're yours."-- Richard Bach
| "Jason Miller"
<[EMAIL PROTECTED]>
Sent by: "Action Request System discussion list(ARSList)" <[email protected]> 11/09/2006 07:56 AM
|
|
Also be careful when using an escalation to populate from AD. I have seen
instances where a synch from AD was done nightly (over 30k users in AD)and
since escalations are single threaded no other escalations would fire until
the AD synchronization escalation was finished many, many hours later. This
was delaying notifications on urgent Help Desk tickets.
Jason
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Michiel Beijen
Sent: Thursday, November 09, 2006 2:35 AM
To: [email protected]
Subject: Re: Updating SHR:People from Vendor form
Gianluca,
You are right, there are no Submit, Modify and Delete events on Vendor
forms. You could look into using escalations to push the data over to
SHR:People.
Please note that some (windows) ad server by default only export 2000
records at a time. Your windows administrator can modify this value.
Kind regards,
Michiel
On 11/9/06, Gianluca Nieri <[EMAIL PROTECTED]> wrote:
> Hi all,
> I have succesfuly connected a vendor form to an Active Directory server
and now I can populate mys SHR:People
> form using an escalation.
>
> Now I need to create 3 filters for the Create New User, Modify User,
Delete User events in AD.
>
> I built all the filters running on the Submit/Modify/Delete events of my
vendor form to push the data in the
> SHR:People, but they don't work.
>
> I suppose that it is not possible to use normal filters on vendor form, am
I right?
>
> Can you suggest me any solution?
>
> Regards, Gianluca
>
>
>
>
>
> --
> Gianluca Nieri
> IT Business consultant, Remedy Specialist
>
> 3, Via Niccodemi | 20156 Milan, Italy
> Mobile +39.3475844658 | Office +39.02.36511627 | Fax +39.02.99984338
> www.gianlucanieri.com | [EMAIL PROTECTED]
>
>
____________________________________________________________________________
___
> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
>
____________________________________________________________________________
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
***IMPORTANT NOTICE: This communication, including any attachment, contains information that may be confidential or privileged, and is intended solely for the entity or individual to whom it is addressed. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message is strictly prohibited. Nothing in this email, including any attachment, is intended to be a legally binding signature.***
__20060125_______________________This posting was submitted
with HTML in it___
__20060125_______________________This posting was submitted with HTML in
it___
***IMPORTANT NOTICE: This communication, including any attachment, contains information that may be confidential or privileged, and is intended solely for the entity or individual to whom it is addressed. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message is strictly prohibited. Nothing in this email, including any attachment, is intended to be a legally binding signature.***
