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.
Triggers are synchronous, and will slow bulk inserts. Service Broker is a good technology for async processing in T-SQL. There is a bit of a learning curve, though, and it is important to follow best practice patterns (e.g. don't "fire and forget"). Make sure you have the unhappy paths covered since processing occurs in the background.