May 17, 2011 at 12:04 am
Comments posted to this topic are about the item Asynchronous Procesing with Service Broker
May 17, 2011 at 5:48 am
... a hardware shop keeper & customer ...
Am I the only one who was expecting a request for "four candles"? 😛
May 17, 2011 at 6:05 am
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
May 17, 2011 at 6:40 am
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.
May 17, 2011 at 6:40 am
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.
May 17, 2011 at 6:54 am
Hi,
I didn't found any scripts that you have mentioned.It will be helpful if you please elaborate taking our requirement
May 17, 2011 at 6:58 am
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.
May 17, 2011 at 7:00 am
Formatting has been corrected.
May 17, 2011 at 3:43 pm
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.
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
May 17, 2011 at 3:49 pm
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.
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
May 17, 2011 at 5:47 pm
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
May 17, 2011 at 10:45 pm
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?
May 18, 2011 at 1:38 am
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?
May 18, 2011 at 1:38 am
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?
May 18, 2011 at 10:25 am
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 22 total)
You must be logged in to reply to this topic. Login to reply