Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here?
In my case, I am using a trigger to read from two view tables (Decisions2 and Con_Decisions_Groupby), each table contains more than 20000 records, and only I want the trigger to work with the updated values and if a new record is added to the view table, and not to go through all the records in order to hide the delay.
This is my trigger: (I am not sure if it is written correctly to cover my case)
USE [W1]
GO
/****** Object:Trigger [azteca].[trg_WOcommittee_02]Script Date: 6/5/2018 10:23:41 AM ******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
ALTERTRIGGER [azteca].[trg_WOcommittee_02]
ON [azteca].[WORKORDER]
AFTERUPDATE,insert
AS
UPDATE [azteca].[REQUEST]
SET [azteca].[REQUEST].[TEXT11] = [dbo].[Decisions2].[L1_Desecion],
[azteca].[REQUEST].[TEXT12] = [dbo].[Decisions2].[L2_Desecion],
[azteca].[REQUEST].[TEXT13] = [dbo].[Decisions2].[R1_Desecion],
[azteca].[REQUEST].[TEXT14] = [dbo].[Decisions2].[R2_Desecion],
[azteca].[REQUEST].[TEXT4] = [dbo].[Decisions2].[HPC1_Desecion],
[azteca].[REQUEST].[TEXT15] = [dbo].[Decisions2].[SPC2_Desecion],
[azteca].[REQUEST].[TEXT5] = [dbo].[Decisions2].[Drafting],
[azteca].[REQUEST].[TEXT2] = [dbo].[Decisions2].[Auditing_Desecion],
[azteca].[REQUEST].[TEXT20]= [dbo].[Con_Decisions_Groupby].[TEXT20]
FROM [azteca].[REQUEST]FULLJOIN [dbo].[Decisions2] ON [azteca].[REQUEST].[REQUESTID] = [dbo].[Decisions2].[REQUESTID]
Fulljoin [dbo].[Con_Decisions_Groupby] on [azteca].[REQUEST].REQUESTID =[dbo].[Con_Decisions_Groupby].REQUESTID
Kindly find the attached screen shot which shows all the story:
