I have a situation where clients are posting query requests to an endpoint (a c# controller method), and the controller takes the data they post and figures out what query they want, then calls a stored procedure to retrieve the data, then sends the results back to them.

The problem is that these queries come in pretty much all at once every 10 min or so, so we will get several hundred in a second, then nothing for another 10 min, then it repeats. Some of these are pretty intensive so when they all come in, the system can slow a bit on other tasks.

The queries are not that time sensitive, so the caller can wait a few seconds or more without impact, so what I would like to do is take all the incoming queries and log them to a table or something then take them one at a time and execute them then return the results.

One thing I thought of was to make a function like async Task<string> DoQuery then await it in the controller, then in the DoQuery function, I could read the 'queue' table and start processing them one at a time but I can't think of a good way to keep the results straight so that I return the correct results to the correct caller.

I am probably over thinking it but would appreciate any input.

  • standard code already does the waiting bit. it sounds like really you want to run the queries one at a time? – Ewan Sep 6 at 13:35
  • Why not fuzz the "every 10 minutes" with some small random offset so that the queries are spread over a minute or two rather than all at once? – Telastyn Sep 6 at 17:46
  • @Telastyn, we don't have control over the calling code, it is done by clients – user197505 Sep 6 at 18:37

You don't want to keep the connection open while clients wait. So to do the async processing you really need to be able to post back to the client, or at least enable the client to poll the status of their job.

Once you separate out the request and reply messages you can solve the queuing with any MQ solution (ie rabbit mq, MSMQ, ZeroMQ)

However. There are a couple of red flags in your post which suggest to me that this isnt the solution to your problem

  1. "we will get several hundred in a second"

    That's a high volume, but not particularly unusual

  2. "Some of these are pretty intensive so when they all come in, the system can slow a bit on other tasks."

    One task affecting the performance of others of course happens, but is usually mitigated by splitting the tasks across multiple computers. What is the contented resource?

  3. Executing one at a time might lower resource contention, but you wouldn't normally expect an increase in performance. If it takes more than 10min to run through all the tasks you just have the same issue again.

If I were to take a guess I would say that your are probably running all the queries on the same DB and have locking or memory issues. Sharding the database, adding read only copies, moving logic out of the DB and into the application layer are perhaps the real solution to your problem.

  • 1
    Yes, I am running them on the same database, and you are right in your above comment that I want to run them all at the same time. As for splitting them across multiple computers, that wouldn't work because we only have one database server, so it would be the one doing all the work even if we offloaded the 'call' to other computers. Splitting the database would be a real mess, we have a lot of interconnected things going on and would end up referencing a master for most things. If we could just run the queries sequentially that seems like it would solve the issue. – user197505 Sep 6 at 18:33

I assume that @Ewan is right, and that we have here a typical XY-problem, where you are asking about your solution X when your real problem is Y.

In general, your problem is not queueing, but serving the results in a timely manner without slowing down your machine.

There are several solutions:

  • physical like throwing money and get you a bigger server / more servers
  • logical like organizing your data better

One possible solution could be simple, preparing a cache for the requests with the results needed, so the retrieval would be independent from serving the actual results. If the requests come, the results are ready to be served.

Another solution would be to give the user the response back, that there is the creation process for a ressource ongoing. The result HTTP 202 indicates, that the request to start this creation was accepted. The response has a link to a (future) location of the result. From that, it is up to the backend, how it deals with the retrieval.

That said, it would be relatively easy to do, what you intended

I could read the 'queue' table and start processing them one at a time but I can't think of a good way to keep the results straight so that I return the correct results to the correct caller.

Here you do your $JOBand give the client HTTP 202 with a UUID for the job. And the result would be available under /jobs/d50d5b7e-72ea-408b-8d92-ba46c22104a4. In case, the process is not ready a 404 NOT FOUND would indicate, that the resource is not available (yet). In case of success, the result is waiting. In case of an error, some kind of error could be consumed.

But to give better advice, it would be necessary to know your problem / system better.

  • I like the 202 idea, but that would require our clients to rework their code which is a non-starter right now :( – user197505 Sep 6 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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