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.