I recently tried to run a update statemene with joins on a table that has 200,000 records. I suppose it should be very quick.
But it turned out it ran30 minutes, and still ran. I canceled it, thinking it might be something wrong. I tried to put the joins in a temp table and then updated with 1 join. but still takes long time.
By clicking through the object , I finally found there is a trigger for the table, once there is a change it adds records into other table.
Any other way to troubleshoot the slow query and found the trigger without knowing there is a trigger to the table?
thanks
February 14, 2022 at 7:27 pm
My guess would be that the log file needed to grow dynamically, and that is very slow. That partly would depend on the specific UPDATEs that were done and how wide the data involved was.
But, no matter the size, pre-allocating sufficient log file space will make it run much faster if the current log file doesn't have enough empty space in it. Log file space must be pre-formatted, which slows down those allocations. You can shrink the log back down afterward if you prefer.
Also, verify that the other trigger is very efficiently written. Really well-written triggers should be no problem, but poorly written ones can be deadly to performance.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 14, 2022 at 7:55 pm
I recently tried to run a update statemene with joins on a table that has 200,000 records. I suppose it should be very quick.
But it turned out it ran30 minutes, and still ran. I canceled it, thinking it might be something wrong. I tried to put the joins in a temp table and then updated with 1 join. but still takes long time. By clicking through the object , I finally found there is a trigger for the table, once there is a change it adds records into other table.
Any other way to troubleshoot the slow query and found the trigger without knowing there is a trigger to the table?
thanks
Post the code, please. There's a form of joined-Update that I simply refer to as an "illegal update". I've fixed it in the past for folks where it slammed a handful of CPU's into the wall and took hours to run. After repairing the "illegal update", it ran in seconds and barely showed up on monitoring systems for CPU, Read, and Writes.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2022 at 9:48 pm
Yes, I did check the log space, I see there is plenty space while it is running.
This is a non-production environment for me to scramble data.
So I was able to disable the trigger, then do the update, it takes only 1 second. then I re-enable the trigger.
What I meant is how to find out when I ran the update query what makes it slow.
My guess is to use SQL profiler or extended event to monitor what SQL it executes to trace to the trigger.
February 14, 2022 at 9:51 pm
Also the trigger is to add logs to another table. This is usually used when user change address from user interface web site, so it is usually one record one user at that time. so not problem. But for batch loading into the table, the trigger really slow down the process , so need to disable.
February 14, 2022 at 9:55 pm
Also the trigger is to add logs to another table. This is usually used when user change address from user interface web site, so it is usually one record one user at that time. so not problem. But for match loading into the table, the trigger really slow down the process , so need to disable.
So it seems that the trouble is in the trigger. Either post the code for the trigger and maybe the DDL for the table or the only way we can help is to say "learn the proper way to write a trigger". And STOP disabling triggers in production... 😉 They're there for a reason!
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2022 at 10:04 pm
table and script trigger attached.
Thanks
February 14, 2022 at 10:21 pm
table and script trigger attached.
Thanks
Where? I see no attachments anywhere on this thread,
--Jeff Moden
Change is inevitable... Change for the better is not.
if I got it correct the following likely to perform better - do look at the comment inside it - and as with everything test test and test again.
alter TRIGGER [dbo].[trg_stuAddressChangeLog] ON [dbo].[stuAddress]
FOR UPDATE
AS
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM deleted) AND EXISTS (SELECT 1 FROM inserted)
BEGIN
INSERT INTO dbo.stuAddressChangeLog
( StudentID ,
AddressID ,
ApartmentNum ,
StartDate,
EndDate,
CreateDt ,
CreatedBy
)
SELECT del.StudentID ,
del.AddressID ,
del.ApartmentNum ,
COALESCE(del.ChangeDt,del.CreateDt),
SYSDATETIME(),
SYSDATETIME(),
COALESCE(del.ChangedBy,del.CreatedBy)
from deleted del
--inner hash join inserted ins -- hash join may perform better for high volumnes - use it only after extensive testing and with high volumes of a single update transaction
inner join inserted ins
on ins.studentid = del.studentid
and (ins.addressid <> del.addressid
or ISNULL(ins.ApartmentNum,'') <> ISNULL(del.ApartmentNum,'')
)
-- FROM deleted del
-- WHERE NOT EXISTS
--(SELECT *
--FROM inserted AS ins
--JOIN deleted AS del
--ON ins.studentid=del.studentid AND ins.addressid=del.addressid
--AND ISNULL(ins.ApartmentNum,'')=ISNULL(del.ApartmentNum,'')
--)
END
GO
ALTER TABLE [dbo].[stuAddress] ENABLE TRIGGER [trg_stuAddressChangeLog]
GO
February 15, 2022 at 7:14 pm
yes, the performance are better if I test small batches of updating.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply