Process a Repeatable Subset of Records in a SQL Server 2000 Table

Question

I have a script that processes queued records in a SQL table in SQL Server 2000. I now need to add additional instances of my script to process their own subset of the queued records in the table.

How can I query the table within each instance so that each instance will return a subset of rows that never overlap with each other?

I could query the id row for odd numbers in one process and even numbers in another, but I'll need to add more than 2 instances eventually.

CREATE TABLE requests (
   id int IDENTITY(1,1) NOT NULL,
   requestor VARCHAR(50),
   status INT,
   created DATETIME,
   queuetime DATETIME
)

The existing query for the single instance is:

SELECT * FROM requests WHERE status = 1 ORDER BY queuetime

Answer 1

Based, on your odd/even solution, it sounds like it doesn't matter what sequence the requests are processed. So, maybe you could do something like this.

SELECT id, requestor, status, created, queuetime
FROM requests
WHERE status = 1
    AND ID % 3 + 1 = 2
ORDER BY queuetime

The 3 in the second condition represents the number of processes. The number 2 indicates the 2nd process.

Answer 2

It sounds like you want to have 2+ processors taking records from the requests table.

Are you able to add a column to the requests table to indicate that it's been processed? In other words, you'll want to keep of track that it's been 'taken', and that it's 'finished'. Perhaps finished in this case, may be that you're deleting the records from the table. So consider simply adding a column like ProcessingOn DATETIME.

With this construct, you can then have each processor SELECT its batch, but of course ensuring that it doesn't take any that are already 'in-process'.

BEGIN TRAN

DECLARE @RecordsToProcess TABLE (ID int)

--grab our candidates
INSERT INTO @RecordsToProcess (ID) 
     SELECT ID FROM requests WHERE status = 1 
     AND ProcessingOn IS NULL ORDER BY queuetime

--mark our batch of records as 'in-process'.
UPDATE requests SET ProcessingOn = CURRENT_TIMESTAMP 
WHERE ID IN (SELECT ID FROM @RecordsToProcess)

--get all those records to process.
SELECT * FROM requests 
WHERE ID IN (SELECT ID FROM @RecordsToProcess)


COMMIT TRAN

You'll then likely run into the situation where a batch has failed. In that case, the records are never deleted, but their ProcessingOn is not null. Perhaps set a tolerance of retry in the SELECT whereby a threshold (perhaps 5 mins, perhaps 1 day, whatever you need) is used to judge whether those records need reprocessing.

   --grab our candidates
INSERT INTO @RecordsToProcess (ID) 
     SELECT ID FROM requests WHERE status = 1 
     AND (ProcessingOn IS NULL OR ProcessingOn < DATEADD(day, -1, ProcessingOn) 
     ORDER BY queuetime