timeout occurs on trigger

Hi, this trigger timeouts, any suggestions?

ALTER TRIGGER [dbo].[Trig_Update_FactAmort]
ON [dbo].[Snap_Customer_NonAccrual_Tbl]
AFTER INSERT
AS
BEGIN
update d
set d.Remain_Amort_Bal = 0,
Load_Modified_Dttm = getdate()
from  Snap_Cust   x with(nolock), customerl b with(nolock), DimCust c with(nolock), Factamt d with(nolock), DimDate e with(nolock)
where x.Cust_Nbr_Key = b.Cust_Nbr_Key
and b.Customer_Nbr_Txt = c.customer_nbr_txt
and c.Cust_Info_Dim_PKey = d.cust_info_dim_pkey
and e.Date_Dim_Pkey = d.Date_Dim_Pkey
and x.default_cd = 'ChargeOff'
and e.day_dt = cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) as DATE)
END
eluceroAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
LowfatspreadConnect With a Mentor Commented:
yes but presumably your update is still scoped by the insert performed on the source table...

we would normally expect to see the Inserted Table referenced as the source for any processing...

that way only the subset of rows that need to be updated will be processed....
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
do you have indexes on these columns
x.Cust_Nbr_Key = b.Cust_Nbr_Key
and b.Customer_Nbr_Txt = c.customer_nbr_txt
and c.Cust_Info_Dim_PKey = d.cust_info_dim_pkey
and e.Date_Dim_Pkey = d.Date_Dim_Pkey
and x.default_cd = 'ChargeOff'


also  
- triggers are not good, you should do this within the stored procedure
- you should mention the type of join instead of separating the tables by commas and a where clause
0
 
joeviCommented:
Perhaps using a join on:
x.Cust_Nbr_Key = b.Cust_Nbr_Key
b.Customer_Nbr_Txt = c.customer_nbr_txt
c.Cust_Info_Dim_PKey = d.cust_info_dim_pkey
e.Date_Dim_Pkey = d.Date_Dim_Pkey

A join would also confirm the relationships bwn tables (and possibly the cause of the perf issue)

Have you tested the update outside of the trigger? Any issues. Hows the performance?

Take blanket 'triggers are not good' comments with a grain of salt.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
eluceroAuthor Commented:
the update works fine, not slow at all.  It actually becomes slow thru a web app.  Would it help if I created the update script in a stor proc and then exec the stor proc thru a trigger?  Or what if I put a case statement before the update, like only perform the update if the default cd = 'Chargeoff', how would I write this in the trigger?  
0
 
Rajesh_mjCommented:
why we need to update all the records for each insert? we can use the "inserted" table in the trigger and we will get the newly inserted records, right?
0
 
eluceroAuthor Commented:
I'm actually update another table based off the insert.  On the query you can see I need to update the factamt table.

ALTER TRIGGER [dbo].[Trig_Update_FactAmort]
ON [dbo].[Snap_Customer_NonAccrual_Tbl]
AFTER INSERT
AS
BEGIN
update d
set d.Remain_Amort_Bal = 0,
Load_Modified_Dttm = getdate()
from  Snap_Cust   x with(nolock), customerl b with(nolock), DimCust c with(nolock), Factamt d with(nolock), DimDate e with(nolock)
where x.Cust_Nbr_Key = b.Cust_Nbr_Key
and b.Customer_Nbr_Txt = c.customer_nbr_txt
and c.Cust_Info_Dim_PKey = d.cust_info_dim_pkey
and e.Date_Dim_Pkey = d.Date_Dim_Pkey
and x.default_cd = 'ChargeOff'
and e.day_dt = cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) as DATE)
END
0
 
LowfatspreadCommented:
are you assuming that

and e.day_dt = cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) as DATE)

will detect the rows modified by your source "update" ?

if so thats very inefficient ....
0
 
LowfatspreadCommented:
how many inserts are done to the base table?

after each insert the (whole) Facamt table is updated

 surely thats not what you intend to happend?

this modification may help (reduce logging etc...)

can you have the load date not be updated "daily" if the balance doesn't need to change?


ALTER TRIGGER [dbo].[Trig_Update_FactAmort]
ON [dbo].[Snap_Customer_NonAccrual_Tbl]
AFTER INSERT
AS
BEGIN
update d
set d.Remain_Amort_Bal = 0, 
    Load_Modified_Dttm = getdate()
from  Snap_Cust   x with(nolock)
Inner Join customerl b with(nolock)
  on x.Cust_Nbr_Key = b.Cust_Nbr_Key
Inner Join DimCust c with(nolock)
  on b.Customer_Nbr_Txt = c.customer_nbr_txt
Inner Join Factamt d with(nolock)
  on c.Cust_Info_Dim_PKey = d.cust_info_dim_pkey
Inner Join DimDate e with(nolock)
  on e.Date_Dim_Pkey = d.Date_Dim_Pkey
Where x.default_cd = 'ChargeOff'
  and e.day_dt = cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) as DATE) 
  and (d.remain_amort_bal is null or d.remain_amort_bal <> 0)
END

Open in new window

0
 
eluceroAuthor Commented:
If someone enters a chargoff in the web app, which is the snap_cust tbl then the Remain_Amort_Bal  field for the last day of the previous month needs to be updated to 0.  Thats exactly what I'm trying to do.  But its slow.
0
 
LowfatspreadCommented:
can you show us the statment used to update the Snap_Customer_NonAccrual_Tbl?

0
 
eluceroAuthor Commented:
Its actuall two triggers off the Customer_nonaccrual_tbl


Alter TRIGGER [dbo].[Trig_Insert_Snap_Customer_NonAccrual_Tbl]
ON [dbo].[Customer_NonAccrual_Tbl]
AFTER INSERT
AS
BEGIN
Insert Snap_Customer_NonAccrual_Tbl
(Cust_Nbr_Key,Default_Cd,Effective_Dt,End_Dt,Last_Modified_Dttm, Load_Dttm)
select n.Cust_Nbr_Key,n.Default_Cd,n.Effective_Dt,n.End_Dt,NULL, getdate()
from inserted n with(nolock)
where Delete_Flg = 'N'
END

ALTER TRIGGER [dbo].[Trig_Update_Snap_Customer_NonAccrual_Tbl]
ON [dbo].[Customer_NonAccrual_Tbl]
AFTER UPDATE
AS
BEGIN
update n
set n.cust_nbr_key = x.cust_nbr_key,
n.default_cd = x.default_cd,
n.Effective_Dt = x.Effective_Dt,
n.End_Dt = x.End_Dt,
n.Last_Modified_Dttm = GETDATE()
from inserted x, Snap_Customer_NonAccrual_Tbl n
where n.Cust_Nbr_Key = x.Cust_Nbr_Key
and n.Default_Cd = x.Default_Cd
and x.Delete_Flg = 'N'
END
GO
0
 
LowfatspreadCommented:
fine

and the sql which actual triggers these is ?
0
 
eluceroAuthor Commented:
when the data is entered into the customer_nonaccrual_tbl via a web app, the stor procs are.  When it exec the first one is when it takes so long.  


ALTER PROCEDURE [dbo].[stp_FAS91_Insert_NonAccrual]
     @v_Source_System_Cd varchar(3),
     @v_Customer_Nbr_Txt varchar(10),    
     @v_Effective_Dt datetime,
     @v_End_Dt datetime,
     @v_Updated_By_Txt varchar(50)
     
AS
BEGIN
DECLARE @Cust_Nbr_Key int


BEGIN TRANSACTION

    SELECT @Cust_Nbr_Key = Cust_Nbr_Key
    FROM dbo.Customer_Header_Tbl A
    WHERE Source_System_Cd = @v_Source_System_Cd AND CAST(Customer_Nbr_Txt as int) = CAST( @v_Customer_Nbr_Txt as int) AND SubAcct_Nbr = 0
   
      INSERT INTO [DW_ODS].[dbo].[Customer_NonAccrual_Tbl]
                     ([Cust_Nbr_Key]
                     ,[Default_Cd]
                     ,[Effective_Dt]
                     ,[End_Dt]
                     ,[Updated_By_Txt]
                     ,[Delete_Flg]
                     ,[Last_Modified_Dttm])
             VALUES
                     (@Cust_Nbr_Key
                     ,'NonAccrual'
                     ,@v_Effective_Dt
                     ,@v_End_Dt
                     ,@v_Updated_By_Txt
                     ,'N'
                     ,GETDATE())
   
COMMIT TRANSACTION
END

GO



ALTER PROCEDURE [dbo].[stp_FAS91_Insert_ChargeOff]
     @v_Source_System_Cd varchar(3),
     @v_Customer_Nbr_Txt varchar(10),    
     @v_Effective_Dt datetime,
     @v_Updated_By_Txt varchar(50)
     
AS
BEGIN
DECLARE @Cust_Nbr_Key int


BEGIN TRANSACTION

    SELECT @Cust_Nbr_Key = Cust_Nbr_Key
    FROM dbo.Customer_Header_Tbl A
    WHERE Source_System_Cd = @v_Source_System_Cd AND cast(Customer_Nbr_Txt as int) = cast( @v_Customer_Nbr_Txt as int) AND SubAcct_Nbr = 0
   
      INSERT INTO [DW_ODS].[dbo].[Customer_NonAccrual_Tbl]
                     ([Cust_Nbr_Key]
                     ,[Default_Cd]
                     ,[Effective_Dt]
                     ,[End_Dt]
                     ,[Updated_By_Txt]
                     ,[Delete_Flg]
                     ,[Last_Modified_Dttm])
             VALUES
                     (@Cust_Nbr_Key
                     ,'ChargeOff'
                     ,@v_Effective_Dt
                     ,'9999-12-31'
                     ,@v_Updated_By_Txt
                     ,'N'
                     ,GETDATE())
   
COMMIT TRANSACTION
END

GO
0
 
eluceroAuthor Commented:
I got it, it was because I named the tbl instead of using inserted, I figured it out from what you said

we would normally expect to see the Inserted Table referenced as the source for any processing...

thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.