5

I'm designing a system for multiple users to queue their requests to a web service which takes requests such as creating new entries, changing entries, requesting data. Many of the web service calls we will be using take hierarchical data as parameters (heirarchies designed to match c# object structures) while others such as a request for the list of allowable entry types, require no parameters. The queue is necessary because the web service has a 5 second or so throttling requirement, and only one web service login will be used by multiple users.

xml Data returned from the web service will eventually come back, be parsed by our own server app, and inserted to a response table of some sort so the user app can know the request was received and also get any data that are returned.

basic picture is

user app (multiple users) --> service request queue <--> server app <-->web service

and responses coming back similarly

user app (multiple users) <-- service response queue <--> server app <-->web service

enter image description here

I'm creating the user app and server app myself, and just trying to get a handle on how to design the service request queue and response queues which will be using sql server and trying to figure out just how many tables these queues will have since requests and responses come in hierarchical form (different hierarchy types are returned as well depending on the request), non hierarchical form both.

Objects like these are specified by the external web service API. Users are sending different types of requests in different related forms of this object structure, which is going to be stored in the queue system/queues, then picked up by the server app for dequeuing to meet a throttle requirement for the web service. The server app might also cancel irrelevant requests, such as if too much time has elapsed and it suspects a faulty or non-relevant request, or such as if the user app is not synchronized with the data stream from the web service. enter image description here

Anyone have any simple example multiple user sql server queues used for this purpose? Is there any good book on this topic? or web tutorial perhaps? seems necessary for throttling multiple users of any web service...

Improve this question
7
  • we have to design to the specifications of this financial API. their api manual is published here: stage.cscreen.com/downloads/Cscreen_API_v15.zip
    – fa1c0n3r
    Commented May 3, 2012 at 23:46
  • 1
    With hierarchical requests you are opening the door for correlated messages in the queue (ie. message 15 cannot be precessed because it depends on the result of message 13). Correlation is very tricky to resolve (well, at least is tricky when you have concurrency). SSB addresses this explicitly with Conversation Group Locking. Trying to solve this yourself will be hard, to say the least. Can you represent each hierarchical tree with one and only one message, so that there are never 2 correlated messages in the queue?
    – Remus Rusanu
    Commented May 4, 2012 at 0:21
  • Provided a simplified diagram on my original question, instead of the specific API. Yes there is a timing issue there definitely if we try to send them in regular FIFO. The server app will have to remove correlated messages after dequeuing a batch, (change request on the same system ID) and return an error message on the response queue to the user app while processing the first request in for that particular product in the system (a kind of FIFO / and delete-all-outdated dequeuing). Thanks for the suggestion though, let me check out Conversation Group Locking...
    – fa1c0n3r
    Commented May 4, 2012 at 0:28
  • 1
    If you rely on the Server App to do the correlation logic you lock yourself into having only one instance of it. Specifically, you won't be able to scale out the processing. Think what happens when multiple application instances dequeue messages and app instance 1 fetches a bunch of messages, then another instance, on a another machine, fetches another bunch. What do you do now, if some messages in the second bunch depend on messages in the first bunch and vice-versa?
    – Remus Rusanu
    Commented May 4, 2012 at 0:45
  • Yes my original solution could only work if there were only one dequeuing process. You are right, SSB conversation locking is likely a good way to do it for scalability. Let me think about how the queues are going to look like. I guess rather than relational queue databases with the object structures, I might directly use the XML data type in the message queue. I have heard many a comment against using the XML data type in a table though. For SSB would you recommend XML data type queues over making a set of relational tables around the object structs? (considering performance loss if any)?
    – fa1c0n3r
    Commented May 4, 2012 at 1:05

2 Answers 2

Reset to default
10

I did exactly this as a project back in 2008-2009, a web service (billing) that needed to handle 1M+ calls per day. I used SQL Server table as a queue, and the lesson from that project I distilled into the article Using Tables as Queues. Stick to the rules I lay out there, and specially don't try to add any whistles and bells to your table, use it exclusively as a queue. Under load I found that a critical issue was to batch dequeue (dequeue 100 web service call request in one DB operation) and handle the dispatch in the app (place the dequeue request into an in memory list, have the web call handlers pick up work from this list). Doing the web service call async is critical. Also, you must read about the ServicePointManager.DefaultConnectionLimit.

Improve this answer
2
  • thanks! I read your article and the one on big queues they are quite useful. I would like to have seen an example database schema or two for dealing with queuing different types of web service calls that take different hierarchical input parameters (even if grossly simplified) to help me make some design decisions. Your suggestion of dequeuing batches is going to come in handy to improve speed and avoid uncontrollably growing that queue.
    – fa1c0n3r
    Commented May 3, 2012 at 10:54
  • 1
    a link to Rusanu's dealing with big queue article: rusanu.com/2010/03/09/dealing-with-large-queues
    – fa1c0n3r
    Commented May 3, 2012 at 11:06
2

Have you considered using SQL Servers built-in "Service Broker" Feature? It is designed to help developers implement asynchronous, queued systems.

Improve this answer
3
  • I haven't used that before, but there is a section in Rusanu's article rusanu.com/2010/03/26/using-tables-as-queues which mentions why not to use Service Broker... I don't know I might take his advice... Also... my messages are object oriented in nature... They are web service requests that are going to an external web service (I added a diagram of the basic structure I am thinking to make it clearer). If I used Service Broker as a solution for managing the queues, would I have to encode the messages as XML data types?
    – fa1c0n3r
    Commented May 3, 2012 at 15:52
  • After reading that post, I might describe SB as a way to enqueue and dequeue a message. The message is just XML, the contents of which are entirely up to you. To enqueue, pack up the parameters to the web call as XML and 'send' it as your message. Later, something else receives the message, unpacks the XML and does something with it (ie. makes the web call). Nearly all of the queueing schemes I've seen put into production had blocking and/or deadlocking issues, but it seems that Rusanu has worked through all of that. Haven't seen the ghost problem he did, but I havent' seen that kind of load.
    – Darin Strait
    Commented May 4, 2012 at 17:13
  • (I'm wordy.) Devs do seem to run into trouble implementing all of the "extra" stuff to begin/end conversations/messages, etc. and it seems easy to leak handles. I'd say that if rusanu has worked something out with tables, if it seems to handle your scaling requirements and if you can borrow his code/ideas, you should go with the familiar.
    – Darin Strait
    Commented May 4, 2012 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.