Message Retieval

Hello again,

A.
This time I would like to get your input on two ways of getting a message off a queue. Both are samples, but I would like to know more about the implications.
This first example (BOL) goes directly to the queue:

WAITFOR (

RECEIVE TOP(1)

@.messageTypeName = message_type_name,

@.messageBody = message_body,

@.conversationHandle = conversation_handle

FROM ExpenseQueue

), TIMEOUT 500 ;
The second one (SQL Server samples) obtains a conversation hanle, then gets a message:
WAITFOR(
GET CONVERSATION GROUP @.conversation_group_id FROM [dbo].[ProcessBCHQueue]),
TIMEOUT 500 ;

RECEIVE
TOP(1)
@.conversation_handle = conversation_handle,
@.message_type_name = message_type_name,
@.message_body =
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'<none/>' AS XML)
END
FROM [dbo].[ProcessBCHQueue]
WHERE conversation_group_id = @.conversation_group_id ;

what are the implications between both calls?
I would think that:
1. In any case messages are initially send via a dialogue, so I am not sure why the first example eliminates the conversation handle;
2. Does the second example allow for more readers getting messages on different conversations, since unique conversation handles are explictly obtained?
3. Since the first method was in the Handling Poison Messages of BOL, is that a more straight forward approach that allows for easier handling of error messages?
4. Anything else that might be interesting to note? Please share.

Thanks again,

Lubomir

The second example shows how to process messages when you need to retrieve a state from your database associated with the incomming messages. The recomended way is to store this state in a table and use the conversation_group as the table key. The second example allows you to first lock an available conversation group (one that has messages to be received), then look up the state associated with this particular group, then receive the messages of this group.

The different approach is only a performance optimization. The first example would require you to look up this state for each message received. The second example allows you to look up the state once, then process all messages for that particular conversation group in a loop, w/o looking up the state again.

The typical example of processing in the second case is:

BEGIN TRANSACTION
WAITFOR (GET CONVERSATION GROUP...), TIMEOUT ...
WHILE (@.conversation_group IS NOT NULL)
BEGIN
-- lookup state in the state table here, using the @.conversation_group key
--
RECEIVE TOP(1) ... WHERE conversation_group = @.conversation_group
WHILE (@.conversation_handle IS NOT NULL)
BEGIN
-- proces message here
--
RECEIVE TOP(1) ... WHERE conversation_group = @.conversation_group
END
COMMIT;
BEGIN TRANSACTION;
WAITFOR (GET CONVERSATION GROUP...), TIMEOUT ...
END
COMMIT

The transaction boundaries are important in this example. One shouldn't commit each individual message as it processes the inner loop, as a commit will actually release the lock on the conversation_group and another reader might alter the looked up state.

There is no difference between the two approaches on how many readers can progress in paralel. Both approaches will lock the conversations in the same way, the difference is only in how the procedure would handle an application specific state lookup.

HTH,
~ Remus

|||Thank you very much Remus,

Lubomir