Jérôme De Cuyper

Recientemente, estuve involucrado en el desarrollo de una trivia online para niños. Durante 3 meses, 12 retos se publicaron y unos 20k jugadores se registraron. Conforme avanzaban las semanas, los niños iban ganando puntos. Desde el primer día de la trivia, estaba disponible una página con la lista de jugadores ordenados por puntos y fecha de participación. Para generar ese listado, se tenía que realizar una suma de todos los puntos de cada jugador y luego ordenarlos por puntos obtenidos. Durante las primeras semanas, el listado se cargaba sin problema. Pero como lo podrán imaginar, el query se hizo poco a poco más y más lento. El cálculo ya involucraba muchos datos y aunque logramos mejorar un poco el query, no dejaba de alentarse con los días.

Obviamente, fue necesario encontrar una solución. Como el query estaba alentando mucho la generación de la lista de ganadores, se me ocurrió que era necesario mandar su ejecución en un proceso asíncrono al thread principal. Cada vez que un jugador grabe algún punto, se tendrá que calcular nuevamente los resultados pero sin afectar a la consulta de la tabla de ganadores.

Existen varias maneras de crear un proceso asíncrono con SQL Server 2008 pero una de éstas consiste en utilizar el no muy famoso Service Broker:

El Service Broker permite implementar un servicio de mensajería bastante robusto sin salirse de la base de datos. En computación, una mensajería (o cola de mensajes) es un componente lógico que se ocupa para permitir la comunicación entre procesos. Además provee un protocolo de comunicación asíncrona en el cuál el emisor y el receptor no deben interactuar con el mensaje al mismo tiempo. El emisor agrega un mensaje a la cola y el receptor lo consulta en otro momento.  En mi caso, la ejecución del query es mi “mensaje”. El emisor es la trivia y el receptor es el Service Broker. Cada vez que un niño marca puntos, la aplicación mete en la cola un mensaje para ejecutar el cálculo de posiciones. Luego, el Service Broker se encarga de ejecutar el cálculo sin interumpir al thread principal. Y no importa si varios jugadores están grabando puntos al mismo tiempo, porque por cada nuevo punto, el Service Broker se encargará de recalcular la tabla de posiciones.

Originalmente, el Service Broker ha sido diseñado para permitir intercambiar datos entre bases de datos distribuidas y así repartir la carga de ejecución entre varias máquinas. Sin embargo, también se puede utilizar, como en mi caso, adentro de la misma base de datos y en el mismo servidor. Ahora, ¿cómo implementar este servicio? Desafortunadamente, no existen tantos artículos sobre este componente como me lo esperaba. Tuve que buscar y debugear un buen antes de poder levantar mi propio servicio. La buena noticia es que valió la pena el esfuerzo. Aquí les explico cómo armar un servicio de mensajería sencillo.

Antes que todo, se tiene que confirmar que la base de datos tiene el Service Broker habilitado. Esto se puede visualizar en las propiedades de la base de datos:

Se puede habilitar mediante la siguiente instrucción SQL:

ALTER DATABASE miBD SET ENABLE_BROKER

En los dos casos, la instrucción puede tardar un poco en ejecutarse. Cuando ya está lista nuestra base de datos, podemos empezar a configurar los componentes del servicio. Para proteger los accesos y las lecturas de los mensajes, se tiene que definir tanto el tipo de mensaje que se va a manejar, así como el contrato que va a existir entre el emisor y el receptor. Se puede requerir, por ejemplo, que los mensajes sean en formato XML y obligar el servicio a validar ese formato durante la transmisión de los mensajes. En nuestro caso, vamos a ocupar el formato más sencillo y sin requerir validación ninguna:

CREATE MESSAGE TYPE CalcularPosiciones VALIDATION = NONE;

Los intercambios de mensajes entre emisor y receptor se llaman conversaciones. Cada conversación está asociada con un contrato.  El anterior define el sentido de la conversación (de emisor a receptor o vice-versa) así como el tipo de mensaje que se puede intercambiar. Nuestro contrato define que solamente se pueden intercambiar mensajes de tipo ‘CalcularPosiciones’ y que sólo el iniciador de la conversación puede mandar mensajes de este tipo :

CREATE CONTRACT ContratoCalculoPosiciones
(
CalcularPosiciones SENT BY INITIATOR
)

Luego, se crea una cola que se va a encargar de procesar cada nuevo mensaje. La configuramos con un estatus activo. La propiedad MAX_QUEUE_READERS indica si se pueden o no procesar varios mensajes a la vez. Para evitar colisiones, dejamos esta propiedad en un 1. El stored procedure ’spCalcularPosiciones’ será disparado por cada activación de la cola de recepción. Como lo veremos más adelante, este stored procedure se encargará del cálculo de la tabla de posiciones.

CREATE QUEUE [ColaRecepcion];
ALTER QUEUE [ColaRecepcion] WITH ACTIVATION
(
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = spCalcularPosiciones,
EXECUTE AS SELF
);

Para que la cola de recepción funcione, se le tiene que asociar a un servicio de recepción. El servicio tiene como tarea entregar los mensajes a la cola. Se asocian de la siguiente forma:

CREATE SERVICE [ServicioRecepcion] ON QUEUE [ColaRecepcion]([ContratoCalculoPosiciones]);

Para poder mandar los mensajes, se necesita crear una cola de envío y asociarla con un servicio emisor:

CREATE QUEUE [ColaEmisor];
CREATE SERVICE [ServicioEmisor] ON QUEUE [ColaEmisor];

Ya tenemos listas nuestras dos colas de mensajes así como nuestros servicios. Ahora nos falta crear el stored procedure que se va a disparar por cada mensaje recibido:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCalcularPosiciones]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Handle UNIQUEIDENTIFIER;
DECLARE @MessageType SYSNAME;
DECLARE @fecha DATETIME;

RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name
FROM [ColaRecepcion];

SELECT @Handle
IF(@Handle IS NOT NULL)
BEGIN
-- Calcular puntos de todos los jugadores e insertarlos en la tabla de resultados
-- Para efectos de prueba, también se puede inserta un registro en alguna tabla con efecto de crear un log de los movimientos de cada mensaje
END
END

El stored procedure se encarga de buscar, con la instrucción RECEIVE, el último mensaje disponible en la cola. Se recupera el ‘handle’ de la conversación. Cada intercambio
de mensaje se asocia con un handle, un identificador único. Después de recuperar el último mensaje, se realiza el cálculo de las posiciones de cada jugador o cualquier otro tipo de cálculo que necesiten.

Ya están listos todos los componentes. Ahora, nada más nos falta probar que todo esté funcionando correctamente. Para mandar un mensaje a la cola, se ocupan las siguientes instrucciones:

DECLARE @MessageBody XML
SET @MessageBody = '';

DECLARE @Handle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [ServicioEmisor]
TO SERVICE 'ServicioRecepcion'
ON CONTRACT [ContratoCalculoPosiciones]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @Handle
MESSAGE TYPE [CalcularPosiciones](@MessageBody);

Una vez que se manda el mensaje, el servicio de recepción se activa y agrega el mensaje a la cola de recepción que a su vez dispara nuestro stored procedure. Si todo sale bien, se debe ejecutar correctamente nuestro cálculo de posiciones. El envío del mensaje se puede incluir, por ejemplo, en un trigger asociado con la inserción de registros en una tabla. En mi caso, agregue un trigger a la tabla de puntos. Cada vez que un jugador inserta puntos en esa tabla, se dispara un mensaje nuevo hacia nuestro Service Broker. La gran ventaja de este servicio de mensajería es que la inserción de los puntos ya no está afectada por el cálculo de posiciones. Mientras se insertan nuevos puntos, el cálculo se ejecuta pero de forma asíncrona sin alentar el thread principal. Gracias a este servicio, la consulta de la tabla de posiciones ya no está alentada por ningún cálculo y tiene un tiempo de respuesta inmediata.

Esta implementación del Service Broker es bastante sencilla. Si requieren configurarla para un desarrollo más complejo, les aconsejo consultar el blog de Remus Rusanu. Este blog contiene los artículos más completos que he visto sobre el Service Broker. También, pueden consultar una pregunta que dejé en el sitio de Stackoverflow.

FUENTES:
Crear Base de Datos (.sql)
Crear Service Broker (.sql)
Activar Service Broker (.sql)
Borrar Service Broker (.sql)