**Overview**

I'm working on a web based solution that receives many millions of records a day (1000 at a time) via a web service from multiple clients simultaneously.

Each record needs to be stored in a database and use a complex set of rules and look-ups to work out how to classify each record, this is currently done via a stored procedure for ultimate speed (a record can be classified in about 4 milliseconds).

After classification the records needs to be moved to another database, and archived, this is the least important step and can be mainly left out of the question

**Main Priorities**

Priority 1 : Store the records, this is currently done via a bulk insert, and works well

Priority 2 : Classify each record, this is best done asynchronously, I.e this doesn't need to be done immediately (for each record) however it needs to happen in a timely manner and very efficiently

Priority 3 : Archiving, this is totally dependent on the clients requirements, it may need to be done immediately after classification, or in batch, and only needs to be an after thought at this stage

**Proposed Solutions**

My thinking has evolved since i first began this project

My first thought was to use a dedicated Windows Service to sit in the background and process batches of records, I.e check if there are records to classify, start a batch of N number of records, and repeat if necessary. However this solution requires a Windows Service (i'e another technology), and there may be a better approach

I've also been dabbling with the idea of triggers to process each record on update, however since I've never dealt with triggers, i'm am not sure whether it will slow down the bulk updates (step 1) how reliable they are or once again if there is a better approach

Lastly I've learnt of Service Broker, which seems to tick a lot of boxes in regards to asynchronous processing, reliability, and archiving / moving (last step), though i'm not sure if this over complicating the issue, if there are any performance sacrifices, and the extra boiler plating and black boxing is worth the effort

---

Please note: I would usually try every approach for myself, however the development time debugging, metrics analysis and learning curves with the approaches doesn't make it feasible, additionally it's outweighed by the time it might take to get some well rounded opinions from the Stack Overflow community.