May 6, 2009 at 7:37 am
I have the following queries that process the same data. The select buzzes hrough 160K rows in less than a second. Execution plan looks good, hitting my indexes for a cost for each of 48%. The update, with the same join on clause, hits my indexes but then goes into a table spool/eager spool, at a cost of 58%, and my index seeks drop to a cost of 8%. I kill the process after multiple minutes of processing. The entire tables contains about 3 million rows of data but I do have appropriate indexes for the processing that we do, or so I thought. Again, the select returns very quickly. I also know the table spool is using a temp table. Is there any way to avoid this? I can provide data and DDL if desired but I guess I'm looking for direction on why the execution plans are so vastly different and is there something I should be doing differently?
UPDATE s
SET s.trigger_value = 'Discard'
FROM eligibilitystaging s
INNER JOIN eligibilitystaging x
ON s.interchangekey = x.interchangekey
AND s.employee_ssn = x.client_ssn
AND s.interchangekey = 2362
AND s.record_type = 'E'
AND x.record_type = 'D'
select s.*,x.* FROM eligibilitystaging s
INNER JOIN eligibilitystaging x
ON s.interchangekey = x.interchangekey
AND s.employee_ssn = x.client_ssn
AND s.interchangekey = 2362
AND s.record_type = 'E'
AND x.record_type = 'D'
Edit: Sorry, running SQL 2000, sp4
-- You can't be late until you show up.
May 6, 2009 at 7:48 am
Early morning brain cramp! :rolleyes: What I did to get around this was create a temp table with my select results and changed the update process to join to the temp table. Runs in milliseconds.
select s.employee_ssn, s.interchangekey into tempDiscard
FROM eligibilitystaging s
INNER JOIN eligibilitystaging x
ON s.interchangekey = x.interchangekey
AND s.employee_ssn = x.client_ssn
AND s.interchangekey = 2362
AND s.record_type = 'E'
AND x.record_type = 'D'
UPDATE s
SET s.trigger_value = 'Discard'
FROM eligibilitystaging s
INNER JOIN tempDiscard x
ON s.interchangekey = x.interchangekey
AND s.employee_ssn = x.employee_ssn
However, if anyone has any input to my OP with repect to why the plans were so different, I would appreciate your effort to enlighten me!
-- You can't be late until you show up.
May 6, 2009 at 8:13 am
Update and select don't necessarily end up with the same plan. For that matter, the same select can end up with different plans sometimes, under the right circumstances.
Something you might try is creating a view based on the select, and try updating the view. Just to see how well it performs.
Not sure from your posts, but I'd definitely go with making the tempDiscard table into an actual temp table, with # at the beginning of the name. You may already be doing that and it just didn't make it into the post, but it doesn't appear that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2009 at 9:46 am
Thanks Gus, I appreciate the feedback. I did add the # to tempDiscard after my post. It's all wrapped up in a stored proc and, so far anyway, is performing wonderfully. Still trying to get my hands around the execution plans but I'm making progress. Next task is getting our new SQL 2005 box fully into production!
-- You can't be late until you show up.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply