0

take2 of this post since it was missing information and it was closed prior to the update that I added.

I have an update trigger that inserts into auditing tables. We had no problem until someone decides to update over 1 million records... (That's my bad. I didn't think it would be a problem when developing). Now facing reality, I need to find a solution...

This is a simplified version of the real thing

-- drop trigger PriceHist_trig_U 
-- drop table MyPriceTable
-- drop table price_history
Create Table MyPriceTable (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100)
CONSTRAINT [PRICE_TAB_P01] PRIMARY KEY CLUSTERED 
(
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Declare @Id int
Set @Id = 1

While @Id <= 1000000
Begin 
   insert into MyPriceTable values (right('000000000000' + CAST(@Id as nvarchar(10)),13),'Grp ' + CAST(@Id%10 as nvarchar(10)), @id%3, RAND()*(25-10)+10, 1, 'there are many other columns')
   Print @Id
   Set @Id = @Id + 1
End

-- Drop table   price_history 
create table price_history (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100), historyDate datetime, ChangedColumns varchar(Max))
CREATE NONCLUSTERED INDEX price_history_nc1 ON price_history
(
    HistoryDate ASC,
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC
)

go
Create TRIGGER PriceHist_trig_U ON MyPriceTable FOR UPDATE 
AS 
INSERT INTO price_history (SKU, PriceGroup, PriceLevel, price, Qty, ManyOtherColumns, HistoryDate, ChangedColumns) 
            SELECT INS.SKU,INS.PriceGroup,INS.PriceLevel,INS.Price,INS.Qty,INS.ManyOtherColumns, getdate(),  
CASE WHEN update(Price) and INS.Price<>DEL.Price THEN 'Price-' ELSE '' END +
CASE WHEN update(Qty) and INS.Qty<>DEL.Qty THEN 'Qty-' ELSE '' END +
CASE WHEN update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns THEN 'other-' ELSE '' END 
FROM INSERTED INS 
JOIN DELETED DEL ON DEL.sku=INS.sku AND DEL.PriceGroup=INS.PriceGroup AND DEL.PriceLevel=INS.PriceLevel 
WHERE  (update(Price) and INS.Price<>DEL.Price) 
    OR (update(Qty) and INS.Qty<>DEL.Qty) 
    OR (update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns)

/* tests */ 
update MyPriceTable set price = price-1

I've been doing many tests and researches to try to figure out how to solve my issue of having a trigger perform poorly... I've come to the conclusion that to minimize the bad performance of the "Table Insert" in the execution plan, I need to insert in smaller batches.

The question is: Since I'm not sure of where all the different updates can come from, I'm trying to figure out how I can insert the auditing records in batches within the trigger?

example, The update of the main table for 1 million records would happen and call the trigger, which would insert 100 thousand records at a time in some type of loop.

Is this possible? If so, how do you suggest? If not, how else can I improve the table insert of the execution plan?

5
  • 4
    You should wait for the original Slow Trigger performance when big batches to get reopened instead of opening a duplicate question. Commented Apr 20, 2020 at 19:54
  • @LowlyDBA, This is confusing ... the blue box that alerts me that the question was closed states, to edit the question or post a new one. I waited 4 days and thought since it will not be re-opened, I will post a new question. How long is the usual wait for a question to re-open?
    – JohnG
    Commented Apr 20, 2020 at 20:09
  • 1
    @JohnG There is nothing fundamentally wrong with opening a new question. But you should add more information, in particular the execution plan of the long running query. If you do that, I'll gladly vote to re-open the original question. Commented Apr 21, 2020 at 6:46
  • @LaurenzAlbe I tried adding the XML execution plan but it's too big... How else can I attach an execution plan?
    – JohnG
    Commented Apr 22, 2020 at 19:39
  • @LaurenzAlbe unfortunately, it's giving an error... Can't create explain! Explain "top_node" is undef! at /home/depesz/sites/explain.depesz.com/lib/Explain/Controller.pm line 220.
    – JohnG
    Commented Apr 22, 2020 at 19:52

1 Answer 1

0

Instead of doing all that work in the update trigger, you could just capture the DELETED rows and worry about what actually changed, and formatting a pretty message later. EG

create table price_history (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100), historyDate datetime, ChangedColumns varchar(Max))
CREATE unique clustered INDEX price_history_nc1 ON price_history
(
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC,
    HistoryDate ASC
)

go
Create or alter TRIGGER PriceHist_trig_U ON MyPriceTable FOR UPDATE 
AS 
INSERT INTO price_history (SKU, PriceGroup, PriceLevel, price, Qty, ManyOtherColumns, HistoryDate) 
            SELECT DEL.SKU,DEL.PriceGroup,DEL.PriceLevel,DEL.Price,DEL.Qty,DEL.ManyOtherColumns, getdate() 
FROM DELETED DEL 

Or try skipping the trigger and using Change Tracking, Change Data Capture, or Temporal Tables instead.

3
  • I initially researched on that a while back and had constraints if my memory serves me right. See, this needs to run on all possible versions of SQL server standard and express... As for skipping the trigger, I can't see how I can skip it since the history table is used for other stuff and needs to be realtime.
    – JohnG
    Commented Apr 22, 2020 at 19:31
  • Temporal Tables and Change Tracking are available in all editions of SQL Server. Commented Apr 22, 2020 at 19:33
  • CT changes were introduced in SQL 2008 (not sure when in 2008 ... if it was RTM, or R2 SP2 like I've seen other functionalities introduced in the middle of versions) ... unfortunately, we still have customers that use SQL 2005.
    – JohnG
    Commented Apr 22, 2020 at 19:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.