Is decoupling web and database tiers via MSMQ necessary or overkill?

Question

I'm putting together a simple asp.net web control, that as the result of an ajax form post inserts a record into a MSQL database.

It's possible that the page containing this control will recieve many thousands of hits in a small space of time and I'm worried about the performance issue of opening a database connection, inserting the record and then closing the connection for each request.

A solution that has occured to me is to make the web control place a message into an MSMQ queue and have a Windows Service on the server periodically read the queue and do a batch insert.

Does that sound like a sensible architecture given that the web and database servers are running on the same machine. Does it sound necessary?

From what I've read on the database most of the benefits of using MSMQ are to do with resilience rather than performance, so I might be barking up the wrong tree.

Any advice would be very gratefully appreciated

Many thanks

Pete

Answer 1

Processing requests offline in this way is a common pattern for when you are dealing with a high volume of requests.

Whether you should or even can do this is based on a number of factors.

The main factor is: do your callers need to see the response to their requests synchronously?

What I mean is does the caller have to know, immediately and with absolute certainty, if the database successfully committed their data as part of the call response? If so, then taking request processing offline is not really an option.

However, if it is acceptable that callers can be sent a response saying that their request will be processed offline (and any required response also sent offline) then using a queue will definitely benefit your overall architecture.

The first thing that will be benefited is front-end availability issues.

If you are processing thousands of requests over a short period, and each request is serviced by a thread which then goes away and inserts data into a database, very likely you will run into the problem where no available dispatcher thread exists to service incoming requests.

However, by reducing the amount of back-end work IIS is doing (writing to a local queue is really cheap relative to a database call) you are also reducing the likelihood of availability-based failure.

Secondly by using a queue you will have a means of throttling back-end traffic, because you have control over the throughput on the back-end processing.

For example, you could have a a single-threaded queue reader process which de-queues a request and processes it into a database. If you find you have messages building up in the queue you can just scale out the queue reader service by hosting more instances of it.

What this means is that you are reducing the likelihood of getting database contention issues because you have more control over the number of accessing threads on the database at any one time.

So, by using queuing you suffer fewer failures and have lower management overhead, which are the hallmarks of well written applications. This especially when you are considering hosting your database on your web server!

Also, you should have a read up of an architectural pattern called CQRS, one of the central principals of which is to do your database writes offline.