Trigger Conundrum

  • tfifield

    SSCrazy Eights

    Points: 9655

    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

  • GSquared

    SSC Guru

    Points: 260824

    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

  • Jack Corbett

    SSC Guru

    Points: 184381

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply