RECEIVE vs RECEIVE TOP(1)

In working through some examples, sometimes I will see this pattern for receiving messages: What is the purpose of the "nested" WAITFOR (RECEIVE? What is this actually doing? Is it receiving the same message in both RECEIVE?

WAITFOR (

RECEIVE @.dh = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = CAST([message_body] AS NVARCHAR(4000))

FROM [Queue]), TIMEOUT 1000;

WHILE @.dh IS NOT NULL

BEGIN

IF @.message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'

BEGIN

RAISERROR (N'Received error %s from service [Target]', 10, 1, @.message_body) WITH LOG;

END

END CONVERSATION @.dh;

COMMIT;

SELECT @.dh = NULL;

BEGIN TRANSACTION;

WAITFOR (

RECEIVE @.dh = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = CAST([message_body] AS NVARCHAR(4000))

FROM [Queue]), TIMEOUT 1000;

END

COMMIT;

Other times I will see this pattern for receiving messages: Why do a RECEIVE TOP(1) instead of just a RECEIVE?

WAITFOR(RECEIVE TOP(1)

@.conversationHandle = conversation_handle,

@.messageTypeName = message_type_name,

@.messageBody = message_body

FROM [Queue]), TIMEOUT 1000;

And other times I will see this pattern for receiving messages: What is the purpose of RECEIVING into an in-memory table when you can just process the message directly?

WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [Queue]

INTO @.tableMessages), TIMEOUT 1000;

IF (@.@.ROWCOUNT = 0)

BEGIN

COMMIT;

BREAK;

END

What is the difference between the three approaches from an architectural and performance perspective? I need to process messages as fast as possible and I'm not sure why or when each should be used. Also, does the timeout have any impact on how FAST messages will be processed, or is it exactly what it says - a timeout - if a message is not found within the period then the procedure will break?

The samples all use RECEIVE TOP(1) because it's much easier code to write and explain. Receiving all the messages available in a conversation group is much more efficient but it makes for more complex code that's harder to understand so I don't usually write samples that way. Remus has some great tips for writing efficient services here: http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx|||

Well, I've reviewed that blog from Remus and actually ran the exact benchmarks and its true that I get pretty good performance. However, let's say that you have thousands of messages coming in per second and each message needs to be processed with some logic - what is the best way to do that using service broker. It seems counter-productive to load them into a temporary table as the example illustrates (but the example is only trying to show the capacity to clear the queue). Also, simply loading a conversation group could be unpredictable if you are recycling dialogs (every message would be a part of the same or a few dialogs). I see alot of benefit to the technology and I'm trying to use it in a very high transaction environment, but some of the advice seems to go against other recommendations and/or the examples are limited to single message processing. How about a boilerplate example of what one should strive for in the activated stored procedure when expecting the kind of load I mentioned above (thousands/sec).

|||

Well, if you trust your judgement more than the espertise of the guys who wrote Service Broker, I suupose you're free to make those judgements. The best way to scale to thousands of messages is going to depend on how big the messages are, how the dialogs are structured, etc. You may find that receiving a bunch of messages into a recordset in a .Net application running on a seperate server scales best for you if there's a significant amount of comutationally intensive processing for each message. A receive top(1) works just like a select top(1) - the whole query is executed and then after the first result is retreived the query is cancelled. If there are a lot of messages in a conversation group, this can be pretty inefficient but if each conversation group has only one message, it doesn't really matter whether you do a top(1) or not. Here are some scaleability thoughts I posted on another thread last week:

Beginning and ending a conversation takes longer than sending a message so starting up a new dialog for every message is pretty inefficient. Some thoughts here: http://blogs.msdn.com/rogerwolterblog/archive/2006/05/20/602938.aspx

Receiving one message at a time and commiting after each message is not a good way to scale an SSB application. Processing multiple messages per transaction makes error handling complex and receiving many messages per Receive statement is harder to do but if performance is a major issue, you need to do it. http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx

If you have to do a lot of processing per message - complex XML manipulation, serializing and deserial,izing, etc. A stored procedure probably isn't a good option. Moving the messaging processing off the database server can help scaleability.

Here's a discussion of some of the things I've learned about SSB applications: http://msdn2.microsoft.com/en-us/library/aa964144.aspx

The key to understanding total throughput is understanding what it means to process a message. I had one customer complain that he could only process 300 messages a second but when I asked for his code I found out processing a message involved doing 40 or 50 inserts into the database so his 300 messages a second translated to 15,000 inserts a second. It's not unusuall to see Service Broker process a couple thousand messages a second but not on a server that's also doing a thousand order-entry transactions a second.

|||

Well, of course I trust the expertise of the guys who wrote Service Broker. My message size is configurable because they are essentially batches of data - so I can configure them based on the performance of the processing procedures. So I guess my question still remains...how best to process multiple messages using RECEIVE. Since it works just like SELECT, do I have to create some sort of cursor or does service broker offer a better pattern or syntax for processing each message in a RECEIVE without having to create a table to hold the data? Can I do some sort of join or apply or something? A simple code sample illustrating would be great. I have read those articles and links many times, but they don't quite answer the question.

|||Have a look at my blog at http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx, it covers many of the questions you ask.sql