Looking for example design of using an SQL database as a queue for throttling web service requests of multiple users

Posted on

Question :

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…

Answer :

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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *