How to reduce flooding a Service Broker queue?

Question

I'm new to using the SQL Service 2005 Service Broker. I've created queues and successfully got conversations going, etc. However, I want to sort of "throttle" messages, and I'm not sure how to go about that.

Messages are sent by a stored proc which is called by a multi-user application. Say 20 users cause this proc to be called once each within a 30 second period of time, it only needs to be sent once. So I think I need some way from my proc to see if a message was sent within in the last 30 seconds? Is there a way to do that?

One idea I had was to send a message to a "response" queue that indicates if the request queue activation proc has been called. Then in my stored proc (called by user app) see if that particular message has been called recently. Problem is I don't want this to mess up the response queue. Can one peek at a queue (not receive) to see if a message exists in it?

Or is there a more simple way to accomplish what I'm after?

Answer 1

Not sure if you could do this in SB somehow, but could you just have a table with a timestamp field in it that was updated when a message is sent. The proc would check for a time diff of > 30sec and send.

Answer 2

Yes you can peek at a queue to see if a message is in it before hand. Simply query the queue using SELECT instead of RECEIVE and you can look at the data.

A better bet would be to send the messages and have the stored procedure which receives the messages decide if the message should be tossed out or not.

I send hundreds of thousands of messages to service broker at a time without any sort of performance issue.

If you are seeing performance issues then try sending more than one message per conversation as that is the quickest and easiest way to improve Service Broker performance.