Solved

timeout occurs on trigger

Posted on 2011-02-28
14
950 Views
Last Modified: 2012-08-13
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
Comment
Question by:elucero
14 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 35002992
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
 
LVL 4

Expert Comment

by:joevi
ID: 35003474
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
 

Author Comment

by:elucero
ID: 35003713
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
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 35003757
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
 

Author Comment

by:elucero
ID: 35003784
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35004360
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35004812
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35004938
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
 

Author Comment

by:elucero
ID: 35008444
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35008535
can you show us the statment used to update the Snap_Customer_NonAccrual_Tbl?

0
 

Author Comment

by:elucero
ID: 35008583
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35008937
fine

and the sql which actual triggers these is ?
0
 

Author Comment

by:elucero
ID: 35009073
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
 

Author Comment

by:elucero
ID: 35012542
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now