High Performance Trigger with SQL Server Service Broker
High Performance Trigger with SQL Server Service Broker
By : Kasim Wirama,
MCITP, MCDBA
This
posting, I would like to describe practical use of service broker in
trigger. Service broker has been introduced since SQL Server 2005 and it
would be still one of its core features. Investment on service broker
would result high performance database through its reliable asynchronous
nature for its message-based architecture
Trigger
has been suspected as database evil because it’s synchronous nature to
commit/rollback transaction. By implementing service broker inside
trigger, you change performance bottleneck on trigger becoming high
performance trigger. I show you sample scenario.
Simple
scenario is to calculate 2 columns of a table below and put into
‘summary column’ when insertion happens. This is very simple case and it
could be achieved by other ‘straight-forward’ solution BUT I just show
how to implement service broker inside trigger.
Create
sample table first in any user database that you prefer.
create table dbo.test( col1 int identity(1,1) primary key, col2 int not null, col3 int not null, sum_col2_col3 int null);
Now
create message type as shown below :
create message type [myMessageType]validation = well_formed_xml;go Create
message contract here :
create contract [myContract]([myMessageType]
sent by any); Create queue :
create
queue [myQueue];
Create
service to sit on top of queue and bind to message
contract :
create service [myService]on queue [myQueue]([myContract]);
create
procedure to process message on the queue here :
create procedure dbo.[proc]asbegin ; set nocount on; declare @conversation_handle uniqueidentifier; declare @message_type_name sysname; declare @message_body xml; receive top (1) @conversation_handle
= conversation_handle,
@message_type_name = message_type_name,
@message_body = message_body from [myqueue]; if (@conversation_handle is not null and @message_body is not null) begin ;
declare @tmp table (col1 int, col2 int,
col3 int);
insert into @tmp (col1, col2, col3)
select q.col.value ('@col1','int'),
q.col.value ('@col2','int'),
q.col.value ('@col3','int')
from @message_body.nodes('/row') as q(col);
update dbo.test
set sum_col2_col3 = t1.col2 + t1.col3
from dbo.test as t , @tmp as t1
where t.col1 = t1.col1; end ;end ;
Now
alter the queue to include the stored procedure and set it active with
STATUS = ON.
alter queue [myQueue]with activation
( status = on, procedure_name = dbo.[proc], max_queue_readers = 10, execute as self
);
The
last step is to create corresponding insert trigger on TEST table here
create trigger dbo.trg_i_teston dbo.testafter insertasbegin ; set nocount on; declare @handle uniqueidentifier; declare @message xml; set @message = (
select i.col1,
i.col2,
i.col3
from inserted as i
for xml raw, type
); begin dialog conversation @handle from service [myService] to service 'myService' on contract [myContract] with encryption =off; send on conversation @handle message type [myMessageType](@message);end ;
Now,
try to insert row into TEST table here
insert
into test (col2, col3)
values (4,6); After 1
seconds, query TEST table, and you will find out that sum_col2_col3 ccolumn has value 10.
Service
broker has been one of alternative in asynchronous nature in SQL Server
and even it could replace replication (read mySpace study case about
SQL Server implementation).