paulixml


HI

I am trying to set up a stored procedure to retrieve to 20 messages from a queue into a table to implement a batched process. I have the following code in a stored procedure.

WAITFOR (
RECEIVE top (20) -- get batched so that we can process same listid once
message_type_name,
message_body, -- the message contents
conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.target
into @PayloadData
), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

However, the stored procedure is only retrieving 1 message at a time from the queue. Did I miss some other setting

thanks

P




Re: receive top 20

Remus Rusanu


RECEIVE can only return messages on one conversation group. Normally each conversation is its own conversation group. If you sent only one message on each conversation, RECEIVE cannot get more that one message at a time, even if there are more messages in the queue.






Re: receive top 20

paulixml

Hi

so, in your blog on T-SQL RECEIVE. Fast. : Set based Processing.

How are you able to receive the message in bulk Is it because of the way you send the message in LoadQueueReceivePerfBlog

P





Re: receive top 20

Remus Rusanu

Yes. This is also the reason why I recommend reusing dialogs in my other entry at http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx





Re: receive top 20

paulixml

one follow up,

when I send the message using the same conversation handle, the receive top (20) statement waits until the previous batch is committed before it will start retrieving the next 20. I guess this is because of the its now part of the same conversation group and service broker need to guarantee process order

This is what my proc looks like

BEGIN TRANSACTION

WAITFOR (

RECEIVE top (20) -- get batched so that we can process same listid once

message_type_name,

message_body, -- the message contents

conversation_handle -- the identifier of the dialog this message was received on

FROM dbo.target

into @PayloadData

), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

-- do some processing of the records in @PayloadData

COMMIT TRANSACTION

if "-- do some processing of the records in @PayloadData" is taking a long time, its going to block the messages in the queue.

If I remove the begin and commit transaction block, it only wait for the 3 seconds I specified.

Question: is the transaction block necessary in the activated procedure.

thanks

Paul