Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1328
  • Last Modified:

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
0
elucero
Asked:
elucero
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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:
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now