Forum Discussion
Resolver IAzure Upsert Activity very slow with trigger
Hi all,
I a new to ADF and Azure SQL so please be patient. I have an azure sql table and I created the following trigger to modify a last modified date column in azure sql if a copy activity using upsert modifies the row:
CREATE TRIGGER TR_wrsh_ModifiedDate
ON [dbo].[WRSH]
AFTER UPDATE
AS
BEGIN
UPDATE [dbo].[WRSH]
SET ModifiedDate = GETDATE()
WHERE Barcode IN (SELECT Barcode FROM inserted);
-- UPDATE YourTableName SET ModifiedDate = GETDATE() WHERE ID NOT IN (SELECT ID FROM inserted);
END;I've noticed that this absolutely kills performance of the copy activity, even when the column in indexed. Any suggestions here? Is my SQL and understanding of setting this up OK? I just want to create a lastmodified time stamp if the upsert activity results in an update.
6 Replies
- nilendraFabric
Super User
Hello shuhn1229
Your `AFTER UPDATE` trigger performs a correlated update on the entire table, which becomes inefficient for large batches
Optimized Approach would be to Use a JOIN instead of `IN` clause:UPDATE w
SET ModifiedDate = GETDATE()
FROM [dbo].[WRSH] w
INNER JOIN inserted i ON w.Barcode = i.Barcode;This leverages set-based operations more efficiently.
• Ensure `Barcode` is indexed (ideally clustered if it’s the primary key) to optimize the join operationif trigger is not necessary
For high-throughput scenarios, consider temporal tables (Azure SQL feature):ALTER TABLE [dbo].[WRSH] ADD
ModifiedDate datetime2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT GETUTCDATE(),
PERIOD FOR SYSTEM_TIME (ModifiedDate, Garbawgy);Eliminates trigger overhead by auto-tracking changes.
• Provides built-in auditing without custom code.- shuhn1229
Thank you very much, didn;t realize the second was even an option
- how sigjificant of a performance hit do you suspect I'll take with a copy activity for a table in the millions of source/sink with the temporal table
- If I delete the temporal column "ModifiedDate" will this revert the table to its previous form?
Thank you so very much
Hi shuhn1229,
Thank you for reaching out to the Microsoft Fabric Forum Community.
CREATE TRIGGER TR_wrsh_ModifiedDate
ON [dbo].[WRSH]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;UPDATE wrsh
SET ModifiedDate = GETDATE()
FROM dbo.WRSH AS wrsh
INNER JOIN inserted AS i ON wrsh.Barcode = i.Barcode;
END;Using a JOIN with the inserted pseudo-table improves performance by ensuring only the affected rows are updated.
For best results, ensure the Barcode column is indexed — preferably as a primary key or unique non-clustered index — to speed up the join operation in the trigger.
If you found this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to help others in the community.
Thank you & regards,
Prasanna KumarHi shuhn1229,
Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.
Best regards,
Prasanna Kumar
Hi shuhn1229 ,
We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.
If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.
Warm regards,
Prasanna Kumar
Hi shuhn1229,
Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.
This not only closes the loop on your query but also helps others in the community solve similar issues faster.
Thank you for your time and feedback!
Best,
Prasanna Kumar
