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

 

Rate

Share

Share

Rate