Lanzar un Store Procedure cuando se detecta inactividad

[afxe]

Saludos a todos. (En el título: Stock -> Store) Cuando se inserta o modifica datos en una tabla de movimientos de artículos (ventas, compras, traspasos...) lanzo en un trigger una Procedure que se encarga de mantener el stock y datos estadísticos.
En una instalación, a primera hora, se procesan todos los pedidos recibidos el día anterior (desde tres puestos diferentes) y se crean las líneas de ventas correspondientes. Unas 6000 líneas de ventas diarias... lo cual hace que se lance el Store Procedure 6.000 veces. Realmente se han movido 200 artículos, sólo habría que lanzarlo 200 veces cuando se termine la generación masiva en todos los puestos. Durante el resto del día, se hace sólo 20 o 30 documentos por hora, desde más de 10 puestos. Se me ha ocurrido crear una tabla donde el trigger, en vez de lanzar el procedure, meta el código del artículo y el almacén, siempre que no exista ya, y cuando se detecte inactividad lanzar el store procedure para la lista de artículos incluidos y vaciar la tabla...

El tema es... ¿Cómo detecto inactividad en Firebird? o por lo menos... ¿Cómo detecto que en un determinado tiempo no se han añadido artículos a esta tabla? Estoy hablando de ejecución en el propio firebird... sería muy fácil tener un programa con un Timer comprobando la tabla y las última inserciones en la misma, y lanzar el store procedure cuando detecte que se han pasado más de 20 o 30 segundos sin incluir artículos...eso indicaría que han terminado de generar documentación... y seguiría funcionando para el resto de creación de documentos durante el día de la misma manera, evitando que se ralentice la grabación del documento haciendo cálculos de stocks y estadísticas.

Pero por experiencia, tener una aplicación desatendida en el servidor suele dar problemas... un error, un deadlock, una desconexión de red o un reinicio del servidor.... y todos los stocks estarían mal hasta que alguien se de cuenta de que el programa se ha parado. Pero no encuentro cómo crear un timer en Firebird o, lo que sería la caña, detectar que el servidor está inactivo, y ponerme a procesar la tabla en tiempos de inactividad, y parar de procesarla cuando entre en actividad... eso sería ideal.

[Casimiro Notevi]

No entiendo el problema, ¿por qué no haces todo ese en tiempo real? ¿por qué tienes que esperar para actualizar stocks y demás?

[afxe]

Hola Casimiro. Como decía, se generan muchos documentos, más de 600, en los que se venden los mismos 200 artículos docenas de veces... Una vez que almacén ha sacado la mercancía y se ha cargado para reparto con el chequeo correspondiente de que no falta nada ni sobra nada, se pasa de pedido a albaranes y facturas los 600 documentos para 20 rutas de reparto, desde tres equipos a la vez, imprimiendo y separando documentos en impresoras diferentes para cada ruta. Mi intención es que se generen e impriman los documentos sin recalcular stock ni estadísticas... y a determinadas horas o cuando no haya carga de trabajo, que el servidor haga ese proceso. No quiero que el stock esté mal durante demasiado tiempo, pero es que cuando en los mismos 20 minutos TODOS se ponen a generar documento se ralentiza demasiado por culpa de estar calculando el stock una y otra vez para los mismos artículos. Mejor esperar a que terminen de generarse todos los documentos y que se lance automáticamente... pero todo desde el servidor, porque los puestos no saben cuando terminan los demás.

Otra cosa... ¿¿¿Un store procedure que contenga esta línea carga de trabajo o memoria el servidor??? (a ver qué opináis):

WHILE (CURRENT_TIMESTAMP < roxima_ejecucion) DO SUSPEND;

[Casimiro Notevi]

Veamos, el stock es básicamente descontar una cantidad de un campo, algo que lo hace automáticamente un trigger. Algo debe no estar haciéndose bien si dices que se ralentiza el sistema.
En cuanto al bucle que indicas tendrás que probarlo, pero así a simple vista no tiene buena pinta , quiero decir que lo veo innecesario. Yo comprobaría qué es lo que realmente es lento en el sistema para solucionarlo.
Porque si estamos hablando de unos cientos de artículos y una decena de puestos de trabajo... eso es una carga inapreciable para una base de datos como firebird o similar.

[afxe]

El bucle WHILE DO SUSPEND; no funciona... ya hice las pruebas. Gracias por tu interés Casimiro. Te pediría que obviaras la necesidad que tengo de calcular o no el stock en el trigger. No sólo calcular el stock... es algo complejo y la ejecución del trigger hace que la documentación tarde 80 minutos más de lo que sería realmente necesario, después de estar haciendo pruebas durante varios días en servidores paralelos, de 100 minutos a sólo 20 minutos si desactivo los triggers de mi fichero de Movimientos.

