En este artículo:

https://firebird21.wordpress.com/2013/11/06/un-stored-procedure-para-guardar-la-cantidad-de-registros-de-cada-tabla/

habíamos visto un stored procedure que nos permite guardar la cantidad de filas que tienen todas las tablas de nuestra Base de Datos; haríamos algo así para rápidamente obtener esas cantidades cuando las necesitemos.

Aquí tenemos otra alternativa, si por algún motivo la técnica mostrada en el artículo anterior no nos resulta conveniente. En este caso la cantidad de filas de una tabla siempre será la correcta después de hacer un INSERT o un DELETE en ella.

CREATE EXCEPTION E_CODIGO_OPERACION_INCORRECTO 'El código de la operación no existe. Debe ser "I" o "D"';

CREATE PROCEDURE SP_ACTUALIZAR_RECCOUNT(
   tnCodSuc TYPE OF D_CODIGOSUCURSAL,
   tcOperac TYPE OF D_CHAR1,
   tcTablax TYPE OF D_NOMBRE30)
AS
   DECLARE VARIABLE lcComando VARCHAR(128);
   DECLARE VARIABLE lnCantidadFilas INTEGER;
BEGIN

   IF (tcOperac <> 'I' and tcOperac <> 'D') THEN
      EXCEPTION E_CODIGO_OPERACION_INCORRECTO;

   UPDATE
      RECCOUNT
   SET
      REC_CANTID = REC_CANTID + IIF(:tcOperac = 'I', 1, -1)
   WHERE
      REC_CODSUC = :tnCodSuc AND
      REC_NOMBRE = :tcTablax;

   IF (ROW_COUNT = 0) THEN BEGIN -- No se encontró la tabla en RECCOUNT, por lo tanto hay que agregarla
      lcComando = 'SELECT COUNT(*) FROM ' || tcTablax;
      EXECUTE STATEMENT (lcComando) INTO :lnCantidadFilas;
      INSERT INTO RECCOUNT
                 (REC_CODSUC, REC_NOMBRE, REC_CANTID      )
          VALUES (:tnCodSuc , :tcTablax , :lnCantidadFilas) ;
   END

END;

¿Qué hace este stored procedure?

Primero, verifica que la operación a realizar sea “I” (INSERT) o sea “D” (DELETE). Si no es ni “I” ni “D” entonces eleva una excepción y por lo tanto termina la ejecución de este stored procedure.

Segundo, intenta actualizar la cantidad de filas de la tabla destino cuyo nombre está guardado en la tabla RECCOUNT. Si la operación a realizar es “I” aumenta la cantidad de filas en 1 y si la operación a realizar es “D” disminuye la cantidad de filas en 1.

Tercero, si no se pudo actualizar la tabla RECCOUNT porque el nombre de la tabla destino no existía en la tabla RECCOUNT entonces agrega una fila a la tabla RECCOUNT con el nombre de la tabla que no fue actualizada y la cantidad exacta de filas que tiene esa tabla.

De esta manera, al finalizar el stored procedure estaremos seguros que la tabla RECCOUNT tiene una fila con el nombre de la tabla destino y con la cantidad exacta de filas que tiene esa tabla destino.

RECCOUNT1

Captura 1. Si haces clic en la imagen la verás más grande

En la columna REC_NOMBRE tenemos el nombre de cada tabla de la Base de Datos, en la columna REC_CANTID la cantidad exacta de filas que tiene cada tabla.

¿Cómo llamamos al stored procedure?

Mediante un trigger AFTER INSERT o un trigger AFTER DELETE, como podemos ver a continuación:

CREATE TRIGGER AI_PRODUCTOS FOR PRODUCTOS
   ACTIVE AFTER INSERT
   POSITION 1
AS
BEGIN

   EXECUTE PROCEDURE SP_ACTUALIZAR_RECCOUNT(NEW.PRD_CODSUC, 'I', 'PRODUCTOS');

END;
CREATE TRIGGER AD_PRODUCTOS FOR PRODUCTOS
   ACTIVE AFTER DELETE
   POSITION 2
AS
BEGIN

   EXECUTE PROCEDURE SP_ACTUALIZAR_RECCOUNT(OLD.PRD_CODSUC, 'D', 'PRODUCTOS');

END;

Conclusión:

Si en nuestros programas necesitamos conocer la cantidad de filas que tiene una tabla hacer un SELECT COUNT(*) FROM MiTabla cada vez que necesitamos conocer esa cantidad no es conveniente porque en tablas que tienen centenas de miles o millones de filas se demorará mucho tiempo obtener el resultado. Es muchísimo más conveniente tener una tabla cuya misión sea guardar las cantidades de filas que tienen las demás tablas, de esta manera la consulta será rapidísima porque esa tabla (llamada RECCOUNT en este ejemplo) tiene pocas filas y además se puede tener un índice en ella con lo cual se conseguirá que la consulta sea instantánea.

La desventaja de esta técnica es que implica mayor trabajo, porque hay que escribir un trigger AFTER INSERT y un trigger AFTER DELETE para cada tabla que nos interesa, pero como la ganancia en velocidad es impresionante, bien vale la pena el pequeño esfuerzo adicional.

Artículos relacionados:

Un stored procedure para guardar la cantidad de registros de cada tabla

El índice del blog Firebird21