How to separate records into blocks for parallel processing?

Question

I have a table in SQL Server that makes the control of messages to be sent and a routine in C#, configured in a Cron, which selects pending messages, sends and marks as sent.

What I’m implementing is the ability to set up other calls in Cron so that this processing is done in parallel, thus processing a larger volume of records in less time.

The challenge is in mounting the SQL statement so that one call does not select the records of the other call.

I have some premises to follow:

• I cannot change the table structure or create other tables;
• The number of calls must be flexible and more calls can be included in Cron as per demand.

The table schema and the query I have is more or less this:

DECLARE @Mensagem TABLE (
    id_mensagem INT IDENTITY PRIMARY KEY,
    criado_em DATETIME DEFAULT GETDATE(),
    destinatário VARCHAR(250),
    assunto VARCHAR(250),
    mensagem VARCHAR(MAX),
    enviado_em DATETIME
);

INSERT @Mensagem
    (destinatário, assunto, mensagem)
VALUES
    ('[email protected]', 'Assnto 1 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 2 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 3 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 4 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 5 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 6 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 7 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 8 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 9 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 10 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 11 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 12 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 13 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 14 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 15 Lorem Ipsum', 'Mensagem Lorem Ipsum');

-- Seleciona os registros não enviados
SELECT
    M.id_mensagem,
    M.criado_em,
    M.destinatário,
    M.assunto,
    M.mensagem,
    M.enviado_em
FROM
    @Mensagem AS M
WHERE
    M.enviado_em IS NULL;

-- Após o envio pela rotina C#, marca a mensagem como enviada
UPDATE @Mensagem SET enviado_em = GETDATE() WHERE id_mensagem = 1;

Answer 1

The defined solution should consider that new messages can be received in the table at the same time that the process of sending messages is triggered or is running. That is, it is necessary to differentiate the lines that have already been selected for sending from the ones that entered the table after the sending process started. The ideal would be to add column in the table indicating the status of the message. Something like "A" (waiting for sending), "S" (selected for sending), "E" (sent) and "R" (error in sending); maybe some other situation that you think necessary to be registered. This would reduce the possibility of the same message being sent more than once. However, since you cannot change the table structure, this will have to be simulated differently. One option is to use the column enviado_em, with the following values:

• NULL: message waiting to be sent;
• 0: message selected for sending;
• -1: error in sending message;
• other value: message sent.

To get the mailing list to send, and at the same time update the status of the selected lines, I suggest you use the UPDATE statement in conjunction with the OUTPUT clause.

-- código #1
UPDATE Mensagem
  set enviado_em= 0
  output INSERTED.id_mensagem, 
         INSERTED.destinatario, 
         INSERTED.assunto, 
         INSERTED.mensagem
  where enviado_em is null;

Next you can start sending the set of messages returned by executing code #1 or then fire threads sending, passing on to each a subset of the messages. The number of threads as well as the subsets are dynamically defined in the program in #C, depending on the amount of messages to send. That is, just a single call on the Cron.

As for the marking of the messages sent, there are some approaches. One of them is that after sending each message the situation is updated in the database by thread, using the id_message value to locate the line to be updated. If the message was sent, this is the source suggestion:

-- código #2
UPDATE Mensagem
  set enviado_em= current_timestamp
 where id_mensagem= @id;

Another is that each thread, at the end, inform the main body of the program which messages were sent and which ones were not, leaving the responsibility of the main body update the status of the messages in the table.

Answer 2

One approach that solves is to calculate the rest of the division between a single constant and the number of calls that will be made, thus generating an integer that groups the results.

In the example below, the constant used is the auto-increment column, as it generates a unique value for each row:

DECLARE @Mensagem TABLE (
    id_mensagem INT IDENTITY PRIMARY KEY,
    criado_em DATETIME DEFAULT GETDATE(),
    destinatário VARCHAR(250),
    assunto VARCHAR(250),
    mensagem VARCHAR(MAX),
    enviado_em DATETIME
);

INSERT @Mensagem
    (destinatário, assunto, mensagem)
VALUES
    ('[email protected]', 'Assnto 1 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 2 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 3 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 4 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 5 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 6 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 7 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 8 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 9 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 10 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 11 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 12 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 13 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 14 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 15 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 16 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 17 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 18 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 19 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 20 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 21 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 22 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 23 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 24 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 25 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 26 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 27 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 28 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 29 Lorem Ipsum', 'Mensagem Lorem Ipsum'),
    ('[email protected]', 'Assnto 30 Lorem Ipsum', 'Mensagem Lorem Ipsum');

-- Seleciona os registros não enviados apenas para uma determinada chamada
SELECT
    (M.id_mensagem % 3) + 1 AS chamada,
    M.id_mensagem,
    M.criado_em,
    M.destinatário,
    M.assunto,
    M.mensagem,
    M.enviado_em
FROM
    @Mensagem AS M
WHERE
    M.enviado_em IS NULL
    AND (M.id_mensagem % 3) + 1 = 1;-- Aqui entra o número da chamada, neste exemplo seria 1, 2 ou 3

What other approaches can solve this problem?