Eso significa que los repartidores tienen que esperar una hora y media entre que le entregan la carga y la entrega de la documentación. En media hora cargan la mercancía en el camión, sería genial entregarles los albaranes y facturas antes de que terminen de cargar y no tengan que estar una hora más esperando. Son muchas horas, 20 repartidores en cada una de las 40 delegaciones que tenemos en España.

Mi solicitud al grupo es:
¿Alguien sabe cómo crear una tarea programada en Firebird que se ejecute cada x minutos? (dentro del propio servicio de FB, no mediante un batch o un ejecutable externo)

[Casimiro Notevi]

No he visto en profundidad las especificaciones de las últimas versiones, pero no creo.

[Neftali [Germán.Estévez]]

Empezado por afxe

El tema es... ¿Cómo detecto inactividad en Firebird? o por lo menos... ¿Cómo detecto que en un determinado tiempo no se han añadido artículos a esta tabla?

Yo tal vez intentaría pensarlo al revés.
Creo recordar que Firebird posee Jobs. Crea un job programado (por ejemplo) cada 5 minutos. Cuando lo lances, si detectas que en los últimos 5 minutos se han añadido artículos, sales sin hacer nada.
Para detectar eso, basta con crear un campo Timestamp/fecha si no lo tienes ya que se rellene con cada operación.

[[duilioisola]]

Una tercera vía es utilizar semáforos o un campo que indique que no se desea calcular el stock.

Por ejemplo:

update semaforos set calcular_stock = 0

-- insert de 6000 pedidos.

update semaforos set calcular_stock = 1

Y en los triggers o procedimientos que tocan el stock

Código SQL [-]
create trigger detalle_pedido_bi0 before insert
declare variable calcular_stock smallint;
begin
    -- Obtengo valor de semaforo
    select calcular_stock from semaforos
    into : calcular_stock;

    -- Calculo stock si el semaforo esta establecido
    if (calcular_stock = 1) then
    begin
        update stock ...
    end
end

Podría ser relativamente peligroso si el último UPDATE donde vuelves a poner el semáforo no se ejecuta por algún error.

[[duilioisola]]

Una tercera vía es utilizar semáforos o un campo que indique que no se desea calcular el stock.

Por ejemplo:

insert into cabecera_pedido(...) values(...);
insert into detalle_pedido(id_cabecera, articulo, unidades, ..., calcular_stock)
values(: id, : articulo, : unidades, ..., 0);

Y en los triggers o procedimientos que tocan el stock

Código SQL [-]
create trigger detalle_pedido_bi0 before insert
declare variable calcular_stock smallint;
begin
    -- Calculo stock si se desea
    if (new.calcular_stock = 1) then
    begin
        update stock ...
    end

    -- Fuerzo CALCULAR_STOCK por si en el futuro se retoca este registro
    new.calular_stock = 1;
end

[afxe]

Gracias a todos. La idea de Neftali me pareció buena, aunque no he encontrado la manera de que el Firebird ejecute una tarea programada. Os comento cómo ha quedado, por si en posteriores búsquedas alguien lo necesita.
Idea base: Mantener disponibilidad y estadística de un artículo por cada delegación sin saturar el servidor:

Primero, crear una tabla con los artículos que se han vendido, pedido, comprado, traspasado, presupuestado, ajustado:

Código SQL [-]
CREATE TABLE Arti_Actu_Dispon (
  cod_articulo INTEGER NOT NULL,
  cod_delegacion Integer not Null,
  modo INTEGER not null,
  Registrado TIMESTAMP,
  CONSTRAINT PK_ARTI_ACTU_DISPON PRIMARY KEY (COD_ARTICULO, COD_DELEGACION, MODO)
);

CREATE INDEX IDX_ARTI_ACTU_DISPON_1 ON ARTI_ACTU_DISPON (REGISTRADO);

Segundo, meto en todos los triggers de las tablas que provoquen movimiento del artículo (preventas, pedidos, presupuestos, traspasos, compras, ventas...) indicado que ese artículo debe recalcularse sus parámetros de disponibilidad, pongo ejemplo de uno de estos triggers:

Código SQL [-]
ALTER TRIGGER AIUD_EXTRACTO ACTIVE
AFTER insert OR update OR delete POSITION 5
AS
BEGIN
  IF (DELETING) THEN
    In autonomous transaction do update or INSERT INTO ARTI_ACTU_DISPON VALUES(OLD.COD_ARTICULO, OLD.COD_DELEGACION, 1, current_timestamp)  matching (cod_articulo, cod_delegacion, modo);

  IF (UPDATING AND ( (OLD.COD_ARTICULO <> NEW.COD_ARTICULO) OR (NEW.COD_DELEGACION <> OLD.COD_DELEGACION) ) ) THEN
    In autonomous transaction do update or INSERT INTO ARTI_ACTU_DISPON VALUES(OLD.COD_ARTICULO, OLD.COD_DELEGACION, 1, current_timestamp)  matching (cod_articulo, cod_delegacion, modo);
    
  IF (INSERTING OR UPDATING) THEN
    In autonomous transaction do update or INSERT INTO ARTI_ACTU_DISPON VALUES(NEW.COD_ARTICULO, new.COD_DELEGACION, 1, current_timestamp) matching (cod_articulo, cod_delegacion, modo);
