The application is on .Net 3.5 and Oracle Standard Edition 11G database where a table have over 2 million records. The record size is not huge. The records have a Status column of type Char(1). Depending upon the status a process picks records to process and updates the status after completion. A record goes via multiple processes in serial order, i.e, A -> B -> C..... The processes are suppose to run on separate machines as windows service a
Simplest solution is all the processes runs every 10 minutes and queries the table for the records with the respective status. There are multiple processes around 15 running parallel to check for a particular status. This may be not an efficient solution. A bit of time lag between the processes in not an issue.
Another option I was considering is to define a queue for each process. Once a process finishes it adds the record to next queue. I can use Oracle Queues here.
Which option do you think is better for this scenario? The solution should not affect the performance of the database as a website is also running on the database and accessing the same table as well. Is there any other better approach? Does .Net Workflow framework fits here to provide development ease?