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.