Verificando la ejecución óptima de los stored procedures

1 comentario

Así como podemos verificar que los SELECT estén optimizados, también podemos hacer lo mismo con los stored procedures, aquí tenemos un ejemplo:


EXECUTE PROCEDURE GENERAR_ASIENTOS_2(2013)

llama al stored procedure GENERAR_ASIENTOS_2 el cual recibe un parámetro de entrada que corresponde al año. Al verificar su ejecución con el programa EMS SQL Manager nos encontramos con esto:

VERIFICAR1

(si haces clic en la imagen la verás más grande)

¿Más de 97 millones de registros de venta hasta mayo de 2013? eso es una total exageración, hay algo que está muy mal ahí. ¿Más de 18 millones de compras hasta mayo de 2013? imposible. ¿6 minutos y 25 segundos para completar la ejecución? demasiado tiempo, hay que acortar eso. Así que optimizamos el stored procedure para asegurarnos de que ahora sí use índices y el resultado es el siguiente:

STORED2

(si haces clic en la imagen la verás más grande)

 Se trata del mismo stored procedure pero ahora usando los índices correctos. Y la diferencia es grandísima.

  • En el primer caso la tabla VENTASDET fue leída 97.541.444 veces, en el segundo caso solamente 18.629 veces
  • En el primer caso la tabla COMPRASDET fue leída 18.666.752 veces, en el segundo caso solamente 7.384 veces
  • En el primer caso la tabla ASIENTOSDET fue leída 473.588 veces, en el segundo caso solamente 44.660 veces
  • En el primer caso la ejecución del stored procedure demoró 6 minutos y 25 segundos. En el segundo caso demoró 8 segundos

Como puedes ver, se ha logrado una mejora sustancial al usar los índices correctos y optimizar el código. Es por ese motivo que siempre debes verificar que la ejecución de tu stored procedure sea óptima. A veces, sin darte cuenta cometes algunos pequeños errores cuyas consecuencias pueden ser catastróficas (en este caso, el problema fue que los índices eran CMC_CODSUC, CMC_IDENTI en la tabla COMPRASCAB y VTC_CODSUC, VTC_IDENTI en la tabla VENTASCAB, pero en las consultas había puesto CMC_IDENTI y VTC_IDENTI, por lo tanto no se usaban los índices y ambas tablas se recorrían muchísimas más veces de las necesarias).

También es conveniente hacer clic en la pestaña “Summary” para ver un resumen de todas las inserciones, actualizaciones, borrados y consultas.

VERIFICAR3

(si haces clic en la imagen la verás más grande)

 Conclusión:

Siempre debes verificar que tus stored procedures estén optimizados porque aunque a primera vista puedan parecerte que está todo ok pueden tener algunos errores cuyas consecuencias pueden ser drásticas.

Artículos relacionados:

Usando índices correctos para aumentar la velocidad de las consultas

El índice del blog Firebird21

Error en un artículo del blog

Deja un comentario

En el artículo:

https://firebird21.wordpress.com/2013/06/26/registrando-errores-en-una-tabla-de-log/

se deslizó un error involuntario, dice:

SET TERM ^ ;

CREATE TRIGGER LOG_BI FOR LOG
   ACTIVE BEFORE INSERT
   POSITION 1
AS
BEGIN

   NEW.LOG_USUARI = CURRENT_USER;
   NEW.LOG_FECHOR = CURRENT_TIMESTAMP;
   NEW.LOG_COMPUT = (SELECT M.MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS M WHERE M.MON$ATTACHMENT_ID = CURRENT_CONNECTION);

END^

SET TERM ; ^

pero allí no se debe emplear la variable de contexto CURRENT_TIMESTAMP porque ella tiene la fecha y hora en que empezó la transacción, que no es lo que queremos. Lo que queremos es que muestre la fecha y la hora en que ocurrió cada inserción dentro de la tabla de LOG.

El trigger correcto es el siguiente:

SET TERM ^ ;

CREATE TRIGGER LOG_BI FOR LOG
   ACTIVE BEFORE INSERT
   POSITION 1
AS
BEGIN

   NEW.LOG_USUARI = CURRENT_USER;
   NEW.LOG_FECHOR = CAST('NOW' AS TIMESTAMP);
   NEW.LOG_COMPUT = (SELECT M.MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS M WHERE M.MON$ATTACHMENT_ID = CURRENT_CONNECTION);

END^

SET TERM ; ^

 Porque ‘NOW’ sí nos da la fecha y hora actuales, en cualquier instante.

Otro ejemplo del uso de la tabla de LOG

1 comentario

En este artículo:

https://firebird21.wordpress.com/2013/06/26/registrando-errores-en-una-tabla-de-log/

hemos visto como registrar errores en una tabla de log, el ejemplo allí mostrado usaba un trigger, ahora veremos uno similar pero usando un stored procedure y explicando más detenidamente como funciona:

SET TERM ^ ;

CREATE PROCEDURE EXISTE_RETENCION(
   tnCodSuc D_CODIGOSUCURSAL,
   tcRetNro D_NOMBRE15)
RETURNS(
   tlRetencionExiste TYPE OF D_BOOLEAN)
AS
   DECLARE VARIABLE lcTemp TYPE OF COLUMN RETENCIONESCAB.RTC_NUMERO;
BEGIN

IN AUTONOMOUS TRANSACTION DO BEGIN
   INSERT INTO LOG (LOG_TABLAX, LOG_STOPXX, LOG_VALORX) VALUES('RETENCIONESCAB', 1, 'Aquí empieza el stored procedure EXISTE_RETENCION');
END

   lcTemp = (SELECT
                COALESCE(R.RTC_NUMERO, '')
             FROM
                RETENCIONESCAB R
             WHERE
                R.RTC_CODSUC = :tnCodSuc AND
                R.RTC_NUMERO = :tcRetNro);

IN AUTONOMOUS TRANSACTION DO BEGIN
   INSERT INTO LOG (LOG_TABLAX, LOG_STOPXX, LOG_VALORX) VALUES('RETENCIONESCAB', 2, 'lcTemp = ' || lcTemp);
END

   tlRetencionExiste = 'F';

   IF (tcRetNro = lcTemp) THEN
      tlRetencionExiste = 'T';

IN AUTONOMOUS TRANSACTION DO BEGIN
   INSERT INTO LOG (LOG_TABLAX, LOG_STOPXX, LOG_VALORX) VALUES('RETENCIONESCAB', 3, 'tlRetencionExiste = ' || tlRetencionExiste);
END

IN AUTONOMOUS TRANSACTION DO BEGIN
   INSERT INTO LOG (LOG_TABLAX, LOG_STOPXX, LOG_VALORX) VALUES('RETENCIONESCAB', 4, 'Fin del stored procedure EXISTE_RETENCION');
END

END^

SET TERM ; ^

En este stored procedure tenemos cuatro IN AUTONOMOUS TRANSACTION y en cada uno de ellos un INSERT INTO LOG. Si se desea, se pueden tener varios INSERT INTO LOG dentro de un IN AUTONOMOUS TRANSACTION.

Los valores que guardamos en las columnas LOG_TABLAX, LOG_STOPXX y LOG_VALORX pueden ser cualesquiera, lo único importante es que en LOG_TABLAX y en LOG_VALORX guardemos caracteres y en LOG_STOPXX guardemos un número.

El Firebird automáticamente hace el COMMIT de la transacción autónoma cuando se llega al END de la misma. No importa si la transacción principal terminó con un COMMIT o con un ROLLBACK, la transacción autónoma siempre termina con un COMMIT, o sea que a la tabla de LOG siempre se le insertan filas.

IN AUTONOMOUS TRANSACTION puede ser escrito en cualquier parte dentro del stored procedure o del trigger

A tu usuario debes asignarle derechos de INSERT y de SELECT de la tabla LOG, para que puedas ver su contenido

Todos los comandos que escribas dentro de IN AUTONOMOUS TRANSACTION deben finalizar con un punto y coma

El END de IN AUTONOMOUS TRANSACTION no debe finalizar con un punto y coma

Artículos relacionados:

Registrando errores en una tabla de LOG

El índice del blog Firebird21

Similitudes y diferencias entre stored procedures y triggers

5 comentarios

En general, quienes recién empiezan a usar Firebird entienden bastante bien el  concepto de stored procedure, después de todo es lo mismo que una rutina, procedimiento o función en el lenguaje de programación que están acostumbrados a usar (Visual FoxPro, Visual Basic, Delphi, C, C++, Java, etc.).

Sin embargo, suelen tener dificultades en entender a los triggers, así que aquí explicaremos en que se asemejan y en que se diferencian.

Semejanzas:

  1. Ambos son código fuente que escribimos dentro de la Base de Datos
  2. En ambos podemos usar excepciones
  3. En ambos podemos usar las operaciones: INSERT, UPDATE, DELETE, SELECT
  4. En ambos podemos usar EXECUTE PROCEDURE
  5. En ambos se pueden escribir comentarios

Diferencias:

  1. A los stored procedures se los debe llamar explícitamente con el comando EXECUTE PROCEDURE o con el comando SELECT. Los triggers son invocados automáticamente por el Firebird
  2. Los triggers disponen de dos seudo-variables llamadas NEW y OLD. Con NEW sabemos cual es el valor que se guardará en una columna, con OLD conocemos cual es el valor que actualmente está guardado en esa columna
  3. En los triggers tenemos tres variables de tipo boolean, llamadas: INSERTING, UPDATING, DELETING, las cuales nos permiten saber si el trigger fue lanzado durante una operación de inserción, de actualización o de borrado
  4. Los stored procedures pueden no recibir parámetros de entrada, recibir un solo parámetro de entrada, recibir muchos parámetros de entada. Los triggers no reciben parámetros de entrada
  5. Los stored procedures pueden no devolver valores, devolver un solo valor, devolver muchos valores. Los triggers nunca devuelven valores
  6. Para determinar en que orden se ejecutarán los triggers se usa la variable POSITION que puede valer 0, 1, 2, 3, 4, …, etc. Primero se ejecuta el trigger que tiene POSITION = 0, luego el que tiene POSITION = 1, y así sucesivamente

Ejemplo de stored procedure:

Listado 1.

CREATE PROCEDURE EXISTE_NUMERO_NOTA_CREDITO(
   tcCodSuc TYPE OF COLUMN MOVIMCAB.MVC_CODSUC,
   tcNotCre TYPE OF COLUMN MOVIMCAB.MVC_NOTNRO)
RETURNS(
   ftlDocumentoExiste TYPE OF D_BOOLEAN)
AS
   DECLARE VARIABLE lcNumero TYPE OF COLUMN MOVIMCAB.MVC_NOTNRO;
BEGIN

   SELECT
      COALESCE(M.MVC_NOTNRO, '')
   FROM
      MOVIMCAB M
   WHERE
      M.MVC_CODSUC = :tcCodSuc AND
      M.MVC_TIPMOV = 'NCR' AND
      M.MVC_NOTNRO = :tcNotCre
   INTO
     :lcNumero;

   ftlDocumentoExiste = 'F';

   IF (tcNotCre = lcNumero) THEN
      ftlDocumentoExiste = 'T';

END

En este stored procedure tenemos:

  • Dos parámetros de entrada (tcCodSuc y tcNotCre)
  • Un parámetro de salida (ftlDocumentoExiste)
  • Una variable interna del stored procedure (lcNumero)

Ejemplo de trigger:

Listado 2.

CREATE TRIGGER DEPARTAMENTOS_BI FOR DEPARTAMENTOS
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
AS
BEGIN

   IF (NEW.DEP_IDENTI IS NULL OR NEW.DEP_IDENTI <= 0) THEN
      EXCEPTION E_SIN_IDENTIFICADOR;

   IF (NEW.DEP_CODIGO IS NULL OR CHAR_LENGTH(TRIM(NEW.DEP_CODIGO)) = 0) THEN
      EXCEPTION E_SIN_CODIGO;

   IF (NEW.DEP_NOMBRE IS NULL OR CHAR_LENGTH(TRIM(NEW.DEP_NOMBRE)) = 0) THEN
      EXCEPTION E_SIN_NOMBRE;

END

Este trigger valida las columnas que se quieren insertar o actualizar en la tabla de DEPARTAMENTOS, impidiendo que ocurra la inserción o actualización cuando la columna DEP_IDENTI es nula o es cero, la columna DEP_CODIGO es nula o está vacía, la columna DEP_NOMBRE es nula o está vacía.

Como el trigger es BEFORE INSERT OR UPDATE será lanzado automáticamente cada vez que se quiera insertar o actualizar alguna fila de la tabla de DEPARTAMENTOS.

O sea que si escribimos:

INSERT INTO DEPARTAMENTOS VALUES(0, 1, ‘PRUEBA’);

el Firebird lanzará el trigger. También lo lanzará si escribimos algo como:

UPDATE DEPARTAMENTOS SET DEP_NOMBRE = ‘Otra prueba’ WHERE DEP_IDENTI = 123;

Artículos relacionados:

Entendiendo a los stored procedures

Entendiendo a los triggers

Entendiendo a las excepciones

El índice del blog Firebird21

El foro del blog Firebird21

Backup y restore a una versión más nueva del Firebird

3 comentarios

A veces puedes necesitar que una Base de Datos creada con una versión anterior del Firebird sea usada con una versión más reciente.

Si no necesitas las características adicionales que introdujo la versión más reciente entonces te puedes conectar a esa Base de Datos sin problema.

Sin embargo, a veces necesitas de las características adicionales y en ese caso debes efectuar un ciclo backup/restore

Ejemplo:

MiBaseDatos.fdb fue creada con la versión 1.5 y ahora queremos usarla con la versión 2.5

Caso 1. No necesitamos de las nuevas características introducidas por la versión 2.5

Nos conectamos a la Base de Datos desde la versión 2.5 y podremos usarla sin problemas

Caso 2. Necesitamos algunas de las características introducidas por la versión 2.5 (que es el caso más común)

Aquí, debemos hacer un ciclo backup/restore para agregarle a la Base de Datos toda la funcionalidad de la versión 2.5

Primero, usando el Servidor del Firebird 1.5, en la ventana “Símbolo del sistema” del Windows escribimos:

SET ISC_USER=SYSDBA

SET ISC_PASSWORD=masterkey

GFIX -validate -full MiBaseDatos.fdb

GFIX -mend -full -ignore MiBaseDatos.fdb

GFIX -sweep MiBaseDatos.fdb

GBAK -backup -verbose -ignore -garbage MiBaseDatos.fdb MiBackup.fbk

Los dos SET (ISC_USER e ISC_PASSWORD) sirven para decirle al Windows que queremos usar esas variables y en consecuencia en los comandos GFIX y GBAK no necesitaremos estar introduciendo el usuario ni la contraseña. Es opcional, si prefieres escribir el usuario y la contraseña en cada comando GFIX y GBAK, entonces puedes obviar los dos SET

Si no usas un alias entonces debes escribir la ruta completa a tu Base de Datos, por ejemplo: D:\DATABASES\MiBaseDatos.fdb

Si usas un alias, entonces puedes escribir solamente el alias, por ejemplo: CONTA

El nombre del archivo de backup debe ser escrito con la ruta completa, por ejemplo: D:\BACKUPS\MiBackup.fbk

La opción -validate busca errores en la Base de Datos, los muestra y los repara

La opción -full se usa con la opción -validate para examinar todas las páginas y todos los registros y para liberar fragmentos de registro que no están asignados

La opción -mend marca a los registros corruptos para que al hacer el backup no sean copiados. De esta manera solamente los registros que están en buen estado son enviados al archivo de backup

La opción -ignore ignora los errores de checksum

La opción -sweep realiza el sweep inmediatamente

La opción -backup es opcional, sirve para aclarar (a nosotros, los humanos) que se quiere hacer un backup y no un restore

La opción -verbose muestra lo que está haciendo el programa GBAK

La opción -garbage no realiza la recolección de basura durante el backup y por lo tanto el backup terminará más rápido. Se la usa cuando tienes planeado hacer el restore o el sweep después del backup

 Ahora que nos aseguramos que nuestra Base de Datos está en buen estado y que hemos creado el archivo de backup pasamos al siguiente paso: restaurar el archivo de backup en una versión más reciente del Firebird

Para eso, usando la versión más reciente, escribimos:

GBAK -create -verbose -page_size 16384 MiBackp.fbk MiBaseDatos.fdb

La opción -create creará el archivo MiBaseDatos.fdb y éste no debe existir.

