CONTEXT_INFO and efficient mass update on a large table with a trigger

Let`s assume we have following task – make changes to the data in a large table that has consistency checker in a trigger. Let`s look at the ways to do it in a most efficient way.

Use test database in a simple recovery mode. Create two tables and fill them by random data.

Product table:

 

IF OBJECT_ID('product') IS NOT NULL DROP table product GO CREATE TABLE product (product_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY clustered, [name] SYSNAME NOT NULL, created_on DATETIME2 NOT NULL DEFAULT getdate()) GO WHILE 1 = 1 BEGIN INSERT product (name) SELECT TOP 50 name from sys.objects IF (SELECT COUNT(*) FROM product) >= 50000 break END WHILE 1 = 1 BEGIN INSERT product (name) SELECT TOP 50000 name from product IF (SELECT COUNT(*) FROM product) >= 5000000 break END

 

Finally we have table with about 5 million product records

 

Create order table that references the product table

 

IF OBJECT_ID('order') IS NOT NULL DROP table [order] GO CREATE TABLE [order] (order_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY clustered, product_id INT NOT null, created_on DATETIME2 NOT NULL DEFAULT getdate(), closed_on datetime2) GO CREATE INDEX IX_product ON [order](product_id) GO ALTER TABLE [order] ADD CONSTRAINT FK_order_product FOREIGN KEY (product_id) REFERENCES product(product_id)

 

Orders may have close date (closed orders):

 

WHILE 1 = 1 BEGIN INSERT [order] (product_id, closed_on) SELECT product_id, GETUTCDATE() FROM product IF (SELECT COUNT(*) FROM [order]) >= 20000000 break END

20 million orders

 

And orders may not have close date (not closed orders):

 

INSERT [order](product_id, closed_on) SELECT product_id, null from product

 

About 5 million records

 

Let`s say we have a rule that says:

“Record in a product table can’t be modified unless there are not closed orders related to its products.”

IF OBJECT_ID('trg_product') IS NOT NULL DROP TRIGGER trg_product go CREATE TRIGGER trg_product ON product FOR update AS BEGIN IF EXISTS(SELECT 1 FROM DELETED d JOIN INSERTED i ON i.product_id = d.product_id JOIN [order] o ON o.product_id = i.product_id WHERE i.name <> d.name AND o.closed_on IS null) BEGIN RAISERROR ('There are products in unclosed orders!', 16, 1) ROLLBACK TRAN end end

 

If we want to change names for products with id <= 1M we have to wait some time:

 

update product set name = name + '_new' WHERE product_id <= 1000000

45 seconds duration on my test server

 

Can we improve it?

 

Split update for some small bunches of 100 thousands records:

 

declare @a int = 1, @b INT = 1, @delta INT = 100000 WHILE @b < 1000000 BEGIN SET @b = @a + @delta update product set name = name + '_new' WHERE product_id between @a and @b SET @a = @a + @delta END

This loop worked 10 times and took 15 seconds to run. So we got a 3 times improvement. Not that this is specific to the server.

 

Can we speed it up more? If we are sure that our changes don’t require trigger’s check we can switch off the trigger. But then the trigger will not work for any other users too.

Is there a possibility to disable a trigger in our current session only?

Yes! Add the following hint to the trigger:

 

IF OBJECT_ID('trg_product') IS NOT NULL DROP TRIGGER trg_product go CREATE TRIGGER trg_product ON product FOR update AS BEGIN IF cast(cast(CONTEXT_INFO() as binary(4)) as int) = 001 RETURN IF EXISTS( SELECT 1 FROM DELETED d JOIN INSERTED i ON i.product_id = d.product_id JOIN [order] o ON o.product_id = i.product_id WHERE i.name <> d.name AND o.closed_on < '2015-01-01') BEGIN RAISERROR ('This product is in unclosed orders!', 16, 1) ROLLBACK end end

Try it now:

 

SET CONTEXT_INFO 001 update product set name = name + '_new' WHERE product_id <= 1000000

Running time – 12 seconds

 

Because we set special flag context_info for our session, trigger doesn’t fire for our session. But it fires for all other users`s sessions that do not have this flag enabled.

Finally we have an option to run mass DML queries bypassing trigger restrictions.

 

by Alexey Tikhomirov