question

Majdoleen avatar image
Majdoleen asked

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

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:

sql-server-2012triggerviewstriggers
1.png (117.1 KiB)
4.png (127.6 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.