END

El autonomous transaction es porque he tenido frecuentes deadlocks (mucha intensidad de entrada de datos). Sólo se inserta una única vez cada artículo. Si se ha movido 300 veces el artículo, no tengo que calcular 300 veces su disponibilidad, sólo una única vez cuando termine de moverlo 300 veces.

El siguiente paso ha sido crear el STORE PROCEDURE que se dedica a actualizar la disponibilidad por delegaciones de los artículos que estén contenidos en esta tabla. El bucle se detendrá cuando se hayan actualizado todos los artículos o se detecte que empiezan a entrar nuevos artículos en la tabla.

Código SQL [-]
CREATE OR ALTER PROCEDURE proc_ARTI_ACTU_DISPON
AS
  DECLARE VARIABLE CodArt integer;
  Declare Variable CodDlg Integer;
  Declare variable vModo Integer;
  Declare Variable ultima_entrada TimeStamp;

BEGIN
  WHILE (EXISTS(SELECT COD_ARTICULO FROM ARTI_ACTU_DISPON)) DO begin
    select cod_articulo, cod_delegacion, modo from ARTI_ACTU_DISPON ROWS 1 TO 1 into :CodArt, :CodDlg, :vModo;
    in autonomous transaction do begin
      EXECUTE PROCEDURE PROC_ACTU_DISPON (:CodDlg, :CodArt, :vModo);
      DELETE FROM ARTI_ACTU_DISPON WHERE COD_ARTICULO = :CodArt and cod_delegacion = :CodDlg and modo = :vModo;
    end
    SELECT MAX(REGISTRADO) FROM ARTI_ACTU_DISPON INTO :Ultima_Entrada;
    if (DateAdd(30 SECOND TO Ultima_entrada) > CURRENT_TIMESTAMP) THEN BREAK;
  end
 
 when any DO 
   insert INTO LOG (FECHA, COD_USUARIO, ACCION, FICHERO, COD_DOCUMENTO, REFERENCIA) 
   VALUES (current_timestamp, 1, 'EXEC', 'ARTI_ACTU_DISPON', :CodArt, 'PROC_ACTU_DISPON ') ;
   
END

Este proceso debe estar especialmente protegido, ya que se ha de ejecutar en modo desatendido, y si da un problema, no debe bloquearse el proceso, de ahí el autonomous transaction (por si el proceso se lanzara por error varias veces) y el When Any, para que registrar las incidencias.

Por último, he creado otro Store Procedure que ponga en marcha el bucle si hace más de 30 segundos que no se actualiza la tabla de articulos a procesar:

Código SQL [-]
CREATE OR ALTER PROCEDURE TIMER_execute
AS
  Declare Variable ultima_entrada TimeStamp;
  Declare Variable nReg Integer;
BEGIN
  nReg = (Select cod_articulo from arti_actu_dispon where cod_articulo > 0 rows 1 to 1);
  if (nReg > 0) then 
  BEGIN
     /* llevamos mas de 30 segundos sin hacer entradas */ 
    SELECT MAX(REGISTRADO) FROM ARTI_ACTU_DISPON INTO :Ultima_Entrada;
    if (DateAdd(30 SECOND TO Ultima_entrada) < CURRENT_TIMESTAMP) THEN
      EXECUTE PROCEDURE PROC_ARTI_ACTU_DISPON;  
  END
END

Tengo un programa en delphi funcionando sólo en el servidor dedicado ha lanzar sincronizadores, actualizadores, chequeadores... y he metido un timer para que cada 60 segundos se lance el TIMER_EXECUTE.

En hora punta (08:00-09:00) se han producido 7345 líneas de venta, en los que se ha vendido 720 productos diferentes, mientras se está generando líneas de venta el proceso de actualización de disponibilidad (stock, pedidos, fechas prevista de entrega, activación de punto de pedido, descuento de depositos...) no se ejecuta... cuando lleva 30 segundos sin que se muevan productos, se procesa los archivos de disponibilidad para esos 720 productos, y no se pasa el proceso 7345 veces, como ocurría antes.

Lleva un par de días en funcionamiento y por ahora bien, me ha dado un sólo DeadLock porque creo que tres procesos han ido a actualizar el mismo artículo en el mismo milisegundo... y a petado el AIUD_EXTRACTO (no entiendo porqué, ya que está en una transacción autónoma y no debería estar bloqueado el update en ninguna trasacción).

Lo único que me preocupa es que el ejecutable que lo lanza, a veces se cae... falla algún proceso, se reinicia el servidor, alguien lo para para mantemiento, pierde la conexión con alguna bases de datos...