locked
Using Cursors within an Activation SP versus not using it. RRS feed

  • Question

  •  

    I see activation stored procedure using the following codes..

     

    DECLARE @tableMessages TABLE (
     queuing_order BIGINT,
     conversation_handle UNIQUEIDENTIFIER,
     message_type_name SYSNAME,
     message_body nvarchar(max) );

     

    DECLARE cursorMessages CURSOR FORWARD_ONLY READ_ONLY
    FOR SELECT conversation_handle, message_type_name, message_body
    FROM @tableMessages
    ORDER BY queuing_order

     

    WHILE (1=1)
    BEGIN
     BEGIN TRANSACTION

     ;WAITFOR (RECEIVE TOP(100)
      queuing_order,
      conversation_handle,
      message_type_name,
      cast(message_body as nvarchar(max))
     FROM dbo.QueueOrder
     INTO @TableMessages), Timeout 500

     

    as well as

     

    WHILE (1=1)
    BEGIN
     BEGIN TRANSACTION

     ;WAITFOR (RECEIVE TOP(100)
      queuing_order,
      conversation_handle,
      message_type_name,
      cast(message_body as nvarchar(max))
     FROM dbo.QueueOrder
     INTO @TableMessages), Timeout 500

     

    I'm trying to understand the pros and cons of using one method versus the other.  Is one preferred over the other?  How about when it concurrent processing (setting the MAX_READER = 5)?  How about high volume queues?

    Wednesday, December 10, 2008 12:12 AM

All replies

  • Am I tired or the two code samples are identical?

    Anyway, using CURSORs over @tableVariable in activated stored procedures is a perfectly valid pattern. It beats using RECEIVE TOP (1) @variables=fields by a large margin in performance terms and is the next best thing if you cannot do the processing in a set oriented fashion (and ususally you can't, because of the handling of EndDialog and Error messages if not for else). I have talked in more detail about this in my blog at http://rusanu.com/2006/10/16/writing-service-broker-procedures/ . About your questions:
    1) MAX_QUEUE_READERS=5: RECEIVE is designed specifically to perform in a parallel fashion, ie. multiple RECEIVE will not block each other. Conversation Group Locks are the means to achieve this independence of queue readers.
    2) High Volume Queues: A queue with a high number of messages pending should be the exception case, not the rule, as the readers should keep up with the incoming term, at least over an average. But even if the queue grows (spikes) RECEIVE should perform good. The indexing of the queue is designed to help queue readers deal with large volumes. Performance degradation, if present, is usually caused by the usual suspects related to high volume data: buffer pool eviction, poor cache hit ration, high I/O waits, log writing bottlenecs.

    In adition you need to consider that a RECEIVE returns messages for only one conversation group at a time and is a fairly expensive statement. If your queue has a high number of messages all on different conversations then the TOP 1000 helps little as each iteration can only retrieve only 1 message, and performance is rather poor (again this is covered in my blog article).

    And some final practical considerations (from my experience): @tableVariables are not transacted so your procedure has to be careful around rollbacks because the RECEIVED messages placed in the @tableVariables are NOT rolled back and may still be processed. Also you have to consider what happens if your WHILE loop loops for a VERY long time (incomming message rate never lets activation go away): your @tableVariable may grow to many-many of messages, so make sure you truncate it between transaction commits. Also make sure you commit only at RECEIVE boundaries, ie. if you receive a 100 messages batch you must process the whole 100 before commit, you can't commit in the middle of processing (ie. after 50) because your first commit will commit the whole RECEIVE. And make sure you process the @tableVariable in the same order as returned by RECEIVE (ie. never delete the ORDER BY in the cursor declaration).
    Wednesday, December 10, 2008 6:02 AM