SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Asynchronous Procesing with Service Broker


Asynchronous Procesing with Service Broker

Author
Message
gary.strange-sqlconsumer
gary.strange-sqlconsumer
SSChasing Mays
SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)

Group: General Forum Members
Points: 626 Visits: 640
Comments posted to this topic are about the item Asynchronous Procesing with Service Broker
richardd
richardd
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3314 Visits: 648
... a hardware shop keeper & customer ...


Am I the only one who was expecting a request for "four candles"? :-P



ningaraju.n
ningaraju.n
Right there with Babe
Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)

Group: General Forum Members
Points: 758 Visits: 425
Hi,

we have stored procedure which process the responses from student once the student submit the test,And this stored procedure consuming time and hence hindering the concurrency.
So thought of moving to asynchronous processing of this SP.I walkthrough the article but didnt get clear idea to implement our requirement.At runtime this SP accepts parameters.
Can you please do the favour in accomplish the task.

Thanks in Advance
gary.strange-sqlconsumer
gary.strange-sqlconsumer
SSChasing Mays
SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)

Group: General Forum Members
Points: 626 Visits: 640
Hi,

I'm glad my article inspired you to think of a service broker solution for your organisation. However this is only "lab session" code, so is not intended for production use. I encourage you to read up fully on the subject.

But in the meantime if you want to use the code sample I supplied, to play arround with some ideas of your own, then take a look at the "120 ManyConversations.sql" file. You will find code that loops through the system table names and uses them as parameters to the "uspInventoryCounts" stored procedure. Adapt this code to call your stored procedure and pass any parameters if required.
gary.strange-sqlconsumer
gary.strange-sqlconsumer
SSChasing Mays
SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)

Group: General Forum Members
Points: 626 Visits: 640
Hi,

I'm glad my article inspired you to think of a service broker solution for your organisation. However this is only "lab session" code, so is not intended for production use. I encourage you to read up fully on the subject.

But in the meantime if you want to use the code sample I supplied, to play arround with some ideas of your own, then take a look at the "120 ManyConversations.sql" file. You will find code that loops through the system table names and uses them as parameters to the "uspInventoryCounts" stored procedure. Adapt this code to call your stored procedure and pass any parameters if required.
ningaraju.n
ningaraju.n
Right there with Babe
Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)

Group: General Forum Members
Points: 758 Visits: 425
Hi,
I didn't found any scripts that you have mentioned.It will be helpful if you please elaborate taking our requirement
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224978 Visits: 19638
ningaraju.n (5/17/2011)
Hi,
I didn't found any scripts that you have mentioned.It will be helpful if you please elaborate taking our requirement


There is a download at the bottom of the article in the section titled "Resources". You can get the scripts there.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224978 Visits: 19638
Formatting has been corrected.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34957 Visits: 7660
Hey Gary, a nice lab and an excellent approach to simplifying the concepts of a messaging queue. The devil is in the details in SB, but it's still an excellent introduction to the topic if one hasn't worked with an asynchronous queue before.

Nice work, and thank you for the article.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34957 Visits: 7660
ningaraju.n (5/17/2011)
Hi,
we have stored procedure which process the responses from student once the student submit the test,And this stored procedure consuming time and hence hindering the concurrency.


I wouldn't usually start with asynchronous processing in this unless you had a hundred students trying to submit simultaneously, but I'd start with optimizing the query itself. What you might want to do is start a forum post in one of the optimization forums appropriate to the version of SQL server you're using. See the link in my signature on the best way to get help for index/tuning (second link down on the left).

So thought of moving to asynchronous processing of this SP.I walkthrough the article but didnt get clear idea to implement our requirement.At runtime this SP accepts parameters.
Can you please do the favour in accomplish the task.


You would need to provide a LOT more information to help you walkthrough your specific case. However, the short form of this: In regards to your parameters, what you'll end up doing is taking in the parameters as you are now, and turning that into an XML message. That message then gets sent to the 'Waiting Queue'. The 'Processing Queue' accepts messages from the 'waiting queue', turns the XML back into SQL variables, and runs your existing query. Once done, it returns an 'end dialog' message back to the 'waiting queue' to tell it to clear that message.

If you really want to get into a full SB solution for this, we should probably wander off to a dedicated thread, as it's going to be a bit of discussion and specifics to help you get where you need to be.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search