Sql-server – Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here

ddl-triggersql serversql-server-2012triggerview

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 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [azteca].[trg_WOcommittee_02]
   ON [azteca].[WORKORDER]
   AFTER UPDATE, 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]  FULL JOIN [dbo].[Decisions2] ON [azteca].[REQUEST].[REQUESTID] = [dbo].[Decisions2].[REQUESTID] 
                       Full join [dbo].[Con_Decisions_Groupby] on [azteca].[REQUEST].REQUESTID =  [dbo].[Con_Decisions_Groupby].REQUESTID

Kindly find the attached screen shot which shows all the story:
enter image description here

enter image description here

Best Answer

Sorry for the confusion, the view table is reading from the azteca.workorder table and when adding the trigger to the view table, the trigger is not working, please find the code of the view tables: [dbo].[Decisions2]>>>

SELECT        REQUESTID, Communities, ObjID, [L1] AS [L1_Desecion], [L2] AS [L2_Desecion], [R1] AS [R1_Desecion], [R2] AS [R2_Desecion], [HPC1] AS [HPC1_Desecion], [HPC2] AS [SPC2_Desecion], [Drafting] AS [Drafting], 
                     [Auditing] AS [Auditing_Desecion], Attachments, PROBLEMSID

FROM (SELECT TEXT1, REQUESTID, Communities, ObjID, Attachments, COMMENTS, PROBLEMSID FROM dbo.RequestToWorkorder) src PIVOT (max(TEXT1) FOR COMMENTS IN ([L1], [L2], [R1], [R2], [HPC1], [HPC2], [Drafting], [Auditing])) piv;

[dbo].[Con_Decisions_Groupby]>>>

SELECT        MAX(REQUESTID) AS REQUESTID, TEXT20

FROM dbo.Con_Decisions GROUP BY REQUESTID, TEXT20