Triggers and set based queries

Question

Triggers and set based queries

GabyYYZ

SSCertifiable

Points: 7913
More actions
November 10, 2008 at 6:22 am

#215086

I recently had to run an update on a table for about 14,000 items. The way this particular table works is when you update a row, a trigger is activated, and a document/entry is created for the SAP/Oracle databases to pick up. The way it has been set up (not by me) is simply of the form:
update targetTable
set DateChanged = DateChanged where SerialNumber = '0123456789'
A trigger fires for that entry, even though I didn't change a single thing.
Now the fun begins. I was sent a list of 14,000 items to run this on. I unfortunately did it the hard way, running that statement for each line, but in batches of 1,000 with 'go' separating each batch. I create a table with two columns, SerialNumber and BatchNumber and generated my individual statements based on which batch number a row had.
Would this have been a better option:
update targetTable
set DateChanged = DateChanged where SerialNumber in
(select SerialNumber from temporaryTable where BatchNumber = 1) -- 1, 2, 3 ... up to 14
as apparently triggers can work faster on set based queries?
Thanks.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

Viewing 5 posts - 1 through 4 (of 4 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Amost everything in MS SQL works better as a SET-based operation.

That being said, many triggers are poorly written and either cannot handle batch updates or simply serialize any operations anyway. So, without seeing the trigger code it is impossible to tell you if a SET-based update would have worked much better.

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 2

Here's the pertinent code within the trigger:

UPDATE a

SET a.DateChanged = getdate(),

FROM dbo.tblCatalogData a -- tblCatalogData was my targetTable

INNER JOIN deleted d

ON a.PID = d.PID

What then occurs is an external process logs in and checks the updated columns and generates the info from that.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Then yes, since this trigger is SET-based and not using any sort of cursor, it is going to perform pretty well in a set-based update on the table. You should find that updating in batch will perform much better than updating individual records.

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 4

Thanks...and as a point of reference, the original query of 14000 separate updates, with 'go' placed every 1000 updates, took about eleven hours. With any luck, this will take much less should I have to do this again next time.

As an addendum, I got a lock warning when the query was being run, and I wonder if that batch of 1000 was not updated properly. I'm waiting for confirmation from an analyst to see if all the rows he sent me were processed correctly. If not, at least I can narrow it down by batchNumber.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein