Activated stores procedure and Batch Processing

Hi There

2 Questions :

1. Almost in every SB example you will see this sql :

BEGIN TRANSACTION

WAITFOR (

RECEIVE TOP (1)

@.MessageType = message_type_name,

@.Message = message_body

FROM [Queue1]

WHERE conversation_handle = @.ConversationHandle

), timeout 5000;

If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?

2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?

Thanx

Hi Dietz,

1. The purpose of the WAITFOR is to have the procedure linger a few seconds when it empties the queue, in hope a new message comes in and gets processed. W/o this technique, the procedure might be deactivated just to be activated immedeatly by a new message.

2. To process a batch of messages, the best approach is indeed to RECEIVE into a @.table variable and process a cursor over the @.table variable. A cursor over a @.table variable is quite efficient, specially if the cursor is kept open between RECEIVEs.
One thing to keep in mind is the particular message pattern expected by the service. Remeber that RECEIVE only returns messages for one particular conversation_group at a time, which in most cases equates one conversation. If the typical message pattern is request-reply, then the RECEIVE will mostly return only 1 message at a time anyway, so using the TOP(1) might be simpler and more efficient.
My recommendation is to actually test and measure the performance, because it depends a lot with the message flow pattern.

HTH,
~ Remus

|||

Hi Remus

Initially it seems our app will only send individual messages and the target simpy receive and end conversation, and not have long conversatons or conversation groups spanning many dailogs, so in that case would you agree a top(1) should work fine ? So processing batches is only more efficient if you have large conversation groups , which is not true in our case where it is just a single conversation.

Thanx