July 28, 2009 at 1:45 pm
Hi guys,
I was wondering if anyone knows why IF UPDATE(Column) takes so long to execute. I was working with a stored procedure and a trigger dealing with the Customer table.
The procedure, which I didn't write and was not allowed to re-write, used a cursor to selectively update the customer table. The cursor itself, without the update, actually runs very fast - less than 1 second. With the customer records being upated, about 60 records took nearly 30 seconds to run.
I looked at the update trigger on the customer table and there were 2 functions (1 to check if a proprietary replication process 2as running and the other to check Trigger Nest Level) and then IF UPDATE(CustEMail) BEGIN and some code.
The code below IF UPDATE(CustEMail) was never executed and I verified that by dumping to a log table after the test - so I know that it always tested FALSE.
I put a return above the IF UPDATE(CustEMail) and the whole procedure took less than 1 second to run.
I put some code in to dump GETDATE() before and after the UPDATE test and it seemed to take about 0.4 seconds each time.
I finally fixed the procedure to do a set based update rather than individual updates and the performance was fine. (The powers had to be convinced that it was the individual updates that were causing the problem)
My question is how come this UPDATE(CustEMail) test takes so long? I thought that it just checks for what is being updated from the SQL update statement.
Todd Fifield
July 28, 2009 at 3:11 pm
I've never seen that one take any measurable amount of time, so I really can't say why it would in your case.
Can you provide table definitions and an insert statement for sample data, and the trigger and proc code, so we can test it?
- 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
July 29, 2009 at 9:12 am
This is just a guess, but I would think the fact that the updates are running in a cursor may be causing some locking/blocking issues, possibly in tempdb. Are there any explicit transactions within the cursor or is it all in one big implicit transaction?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply