Sending SSB messages out of trigger with no cursor!

[Farmer]

Here is a tip I can share that may help others that use SQL Service Broker in the way I do.

 

Definition:

 

I have queue tables that have triggers defined on them, which fire messages on INSERT/UPDATE to SQL Server Broker for processing, sort of like this below.

Sometimes I need to fire messages for 20,000 inserted records!

I have a cursor that simply walks INSERTED recordset and sends messages. I never liked this solution as I hate cursors but had no choice; that is what I thought.

So, processing is very simple: get conversation handle from table, generate XML and SEND... away, relying on implicit conversion to varbinary(max)!

 

CREATE TRIGGER dbo.somename
ON dbo.SomeQueue
AFTER INSERT, UPDATE
AS
IF @@ROWCOUNT = 0
    RETURN
SET NOCOUNT ON

 

BEGIN TRY
BEGIN

 

    DECLARE @conversation_handle uniqueidentifier
        ,@Task XML

 

    DECLARE crx CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        actt.InitiatorConversationHandle
        ,(
            SELECT
                 act.acttID                                             as [@acttID]
                ,q.actID                                                as [@actID]
                ,a.actIDInstance                                        as [@actIDInstance]
                ,act.actoID                                             as [@actoID]
                ,q.FromsttID                                            as [@FromsttID]
                ,q.TosttID                                              as [@TosttID]
                ,COALESCE(p1.SystemPrinter, p2.SystemPrinter)           as [@Printer]
                ,q.CreatedOn                                            as [@CreatedOn]
                ,q.CreatedUserName                                      as [@CreatedUserName]
            FOR XML PATH ('Task'), TYPE
        ) as [Task]
    FROM INSERTED q -- must use INSERTED, otherwise deadlocking will happen
    -- some more JOINs here

 

    OPEN crx
    FETCH NEXT FROM crx INTO @conversation_handle, @Task
    WHILE @@FETCH_STATUS = 0
    BEGIN

 

        SEND ON CONVERSATION @conversation_handle MESSAGE TYPE [Task] (@Task)
           
        FETCH NEXT FROM crx INTO @conversation_handle, @Task
    END
   
    CLOSE crx
    DEALLOCATE crx

 

END
END TRY

Doing dynamic SQL was scary for me due to injection attacks via some text fields in XML string.

Here is an alternative that avoids such issues and uses no cursor! Note that InitiatorConversationHandle is uniqueidentifier type.

It's for SQL 2008.

 

It converts generated XML task into varbinary(max), which incapsulates all XML contents into binary format,

then uses SQL 2008 convert mode 1 to convert binary string to text and PATH empty path concatenation method to derive full SEND command.

EXECUTE then simply executes the string! All tasks are SEND... away, no cursor!

 

IF @@ROWCOUNT = 0
    RETURN
SET NOCOUNT ON

 

BEGIN TRY
BEGIN

 

    DECLARE @SQL varchar(max) =
    (
     SELECT
      'SEND ON CONVERSATION '
      + QUOTENAME(actt.InitiatorConversationHandle,'''')
            + ' MESSAGE TYPE [Task] ('
            + CONVERT(nvarchar(max), CONVERT(varbinary(max), -- then this XML is converted into varbinary(max)
            (
                SELECT
                     act.acttID                                             as [@acttID]
                    ,q.actID                                                as [@actID]
                    ,a.actIDInstance                                        as [@actIDInstance]
                    ,act.actoID                                             as [@actoID]
                    ,q.FromsttID                                            as [@FromsttID]
                    ,q.TosttID                                              as [@TosttID]
                    ,p.SystemPrinter                                        as [@Printer]
                    ,q.CreatedOn                                            as [@CreatedOn]
                    ,q.CreatedUserName                                      as [@CreatedUserName]
                FOR XML PATH ('Task'), TYPE
            )), 1) -- then from varbinary back to
            + ');' + CHAR(10) as [text()]
        FROM INSERTED q -- must use INSERTED, otherwise deadlocking will happen
        -- some JOINs here
        FOR XML PATH ('')
    )

 

    EXECUTE (@SQL)

 

END

I hope you like it!

 

thanks

Vladimir

[Farmer]

Actual SEND statements generated will be like

SEND

ON CONVERSATION '9C5CC893-2255-DE11-B2F3-005056C00008' MESSAGE TYPE [Task] (0xFFFE3C0054....)

In my testing, to submit 20,000 messages using cursor takes 28 seconds, using new code, 14 seconds.

 

 

"Farmer" <som...@somewhere.com> wrote in message news:2DB48A10-A224-4AD5...@microsoft.com...

[Dan Guzman]

Thanks for sharing, Vladimir. Attention to performance is especially
important in triggers in order to maximize concurrency.

On a related note, consider voting for a bulk SEND feature
(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126149#details).
Inspired by your post, I was going to submit a suggestion for this feature
but found that Tom Moreau beat me to it.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

[Farmer]

Thanks. Voted on posed suggestion.

"Dan Guzman" <guzm...@nospam-online.sbcglobal.net> wrote in message
news:A0D07180-13DB-4A0D...@microsoft.com...

[Farmer]

Dan,
care to vote on my issue?

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=496271

Please do if you feel it's important.

thanks
vladimir

"Dan Guzman" <guzm...@nospam-online.sbcglobal.net> wrote in message
news:A0D07180-13DB-4A0D...@microsoft.com...