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
