1

First some background on the problem, we have a windows service (C Sharp) that receives new messages and processes them.It is database driven so it checks a table for unprocessed records, processes them and then updates them, this is all done within two stored procedures, one to get unprocessed messages and another to do the processing.

The service has been identified as a bottle neck in our system, if we receive higher volumes of unprocessed messages the service takes longer and longer to get through the back queue.

I have been asked to rewrite this service to take advantage of parallelism, the fact that it is single threaded is assumed to be the reason why it performs so poorly with large volumes, I however have some reservations about this assumption.

Outline of processing a message (done one at a time):

  1. Call getMessage sproc, store relevant data in memory
  2. Call processMessage sproc passing in previously retrieved data, message is processed and validated then marked as processed in source table

The fact that the service does 90% of processing in the DB layer tells me that all the issues to do with performance will be bound to the usage of the database. I do not see the benefit of making this service multithreaded and think efforts should be focused on either migrating the business logic from the sprocs to the service or at least optimising the sprocs so they can be called concurrently without causing table locks or other resource contention issues.

I would appreciate any professional input on this approach as the outcome of the performance of this service will be on me and I would like to lead with the best expectations.

3
  • 1
    You know the system best, so your analysis (that this won't help) seems very reasonable. Core issue is what the processMessage sproc does. Can it be invoked in parallel (from multiple connections) or will that fail due to locks within the DB? Are there CPU-intensive processing tasks that could be moved out of the sproc into application code, or would the necessary data transfer negate any benefits? Is single-core CPU utilization even the bottleneck in the DB, or are other resources such as disk throughput the constraint?
    – amon
    Sep 5, 2020 at 20:43
  • "the fact that it is single threaded is assumed to be the reason why it performs so poorly with large volumes" Stop assuming, start showing this is the case. Only then start worrying about how to multi-thread it. Sep 5, 2020 at 21:26
  • If you have no experience making use of all threads, read up on race conditions and make sure you have non-critical or use mutexes (locks). Locks of course are incredibly slow because it only allows a single thread at that time. Also, if you can simulate high usage on the server code, setup timing statements throughout. Provide yourself with a printed out table of timings of things you think you could work on. This table will tell you where to focus your time. Sep 6, 2020 at 15:17

1 Answer 1

0

Experience tells me that you are 100% correct in your guess. However, experience also tells me never to open my mouth about something until I've proved it.

Here it should be simple enough, add some metrics which record the time take to process a message and how much of that time is spent on each bit.

Ramp up the messages and see which bits start to take longer.

You could even go further, simply change the db calls to async and don't wait them to complete before moving on to the next message, essentially 'multi-threading'(ish) you app. However, if you are correct, this will grind the db to a halt so best run on a test system.

Once the blame is clearly on the sprocs you can dig in to them to work out why they are slow, or just move the logic out.

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.