Queuing using the Database or MSMQ?

Question

A part of the application I'm working on is an swf that shows a test with some 80 questions. Each question is saved in SQL Server through WebORB and ASP.NET.

If a candidate finishes the test, the session needs to be validated. The problem is that sometimes 350 candidates finish their test at the same moment, and the CPU on the web server and SQL Server explodes (350 validations concurrently).

Now, how should I implement queuing here? In the database, there's a table that has a record for each session. One column holds the status. 1 is finished, 2 is validated.

I could implement queuing in two ways (as I see it, maybe you have other propositions):

• A process that checks the table for records with status 1. If it finds one, it validates the session. So, sessions are validated one after one.
• If a candidate finishes its session, a message is sent to a MSMQ queue. Another process listens to the queue and validates sessions one after one.

Now:

• What would be the best approach?
• Where do you start the process that will validate sessions? In your global.asax (application_start)? As a windows service? As an exe on the root of the website that is started in application_start?

To me, using the table and looking for records with status 1 seems the easiest way.

Jeanelle

Answer 1

The MSMQ approach decouples your web-facing application from the validation logic service and the database.

This brings many advantages, a few of which:

• It would be easier to handle situations where the validation logic can handle 5 sessions per second, and it receives 300 all at once. Otherwise you would have to handle copmlicated timeouts, re-attempts, etc.

• It would be easier to do maintanance on the validation service, without having to interrupt the rest of the application. When the validation service is brought down, messages would queue up in MSMQ, and would get processed again as soon as it is brought up.

• The same as above applies for database maintanance.

Cytology

Answer 2

If you don't have experience using MSMQ and no infrastructrure set up, I would advice against it. Sure, it might be the "proper" way of doing queueing on the Microsoft platform, but it is not very straight-forward and has quite a learning curve.

The same goes for creating a Windows Service; don't do it unless you are familiar with it. For simple cases such as this I would argue that the pain is greater than the rewards.

The simplest solution would probably be to use the table and run the process on a background thread that you start up in global.asax. You probably also want to create an admin page that can report some status information about the process (number of pending jobs etc) and maybe a button to restart the process if it for some reason fails.

Commencement

Answer 3

What is validating? Before working on your queuing strategy, I would try to make validating as fast as possible, including making it set based if it isn't already so.

Wolfenbarger

I've googled set based, but I'm not quite sure what you mean by it. I've made validating as fast as possible (now at 500ms per session) but still, if 350 candidates validate at the same time, it makes 175 seconds. Although, it is loadbalanced, so in fact it's 350/4 sec. It works but cpu of webserver goes near 100% for some minutes. – Jeanelle

using "set based" operations in regards to database programming is when you issue a single database command, like UPDATE and it affects all of your rows, as opposed to looping over each row and doing a separate UPDATE on each. I did a google on "database set based processing" and here are two articles from near the top: codeproject.com/KB/database/SetAndProceduralSQL.aspx and weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx – Wolfenbarger

Answer 4

I have recently been investigating this myself so wanted to mention my findings. The location of the Database in comparison to your application is a big factor on deciding which option is faster.

I tested inserting the time it took to insert 100 database entries versus logging the exact same data into a local MSMQ message. I then took the average of the results of performing this test several times.

What I found was that when the database is on the local network, inserting a row was up to 4 times faster than logging to an MSMQ.

When the database was being accessed over a decent internet connection, inserting a row into the database was up to 6 times slower than logging to an MSMQ.

So:

Local database - DB is faster, otherwise MSMQ is.

Wilhelmstrasse

I vouch for this opinion, I felt the same. I tried to insert 100 records into the database that is in my network in my own building and then I created Private MSMQ in my laptop and did the same. The MSMQ took more than 1.5 times more time than database. I guess MSMQ is good when you have number of services wanting to write the data at the same place. – Towhaired

i stumbled upon this quote: "SSB will be significantly faster than MSMQ in transacted mode. MSMQ will be faster if let operate in untransacted mode (best effort, unordered, delivery)". So... is it possible to specify if your tests were executed with a transacted queue or an untransacted one? – Jurisprudent