Asynchronous Procesing with Service Broker

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1782

    Comments posted to this topic are about the item Asynchronous Procesing with Service Broker

  • richardd

    Hall of Fame

    Points: 3899

    ... a hardware shop keeper & customer ...

    Am I the only one who was expecting a request for "four candles"? 😛

  • ningaraju.n

    SSCrazy

    Points: 2322

    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

    SSCommitted

    Points: 1782

    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

    SSCommitted

    Points: 1782

    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

    SSCrazy

    Points: 2322

    Hi,

    I didn't found any scripts that you have mentioned.It will be helpful if you please elaborate taking our requirement

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    Formatting has been corrected.

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • BobA 66314

    Valued Member

    Points: 67

    Very fine article.

    Yet running the code, I never get entries in ExecutionLog, never receive @ReceivedReplyMessageType = N'//HardwareStore/ReplyMessage'.

    Also, in File 070 StaffManager.sql (SP StaffManager)

    -- Instruct worker to execute the request.

    EXEC Worker @ReceivedRequestMessage, @ReplyMessage OUTPUT

    then immediately overwrite the output variable:

    SET @ReplyMessage = (SELECT @ReceivedRequestMessage, @ReplyMessage FOR XML PATH('ExecutionComplete'));

    How can this be correct, since SP Worker carefully loads that output variable with:

    -- Convert the result recordset to xml and pass it back to the caller (OUTPUT var).

    SET @ReturnMessage = (SELECT ItemVolume FROM @InventoryCount FOR XML PATH('WorkerReport'))

    Please clarify.

    And thanks. We need Async operations, and want to use SB.

    Bob A

  • ningaraju.n

    SSCrazy

    Points: 2322

    Hi Craig Farrell,

    We have done all the indexing and query optimization at the best,So thought of asynchronous processing.Could you please help on this?

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1782

    Very good spot BobA,

    It would seem that I declared a separate variable intended to be used when sending a reply @ReturnMessage. However it never gets used and instead I reuse the @ReplyMessage variable.

    -- Send reply with results.

    SET @ReplyMessage = (SELECT @ReceivedRequestMessage, @ReplyMessage FOR XML PATH('ExecutionComplete'));

    SEND ON CONVERSATION @ReceivedRequestDlgHandle

    MESSAGE TYPE [//HardwareStore/ReplyMessage] ( @ReplyMessage );

    By luck more than anything the code does what was intended.

    Regarding your empty ExecutionLog table, do you also have an empty ExecutionErrors table? Have you checked the SQL Server logs like the article suggests?

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1782

    Very good spot BobA,

    It would seem that I declared a separate variable intended to be used when sending a reply @ReturnMessage. However it never gets used and instead I reuse the @ReplyMessage variable.

    -- Send reply with results.

    SET @ReplyMessage = (SELECT @ReceivedRequestMessage, @ReplyMessage FOR XML PATH('ExecutionComplete'));

    SEND ON CONVERSATION @ReceivedRequestDlgHandle

    MESSAGE TYPE [//HardwareStore/ReplyMessage] ( @ReplyMessage );

    By luck more than anything the code does what was intended.

    Regarding your empty ExecutionLog table, do you also have an empty ExecutionErrors table? Have you checked the SQL Server logs like the article suggests?

  • BobA 66314

    Valued Member

    Points: 67

    Thanks, Gary.

    Indeed, I rebuilt from scratch, and it is working fine. Two final questions:

    A) You ACTIVATE the CustomerRequestList queue (file: 080 EmployStaffManager.sql)

    Why is there no need to activate the CustomersResponseToAskList queue? Is it because it is not carrying the message type that is SENT BY INITIATOR?

    B) Also, you recommend Service Broker for using heavy user traffic. What do you recommend for 5 users initiating database actions that take 10 to 90 seconds to process? That's an important scenario for us; our code is efficient, but the volume of data cannot move faster.

    Thanks again. 🙂

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply