Trigger: Serial vs. Parallel 1

[LeonAtRC]

Since the code in a trigger runs serially does that mean a called sproc needs to finish running before the trigger can continue and run another sproc or do other processing?

Alter TRIGGER tr_Times_INSERT_Laps
ON dbo.Times
AFTER INSERT
AS
DECLARE @tmID int
SET @tmID = (SELECT Id FROM Inserted)
EXEC ec_Live_Processing @tmID
EXEC ec_2nd_Sproc @tmID
EXEC ec_3rd_Sproc @tmID

In other words: do each of these sprocs need to finish before the next one starts or will they run in parallel?
Thanks,
--Leon

 

[JayKusch]

I beleive they will run in parallel, at the same time ... almost. Issue here may be that you will create a deadlock depending on what each SP is trying to complete.

Thanks

J. Kusch

 

[LeonAtRC]

Thanks Jay, I'll keeep the deadlock issue in mind.
So far 3 the SPs are not working on related tables.

--Leon

 

[dky1e]

These procedures will run in seriels, not parallel.

Try this for yourself:

create table dbo.tmptest( i int )
go

create TRIGGER tr_ins_test
ON dbo.tmptest
AFTER INSERT
AS
DECLARE @tmID int
SET @tmID = (SELECT i FROM Inserted)
EXEC p1 @tmID
EXEC p2 @tmID
EXEC p3 @tmID

go


create proc p1 @i int
as
waitfor delay '00:00:05'  --wait 5 sec
select 1
go

create proc p2 @i int
as
waitfor delay '00:00:03'  --wait 3 sec
select 2
go

create proc p3 @i int
as
waitfor delay '00:00:01'  --wait 1 sec
select 3
go

insert into dbo.tmptest(i)
select 1

In which order do you get the results and how long does it take to complete?

 

[LeonAtRC]

dky1e

Thank you!
They ran in order: 1,2,3 and it took exactly 9 seconds.

So apparently triggers are very handy tools but they have their limitations as far as speed are concerned.

For my application I still need to use them but I'll try to minimize whatever sproc they are calling. (Or build the code directly into the trigger.)

Very helpful! And you gave a complete but concise explanation.

--Leon

 

[hspoulsen]

Leon,

They do run in sequential order, as you have found out.
I've found out the hard way, because I had a spoc sending out an email. The email system got stuck, and my SQL server frose up right behind it.

We changed the system, so that the sproc inserted a record in a new table. Then we had a task executing every 20 sec, and this would send out the email.

Regards
Henrik