La opción -verbose muestra lo que está haciendo el programa GBAK

La opción -page_size cambia el tamaño de la página. Debes tener en cuenta que si aumentas el tamaño de la página (por ejemplo: la Base de Datos original tenía un tamaño de página de 4096 y se la restaura con un tamaño de página de 16384) aumentará también el tamaño de tu Base de Datos restaurada.

Al finalizar la restauración ya tendremos una nueva Base de Datos, completamente funcional y con todas las características de la versión más reciente del Firebird.

Artículo relacionado:

El índice del blog Firebird21

Borrando filas de detalle en Maestro/detalle

1 comentario

Si tenemos dos tablas: Maestro y Detalle, podemos determinar lo que ocurrirá cuando borramos (o intentamos borrar) una fila del Maestro según lo que hayamos especificado en la Foreign Key, tal como se explica en este artículo:

https://firebird21.wordpress.com/2013/05/26/entendiendo-a-las-foreign-keys/

Sin embargo, a veces necesitamos borrar las filas del Detalle cuando las filas del Maestro cumplen con alguna condición. Y como la Foreign Key no tiene la regla de CASCADE para borrado, no nos sirve borrar la fila del Maestro.

Por ejemplo, queremos borrar las filas de detalle cuando:

  • La fecha de la venta es 26/JUN/2013
  • El identificador del cliente es 12345
  • La moneda es dólares americanos

Para estos casos es muy útil el operador IN, como vemos a continuación:

DELETE FROM
   VENTASDET D
WHERE
   D.VEN_IDECAB IN (SELECT C.VTC_IDENTI FROM VENTASCAB C WHERE C.VTC_IDECLI = 12345)

VENTASDET es la tabla de detalles de las ventas

VENTASCAB es la tabla cabecera (o maestro) de las ventas

VEN_IDECAB es la columna de VENTASDET donde se guarda el identificador de la cabecera

VTC_IDENTI es el identificador de la cabecera

VTC_IDECLI es el identificador del cliente

Este comando DELETE borrará los detalles de todas las ventas que se le hicieron al cliente que tiene identificador 12345. Luego, si es necesario habría que escribir:

DELETE FROM VENTASCAB WHERE VTC_IDECLI = 12345

para borrar también las filas de cabecera de las ventas realizadas a ese cliente.

Desde luego que la forma más fácil y sencilla de conseguir esto es que la Foreign Key sea ON DELETE CASCADE para que al borrar una fila del maestro se borren todas las correspondientes filas de detalle. Pero si la Foreign Key no es ON DELETE CASCADE y no podemos cambiarla entonces aquí se mostró una posible solución al problema de borrar filas de detalles cuando las filas de cabecera cumplen con una condición.

Artículo relacionado:

El índice del blog Firebird21

Como impedir que los usuarios modifiquen el valor de una columna

1 comentario

A veces, los valores que una vez se ingresaron en una columna deben ser siempre los mismos, son estáticos, no deben cambiarse.

En general los códigos tienen esta particularidad, deben ser fijos. Una vez asignados, ya no deberían ser modificados.

Sin embargo, podríamos olvidarnos de deshabilitar esa posibilidad en nuestro programa, el que ejecutan los usuarios y que escribimos en un lenguaje de programación: Visual FoxPro, Visual Basic, Delphi, C, C++, Java, etc.

Afortunadamente con Firebird es muy fácil evitar que cambien el valor de una columna, como podemos ver a continuación:

SET TERM ^ ;

CREATE TRIGGER CATEG_ACTIVO_BU FOR CATEG_ACTIVO
   ACTIVE BEFORE UPDATE
   POSITION 1
AS
BEGIN

   NEW.CAT_CODIGO = OLD.CAT_CODIGO;

END^

SET TERM ; ^

Al tener un trigger BEFORE UPDATE (en castellano: antes de actualizar) que le asigne a una columna el mismo valor que ya tenía esa columna nos aseguramos que jamás pueda cambiarse su valor. No importa cuantas veces lo intenten, no conseguirán cambiarlo.

Esta es una buena medida de seguridad, para evitar que por equivocación o por ignorancia, cambien lo que no debería ser cambiado.

Artículo relacionado:

El índice del blog Firebird21

Older Entries