Trigger - The timeout period elapsed prior to completion

[Anddmx]

Hi,

I have create Trigger on table to update another table on different server. The Trigger sends parameters to stored procedure hoping to make this process run faster but I keep getting Timeout error now. I know the trigger works find but when parameters get sent to store procedure if were the problem is, I think my production table is way to big for this to update in timely manner. Both are linked servers. So I am asking how to make this run smoother without timeout happening, if I have to change something all do it not problem because I know something has to change here and in store procedure if I remove the update statement everything works fine.

Below are my Tigger and SP.....

TRIGGER--

CREATE TRIGGER UpdateBrandID ON [dbo].[Pnr]
AFTER INSERT, UPDATE
AS

SET XACT_ABORT ON

DECLARE @ResID char(100)
DECLARE @CST varchar(100)
DECLARE @Status varchar(20)


IF (UPDATE(ReservationID))
BEGIN

Set @ResID = (Select ReservationID FROM INSERTED)

Set @CST = (Select ClientSubTrack From INSERTED)

Set @Status = (Select Status From INSERTED)

EXEC sp_UpdateBrandID @ResID, @CST, @Status
END


STORE PROCEDURE--

CREATE PROCEDURE sp_UpdateBrandID
(@ResID varchar(100),
@CST varchar(100),
@Booked varchar(20)
)
AS

Set nocount on

Declare @Brand varchar(100)
Declare @Route varchar(100)
Declare @ResFullID varchar(100)
Declare @Username varchar(100)
Declare @UserID varchar(100)

Begin

If (@Booked = 'Booked' AND @CST IS NOT NULL AND @ResID IS NOT NULL)

Begin

Set @Brand = (Select substring(@CST, patindex('%brand!%', @CST)+6, patindex('%route!%', @CST)-10))

Set @Route = (Select substring(@CST, patindex('%route!%', @CST)+6, patindex('%)', @CST)-17))

Set @ResFullID = (Select substring(@ResID, patindex('%travelworm_%', @ResID) + 11, patindex('%no%', @ResID)-12))

Set @Username = (Select UserName From Production.Reservation.dbo.tblRoute Where Route = @Route)

Set @UserID = (Select UserID From Production.Reservation.dbo.tblRoute Where Route = @Route)





UPDATE Production.Res.dbo.Reservations SET
BrandID = RTRIM(@Brand),
UserName1 = RTRIM(@Username),
UserID1 = RTRIM(@UserID)
WHERE ReservationID = RTRIM(@ResFullID)
End


If (@Booked = 'Booked' OR @Booked = 'add_to' AND @CST IS NULL)
Begin

Set @ResFullID = (Select substring(@ResID, patindex('%travelworm_%', @ResID) + 11, patindex('%no%', @ResID)-12))

UPDATE Production.Res.dbo.Reservations SET
BrandID = 1
WHERE ReservationID = RTRIM(@ResFullID)

End

End
GO

 

[SQLSister]

As a start I'd get rid of the rtrims inthe update. These are variables so they shouldn't need them.

More importantly your trigger is designed to fail if there is ever a multiple update to the table. Never user any circumstances assume there will be only one record in inserted. If you had multiple records only one of them would be processed by the trigger.

Questions about posting. See faq183-874

 

[Anddmx]

Ok,

I removed RTRIM from varibles, now I still have the same problem with the timeout. I dont understand why this is so hard to get working... trigger just passes paramaters to SP then the SP should do all the work.

I have to remove reservationID, BrandID, Route from string values with patindex and this is the only why I can lookup values in my tables.

What is the best way to update table on another sql server?

 

[SQLDenis]

firs of all why do you have 3 select when you can do this in one?

Set @ResID = (Select  ReservationID FROM INSERTED)
    
     Set @CST = (Select ClientSubTrack From INSERTED)

    Set @Status = (Select Status From INSERTED)

select @ResID  =ReservationID ,@CST  =ClientSubTrack ,@Status  = Status From INSERTED

second of all you should not use this because of multi-row updates, you will have to loop thru the inserted table

same in your proc

Set @Username = (Select UserName From Production.Reservation.dbo.tblRoute Where Route = @Route)

             Set @UserID = (Select UserID From Production.Reservation.dbo.tblRoute Where Route = @Route)

instead of

Select @Username  =UserName,@UserID  = UserID From Production.Reservation.dbo.tblRoute Where Route = @Route

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[SQLDenis]

what is your execution plan for the update statement?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[Anddmx]

Hi SqlDenis,

I didnt know you could combined that into one statement or I would have done it. This is really my first time with trigger. OK all make the changes and let you know how it goes.

Thanks for the help.

 

[Anddmx]

OK,

So I made changes and everything runs alot faster now.

Update execution plan...

I have job that runs alot SP every hour at 59 min Example "1:59:59", now that updates the reservation table on production. Update takes 2 mins just because reservation table has over 800,000 records ("I need to archive that table"), that is were I had HUGE bottle neck. It was taking too long that my trigger error out.


Insert XML file into Database > Trigger sents paramaters to SP that inserts into Table > SP once hour updates reservation table on production if conditions are meet.


What I was trying to do was just update straight from trigger on PNR table, this didnt work because reservation table on production took to long to update because of the size of the table.

If there is away to update my reservation table ("Which is over 800,000 records") from trigger I would like to know how because everything I have tried faults. That's why I just insert it into CST table and update from there.

Thanks for the help SQLDenis...