June 2, 2003 at 2:09 pm
Your probably going to have to give some sample data for people to understand what you are looking for but I typed this up real quick. It may give you some other ideas but it may be totaly off.
UPDATE rr
SET EXIST_FLAG = 0
FROM RR_Approval AS rr
JOIN(
SELECTRR_APPROVAL.AcctNoID,
MIN(RR_APPROVAL.INDEX_NO_RECORD_NO) AS INDEX_NO_RECORD_NO,
MIN(RR_APPROVAL.INDEX_NO) AS INDEX_NO
FROMListAllLocs_V
RIGHT OUTER JOINRR_APPROVAL
ON ListAllLocs_V.INDEX_NO = RR_APPROVAL.INDEX_NO
AND ListAllLocs_V.INDEX_RECORD_NO = RR_APPROVAL.INDEX_RECORD_NO
AND ListAllLocs_V.AcctNoID = RR_APPROVAL.AcctNoID
WHERE (ListAllLocs_V.AcctNoID IS NULL)
GROUP BY RR_APPROVAL.AcctNoID
)ASdt
ONdt.AcctNoID = rr.AcctNoID
ANDrr.INDEX_NO_RECORD_NO = dt.INDEX_NO_RECORD_NO
ANDrr.INDEX_NO = dt.INDEX_NO
ANDdt.INDEX_NO_RECORD_NO IS NOT NULL
June 3, 2003 at 6:17 am
Thanks for your insight. Sorry about being vague. Basically the sp gets ids required to update another table while processing through the entire recordset. I noticed something weird happened with the DTS that runs this cursor sp. Around 5/15 this job was running for about 30 minutes, however on 5/17 the job ran for almost 20 hours. I've checked indexing done explain plans and everything appears to be normal. Question what do I need to look at if it's not the sp or SQL? Where do I begin to investigate this weirdness?
Thanks
JMC
JMC
June 4, 2003 at 5:39 am
tkbr0wn,
after getting nowhere I used your SQL and it took 24 seconds to run. I replaced the cursor in the stored proc and used your method. Will run as part of a DTS package to continue additional testing.
Thanks
JMC
JMC
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply