Registrando errores en una tabla de LOG

5 comentarios

Como los stored procedures y los triggers se ejecutan en el Servidor desde el Cliente no se puede monitorear lo que está ocurriendo dentro de ellos. Pero saber lo que ocurre dentro de ellos es muy importante cuando estamos buscando errores o problemas.

Entonces, una alternativa es crear una tabla de LOG, o sea, una tabla donde registraremos todo lo que nos interese saber mientras se está ejecutando un stored procedure o trigger.

La tabla de LOG puede tener una estructura similar a la siguiente:

LOG1

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

En esta tabla solamente usaremos el comando INSERT porque lo que nos interesa es saber lo que ocurre dentro de los stored procedures y los triggers entonces no tiene sentido usar UPDATE alguna vez.

A la tabla LOG le agregaremos dos triggers: uno para incrementar el valor del identificador y el otro para insertar dentro de la tabla los valores constantes.

CREATE TRIGGER BI_LOG_LOG_IDENTI FOR LOG
   ACTIVE BEFORE INSERT
   POSITION 0
AS
BEGIN
   IF (NEW.LOG_IDENTI IS NULL OR NEW.LOG_IDENTI = 0) THEN
      NEW.LOG_IDENTI = GEN_ID(LOG_LOG_IDENTI_GEN, 1);
END

Como puedes ver, el trigger superior incrementa el valor del identificador en uno.

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

Este trigger inserta dentro de las columnas LOG_USUARI, LOG_FECHOR y LOG_COMPUT sus correspondientes valores. Como esos valores son constantes no vale la pena escribirlos cada vez que insertamos una fila en la tabla de LOG, lo mejor es tener un trigger que se encargue de esa tarea.

Si no tuviéramos este trigger tendríamos que escribir:

INSERT INTO
   LOG
VALUES
   (0, CURRENT_USER, CURRENT_TIMESTAMP, 'MiTabla', (SELECT M.MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS M WHERE M.MON$ATTACHMENT_ID = CURRENT_CONNECTION), MiStop, MiValor);

Pero como tenemos un trigger podemos escribir:

INSERT INTO
   LOG
   (LOG_TABLAX, LOG_STOPXX, LOG_VALORX)
VALUES
   ('MiTabla', MiStop, MiValor);

El autor de este blog prefiere la segunda forma, pero sobre gustos…

Ahora que ya tenemos la tabla de LOG y sus triggers podemos dedicarnos a insertar sus filas en los lugares que los necesitemos. Algo muy importante a recordar es que las inserciones en la tabla de LOG deben hacerse siempre con IN AUTONOMOUS TRANSACTION.

¿Por qué?

Porque las transacciones autónomas son independientes de la transacción principal y siempre son “comiteadas” o confirmadas, sin importar si la transacción principal terminó con un COMMIT o con un ROLLBACK.

De esta manera nos aseguramos que en la tabla de LOG siempre se inserten las filas, porque eso es justamente lo que nos interesa, que tenga filas que nos permitan conocer lo que está pasando dentro de un stored procedure o de un trigger.

Ejemplo:

CREATE TRIGGER BIENES_BI FOR BIENES
   ACTIVE BEFORE INSERT
   POSITION 1
AS
   DECLARE VARIABLE lcCodigo D_NOMBRE15;
   DECLARE VARIABLE lnNumero D_CANTIDAD1;
   DECLARE VARIABLE lcInicia D_CHAR3;
BEGIN

/* Primero, se halla el último código de esta categoría */
   FOR SELECT
      BIE_CODIGO
   FROM
      BIENES
   WHERE
      BIE_IDECAT = NEW.BIE_IDECAT
   ORDER BY
      BIE_CODIGO
   INTO
      :lcCodigo
   DO BEGIN
   END

   /* Si no se encontró, el número será 1. Si se encontró, será el siguiente */
   IF (lcCodigo IS NULL) THEN
      lnNumero = 1;
   ELSE
      lnNumero = CAST(SUBSTRING(lcCodigo FROM 4 FOR 5) AS INTEGER) + 1;

IN AUTONOMOUS TRANSACTION DO BEGIN
   INSERT INTO LOG(LOG_TABLAX, LOG_STOPXX, LOG_VALORX) VALUES('BIENES', 1, :lcCodigo);
   INSERT INTO LOG(LOG_TABLAX, LOG_STOPXX, LOG_VALORX) VALUES('BIENES', 2, :lnNumero);
END

   /* Se obtienen los caracteres iniciales del código */
   lcInicia = (SELECT CAT_INICIA FROM CATEG_ACTIVO WHERE CAT_IDENTI = NEW.BIE_IDECAT);

IN AUTONOMOUS TRANSACTION DO BEGIN
   INSERT INTO LOG(LOG_TABLAX, LOG_STOPXX, LOG_VALORX) VALUES('BIENES', 3, :lcInicia);
END

   /* Se forma el código del bien que se grabará en la tabla */
   NEW.BIE_CODIGO = lcInicia || LPAD(lnNumero, 5, '0');

IN AUTONOMOUS TRANSACTION DO BEGIN
   INSERT INTO LOG(LOG_TABLAX, LOG_STOPXX, LOG_VALORX) VALUES('BIENES', 4, NEW.BIE_CODIGO);
END

END

En este trigger he puesto tres veces IN AUTONOMOUS TRANSACTION y dentro de ellos los INSERT a la tabla de LOG. En este caso se podría haber usado un solo IN AUTONOMOUS TRANSACTION al final y poner en él todos los INSERT, pero la forma en que está es para mostrar que se pueden escribir varios INSERT INTO LOG si así se desea o necesita.

Al insertar un nuevo bien a la tabla de BIENES se insertan 4 filas a la tabla de LOG, como podemos ver a continuación:

LOG2

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

Si la conexión hubiera sido por dirección de IP en la columna LOG_COMPUT se hubiera visto el número de IP de la computadora.

En la columna LOG_VALORX tenemos los valores de las variables en la primera inserción (LOG_STOPXX = 1), en la segunda inserción (LOG_STOPXX =2), etc.

Conclusión:

Tener una tabla de LOG para registrar en ella todo lo que ocurre dentro de los stored procedures y de los triggers es de una gran ayuda cuando necesitamos depurar código fuente. Hay que recordar que la inserción en la tabla de LOG siempre debe hacerse dentro de un IN AUTONOMOUS TRANSACTION para que esa inserción sea siempre “comiteada” (o sea: confirmada), sin importar como termine la transacción principal.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Actualización de saldos: cuando sí y cuando no

8 comentarios

Es muy común que los programadores quieran tener en una columna el saldo actualizado de ese ítem, pero … ¿es eso conveniente?

Supongamos que tenemos una tabla de PRODUCTOS con las columnas:

  • Identificador del producto
  • Código del producto
  • Nombre del producto
  • Cantidad inicial
  • Cantidad actual

Luego, cada vez que entra o sale un producto del stock se actualiza la columna “Cantidad actual” para así tener siempre a mano cual es la cantidad actual de dicho producto.

¿Es ésa una buena práctica?

No.

¿Por qué no?

Porque cada vez que una transacción actualiza una columna se bloquea la fila completa. La primera transacción que intente hacerlo no tendrá problemas pero las siguientes sí, porque la fila que quieren actualizar está bloqueada. Y en ese caso solamente tienen dos alternativas:

  1. Esperar que la transacción que tiene bloqueada a una fila la desbloquee
  2. Abandonar el intento de actualización con un mensaje de error

Y ninguna de esas alternativas es aceptable en un entorno donde hay mucha concurrencia (o sea, muchas computadoras queriendo actualizar el mismo ítem)

Ejemplo del problema que puede ocurrir:

Un supermercado tiene 25 cajas, es fin de año, época de muchas ventas. La caja 1 registra un producto, actualizando su cantidad en stock. Las cajas 7 y 18 quisieron actualizar también la cantidad en stock de ese producto y fueron rechazadas. La caja 1 liberó al producto, el cual ahora fue actualizado por la caja 7 pero la caja 18 continúa esperando. La caja 1 registra otro producto, y al hacerlo lo bloquea. Las cajas 12, 14, y 20 que querían también registrar ese producto no pueden hacerlo. La caja 3 bloqueó a otro producto, que la caja 5 también debe actualizar y por lo tanto no puede.

Puede ser un problema interminable porque cuando una caja libera a un producto otra caja lo bloquea.

Por lo tanto, querer tener actualizada la cantidad en stock de un producto que puede ser vendido desde muchas computadoras y al mismo tiempo, es un gran error.

A la gente no le gusta esperar, si tienen que estar esperando y esperando y esperando hasta que cada producto pueda ser registrado en la caja lo más probable es que se vayan y no vuelvan. Y si se van se pierden clientes, y si se pierden clientes se pierden ventas, y si se pierden ventas se pierde dinero. Y si pierden dinero, lo más probable es que los dueños de la empresa se deshagan rápidamente del programa inútil y del programador inútil, y contraten algo que realmente les sirva.

¿En qué casos sí se puede actualizar el saldo en línea?

Cuando la probabilidad de que el ítem sea accedido para actualización desde 2 ó más computadoras es cero o casi cero. Por ejemplo, en el momento en que se le vende a un cliente o se le cobra a un cliente sí es correcto actualizar su saldo. ¿Por qué? Porque es altamente improbable o imposible que al mismo cliente se le esté vendiendo o cobrando desde dos o más computadoras. Si el cliente está en la caja 5 pues está en la caja 5, no está también en la caja 12 y en la caja 18. Por lo tanto, es imposible que haya conflicto cuando se quiere actualizar su saldo.

Lo mismo sucedería con los proveedores. Es muy raro que un proveedor entregue varias facturas y esas facturas sean cargadas al mismo tiempo y en distintas computadoras; entonces actualizar su saldo cada vez que se carga una factura es correcto.

Algo similar sucede con los alumnos y las cobranzas de las cuotas. Es prácticamente imposible que al mismo alumno se le esté cobrando al mismo tiempo desde dos o más computadoras.

¿Cómo se obtiene el saldo actual si no se lo actualiza en línea?

Como vimos, en el caso de los productos de un supermercado sería un error querer tener actualizados los saldos de los productos en línea (o sea, en el mismo momento en que se venden). Pero los usuarios necesitan conocer ese saldo, ¿cómo lo solucionamos?

Hay dos alternativas:

  1. Si la velocidad con la cual se recuperan los datos de la consulta es alta, un simple SELECT será suficiente. A la cantidad inicial del producto (que tenemos guardada en la tabla PRODUCTOS) se le suman todas las entradas y se le restan todas las salidas, hallándose así la cantidad actual.
  2. Si realizar la consulta especificada en el punto 1. demora mucho, entonces se tiene una tabla de SALDOS que son actualizados periódicamente (una vez al día, una vez a la semana, una vez al mes, etc.) y luego a la cantidad que hay en esa tabla de SALDOS se le suman las entradas y se le restan las salidas. Por ejemplo, si la tabla de SALDOS se actualiza diariamente (por ejemplo: al final de la jornada laboral), para saber la cantidad actual de un producto se obtiene la cantidad que tenía ayer (ese dato está en la tabla de SALDOS) y se le suman todas las entradas y se le restan todas las salidas que ocurrieron el día de hoy.

Conclusión:

Cuando un ítem (por ejemplo: un producto) puede tener mucha concurrencia (o sea, que accedan a sus datos para modificarlos desde varias computadoras y al mismo tiempo) no se debe actualizar el saldo de ese ítem porque si se lo actualiza solamente acarreará problemas a los usuarios.

Cuando un ítem (por ejemplo: un proveedor) tiene baja concurrencia entonces sí es correcto actualizar su saldo.

La pregunta que debemos hacernos es: ¿es probable que al mismo tiempo se quiera actualizar el saldo de este ítem desde 2 ó más computadoras? Si la respuesta es afirmativa entonces no debemos actualizar el saldo.

Artículos relacionados:

Modos de bloqueo de las transacciones

Bloques mortales

El índice del blog Firebird21

SET AUTODDL ON

3 comentarios

Las siglas DDL significan: Data Definition Language o en castellano: Lenguaje para la Definición de Datos.

Entonces, al escribir el comando SET AUTODDL ON le estamos indicando al Firebird que queremos que realice un COMMIT automático a continuación de cada instrucción DDL.

¿Cuáles son las instrucciones DDL?

Todas las que empiezan con las palabras: CREATE, ALTER, DROP, DECLARE, RECREATE, SET

Sirven para crear tablas, índices, stored procedure, triggers, etc. También para realizar modificaciones, borrados, declaraciones.

Ejemplos: CREATE TABLE, CREATE INDEX, ALTER TABLE, DROP INDEX

¿Cuál es la ventaja de escribir SET AUTODDL ON?

Que no necesitaremos escribir un COMMIT a continuación de las instrucciones DDL

¿Dónde se usa SET DDL ON?

En el programa ISQL.EXE, en los scripts, y en algunos otros programas que implementan esta funcionalidad.

Ejemplo:

Queremos agregarle la tabla BANCOS a nuestra Base de Datos mediante un script:

/* Al poner AUTODDL en ON no necesitamos escribir los COMMIT */

SET AUTODDL ON;

/* Primero, creamos la tabla BANCOS, sin un COMMIT al final del CREATE */

CREATE TABLE BANCOS (
   BAN_IDENTI D_IDENTIFICADOR DEFAULT 0 NOT NULL,
   BAN_NOMBRE D_NOMBRE40 NOT NULL);

/* Después, creamos las restricciones Primary Key y Unique Key, sin COMMIT */

ALTER TABLE BANCOS ADD CONSTRAINT PK_BANCOS PRIMARY KEY (BAN_IDENTI);

ALTER TABLE BANCOS ADD CONSTRAINT UQ_BANCOS UNIQUE (BAN_NOMBRE);

/* A continuación creamos un trigger para tener un identificador que sea autoincremental */

SET TERM ^ ;

CREATE TRIGGER BI_BANCOS_BAN_IDENTI FOR BANCOS
   ACTIVE BEFORE INSERT
   POSITION 0
AS
BEGIN
   IF (NEW.BAN_IDENTI IS NULL OR NEW.BAN_IDENTI = 0) THEN
      NEW.BAN_IDENTI = GEN_ID(BANCOS_BAN_IDENTI_GEN, 1);
END^

SET TERM ; ^

Como puedes ver, ningún COMMIT ha sido escrito sin embargo la tabla BANCOS ha sido creada exitosamente, como también las dos restricciones y el trigger.

¿Por qué? Porque al estar AUTODDL en ON es el propio Firebird quien se encarga de poner el COMMIT luego de cada instrucción DDL.

Artículo relacionado:

El índice del blog Firebird21

Un truco para encontrar valores que pueden estar en varias columnas

1 comentario

En este artículo hemos visto como usar las columnas computadas:

https://firebird21.wordpress.com/2013/06/23/columnas-computadas/

Ahora, aprovechándonos de ellas mostraremos un truco muy útil cuando el valor buscado puede encontrarse en varias columnas.

Por ejemplo, en nuestra tabla de CLIENTES tenemos estas columnas:

CLI_TELPAR     /* Teléfono particular */
CLI_TELLAB     /* Teléfono laboral */
CLI_CELULA     /* Teléfono celular o móvil */

Y queremos buscar un número de teléfono pero éste puede ser particular, laboral, o celular, entonces podríamos tener esta columna computada:

CLI_TELEFO COMPUTED BY (CLI_TELPAR || ' ' || CLI_TELLAB || ' ' || CLI_CELULA)

Entonces, para buscar a un cliente del cual conocemos su número de teléfono escribiríamos:

SELECT
   CLI_NOMBRE
FROM
   CLIENTES
WHERE
   CLI_TELEFO LIKE '%123456%'

En donde evidentemente ‘123456’ es el número de teléfono que estamos buscando.

La contra de este truco es que no puede usar índices así que en tablas muy grandes no será práctico, pero en tablas no muy grandes no debería tardar más de 2 ó 3 segundos.

Artículos relacionados:

Columnas computadas

Utilizando columnas computadas

Algunos ejemplos de uso de las columnas computadas

Usando un SELECT en una columna computada

Indexando una columna computada

El índice del blog Firebird21

El foro del blog Firebird21

Columnas computadas

7 comentarios

Firebird además de las columnas normales nos permite tener columnas computadas.

¿Qué es una columna computada?

Una columna cuyo contenido depende de otras columnas, previamente definidas.

¿Para qué se usan las columnas computadas?

Para poder referenciar a los datos de varias formas, o sea que los datos pueden encontrarse en sus columnas originales o en las columnas computadas. También para escribir fórmulas que estén siempre accesibles.

¿Cómo se declara una columna computada?

Con la cláusula COMPUTED BY

Ejemplo:

Tenemos una tabla llamada ALUMNOS que tiene estas columnas (y varias más, pero que ahora no nos interesan):

CREATE TABLE ALUMNOS (
   ALU_IDENTI D_IDENTIFICADOR NOT NULL,
   ALU_NOMBRE D_NOMBRE25      NOT NULL,
   ALU_APELLD D_NOMBRE25      NOT NULL) ;

El dominio D_IDENTIFICADOR es un INTEGER y el dominio D_NOMBRE25 es un VARCHAR(25).

En la columna ALU_IDENTI se guarda el identificador del alumno, en ALU_NOMBRE sus nombres y en ALU_APELLD sus apellidos.

Pero en los informes queremos que aparezcan primero los apellidos, luego una coma, luego un espacio en blanco, y luego los nombres. Claro que podríamos conseguirlo realizando las concatenaciones correspondientes en cada caso pero lo más sencillo es tener una columna computada, como la siguiente:

ALU_APENOM COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE)

Entonces, la estructura de la tabla ALUMNOS quedaría así:

CREATE TABLE ALUMNOS (
   ALU_IDENTI D_IDENTIFICADOR NOT NULL,
   ALU_NOMBRE D_NOMBRE25      NOT NULL,
   ALU_APELLD D_NOMBRE25      NOT NULL,
   ALU_APENOM COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE)) ;

Si usas EMS SQL Manager, le dices que quieres agregar una columna a la tabla, como siempre lo haces, pero ahora eliges la opción “COMPUTED BY expression” y escribes la expresión que deseas:

COMPUTED3

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

 Si ahora escribimos un SELECT para ver los datos de los alumnos obtendremos algo similar a esto:

COMPUTED1Captura 2. Si haces clic en la imagen la verás más grande

Fíjate en la columna ALU_APENOM, tiene los mismos datos que las columnas ALU_NOMBRE y ALU_APELLD, pero concatenados de la forma que determinamos anteriormente.

¿Cómo se cambia el valor de una columna computada?

La única forma es cambiando los valores de sus columnas base (en este ejemplo, de ALU_NOMBRE y de ALU_APELLD), si intentas algo como esto:

UPDATE
   ALUMNOS
SET
   ALU_APENOM = 'PRUEBA DE CAMBIO DE NOMBRE'
WHERE
   ALU_IDENTI = 1200

El Firebird se enojará contigo y te mostrará el mensaje:

This column cannot be updated because it is derived from an SQL function or expression.Attempted update of read-only column.

O sea que la columna no puede ser actualizada porque es derivada de una función o expresión SQL. Intentaste actualizar una columna que es read-only (sólo lectura).

¿Dónde se usa una columna computada?

  • En el comando SELECT para ver sus valores, para poner una condición de filtro (cláusula WHERE) o para mostrar a los datos ordenados (cláusula ORDER BY).
  • En el comando UPDATE y en el comando DELETE, cuando se coloca una condición de filtro (cláusula WHERE)

No se puede insertar un valor, ni actualizar un valor, ya que las columnas computadas obtienen sus valores de columnas previamente definidas.

SELECT
   ALU_APENOM
FROM
   ALUMNOS

Sí se puede (desde Firebird 2.0) indexar a una columna computada, pero hay que hacerlo de esta forma:

CREATE INDEX IDX_ALUMNOS1 ON ALUMNOS COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE);

Ya que si se intenta así:

CREATE INDEX IDX_ALUMNOS1 ON ALUMNOS ALU_APENOM

Se obtendrá un mensaje de error similar al siguiente:

Unsuccessful metadata update.
Attempt to index COMPUTED BY column in INDEX IDX_ALUMNOS1.

SQL Code: -607
IB Error Number: 335544351

El mensaje dice que la actualización de los metadatos no tuvo éxito, porque se intentó indexar una columna computada.


SELECT
   ALU_APENOM
FROM
   ALUMNOS
ORDER BY
   ALU_APENOM

COMPUTED3

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

¿Se puede cambiar una columna computada?

Se puede cambiar el tipo de datos y la expresión. No se puede cambiar una columna base en una columna computada o viceversa.

¿Las columnas computadas se usan solamente para concatenar columnas alfanuméricas?

No, también pueden usarse con columnas numéricas. Supongamos que tenemos una tabla de PRODUCTOS y deseamos saber cual es nuestro porcentaje de ganancia sobre el precio de costo, entonces podríamos escribir algo como esto:

PRD_PORCEN COMPUTED BY (PRD_PREVTA * 100 / PRD_PRECTO)

Donde PRD_PREVTA es el precio de venta del producto y PRD_PRECTO es el precio de costo del producto. En la columna PRD_PORCEN siempre tendremos los porcentajes de ganancia.

Desde luego que podríamos escribir cualquier otra fórmula matemática, no solamente esa.

Conclusión:

Las columnas computadas son muy útiles para que escribamos menos y podamos visualizar los datos en varias formas distintas, además las podemos usar con las cláusulas WHERE y ORDER BY.

No se puede indexar a una columna computada, pero sí se puede indexar las columnas que la componen.

Artículos relacionados:

Utilizando columnas computadas

Algunos ejemplos de uso de las columnas computadas

Usando un SELECT en una columna computada

Indexando una columna computada

El índice del blog Firebird21

El foro del blog Firebird21

Optimizando las consultas

2 comentarios

Este artículo está basado en el documento “Planos de Optimizaçao do Firebird” de Gladiston Santana.

Seguramente ya has leído o escuchado que el Firebird es rapidísimo para devolver el resultado de las consultas. ¿A qué se debe esa gran velocidad?

Quienes están acostumbrados a usar el modelo desktop (tablas .DBF, Paradox, Access) saben que el rendimiento está directamente ligado al rendimiento del Servidor de archivos, rendimiento de la red, tamaño de las tablas y el uso de índices correctos. Todo eso también es cierto en Firebird pero éste dispone de algo más, muy poderoso: el PLAN de optimización (“PLAN optimizer” en inglés).

¿Qué es el PLAN de optimización?

Es la lista del índice (o ningún índice) de cada tabla involucrada en una consulta que el Firebird utilizará para devolver el resultado de esa consulta (o sea, de ese SELECT)

La intención al usar un PLAN es que los resultados sean devueltos lo más rápidamente posible. Todas las consultas, absolutamente todas, tienen un PLAN de optimización, el cual puede ser puesto:

  • Automáticamente, por el Firebird
  • Manualmente, por el programador

¿Cómo el Firebird determina el PLAN?

Cuando el programador no le dice cual PLAN usar, el Firebird crea su propio PLAN usando para ello un módulo llamado “Query optimizer”, en castellano: optimizador de la consulta.

La tarea de este optimizador es analizar la consulta, evaluando: índices, combinaciones de índices, agrupamientos como sort, union, (inner, left, outer) join, y muchas cosas más y después de haber analizado todo eso evaluar el costo de esa consulta.

Este “costo” es una nota, una calificación, que indica si la optimización de la consulta es ventajosa o desventajosa. Si el optimizador encuentra que la nota es desventajosa entonces puede realizar una optimización distinta en la consulta o decidir no usar optimización. A este último caso se le llama NATURAL PLAN (o sea, un PLAN que no usa índices).

¿En qué casos se usaría el NATURAL PLAN?

Podría parecer extraña la idea de no utilizar optimización pero en algunos casos la mejor alternativa es no usar optimización ya que si se debe re-evaluar una operación o seleccionar un índice, eso a veces toma más tiempo que recorrer la tabla entera. Y en este caso no vale la pena usar el optimizador.

Este es el caso de tablas pequeñas o de SELECTs que no precisan de índices para realizar una búsqueda. También puede ocurrir que una tabla tenga una columna indexada pero que el optimizador elija no utilizar ese índice.

Ejemplo 1:

Tenemos una tabla llamada PERSONAS, la cual tiene una columna llamada PER_APELLD (apellidos de las personas) y un índice llamado IDX_PERSONAS sobre esa columna:

CREATE INDEX IDX_PERSONAS ON PERSONAS(PER_APELLD);

Ahora escribimos esta consulta, porque deseamos obtener los datos de todas las personas cuyo apellido sea ‘TORRES’ o cuyo apellido sea ‘CABRAL’:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE '%TORRES%CABRAL%'

El Firebird usará el índice IDX_PERSONAS, ¿verdad?. No, falso. No usará el índice IDX_PERSONAS como podemos ver al revisar el PLAN utilizado.

PLAN1

(haciendo clic en la imagen la verás más grande)

¿Por qué no usó el índice? Porque hay un “%” en el inicio del LIKE y eso implica que debe recorrer la tabla completa para mostrar el resultado de la consulta. Y no tiene sentido usar un índice en ese caso, ya que tomará más tiempo y no se obtendrá algún beneficio.

Inclusive, llamar al optimizador ya sería un desperdicio porque ningún índice ayudaría a acelerar esta consulta, entonces ni siquiera llamará al optimizador.

Para este caso de consultas que no pueden ser optimizadas es que existe el PLAN NATURAL.

Ejemplo 2:

Usando la misma tabla del ejemplo 1 escribimos esta consulta:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE 'TORRES%CABRAL%'

Fíjate que se borró el “%” que estaba al principio del LIKE. Volvemos a revisar cual es el PLAN utilizado y nos muestra:

PLAN2

(haciendo clic en la imagen la verás más grande)

 Y ahora sí está usando el índice porque es adecuado usarlo.

El SELECT extendido

Los SELECTs que escribimos en los dos ejemplos anteriores no son los que realmente utilizará el Firebird cuando los ejecutemos. ¿Por qué no? porque el Firebird automáticamente le agrega la cláusula PLAN a todos los SELECTs que no tengan dicha cláusula escrita.

Así, para el primer ejemplo, el SELECT que usará el Firebird es:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE '%TORRES%CABRAL%'
PLAN
   (PERSONAS NATURAL)

Fíjate que le agrega las palabras PLAN y luego el PLAN utilizado (en este caso PERSONAS NATURAL). PERSONAS es el nombre de la tabla y NATURAL le indica que no debe usar un índice.

Para el segundo ejemplo el SELECT que usará el Firebird es:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE 'TORRES%CABRAL%'
PLAN
   (PERSONAS INDEX (IDX_PERSONAS))

Fíjate que ahora sí usa el índice IDX_PERSONAS

Preparando un PLAN

El proceso de someter una consulta al optimizador para que éste la analice y determine el mejor PLAN se llama “Prepare query” (preparar la consulta). Los programadores experimentados siempre escriben un PLAN en el SELECT porque eso ahorra tiempo y mejora el rendimiento ya que el Firebird usa el PLAN especificado y no debe llamar al optimizador para determinar cual PLAN utilizará.

En EMS SQL Manager, haríamos clic en la opción “Prepare query” para preparar la consulta  y en “Explain query” para ver cual será el PLAN que utilizará el Firebird, como se ve a continuación:

PLAN3

(haciendo clic en la imagen la verás más grande)

 Entonces, como ya se le indica cual PLAN debe usar el Firebird no pierde tiempo analizando la consulta y determinando ese PLAN.

Advertencia

Si al escribir un SELECT especificamos el PLAN a utilizar y ese PLAN es el más adecuado entonces el SELECT se ejecutará más rápido, sin embargo hay que tener en cuenta algo muy importante: a veces, el PLAN que hoy es excelente puede dejar de serlo conforme las condiciones de la Base de Datos van cambiando, por ejemplo si se le van agregando nuevos índices, fórmulas, e inclusive el mismo aumento en la cantidad de datos. Esto implica que periódicamente debemos verificar el rendimiento de nuestros SELECTs para detectar aquellos planes que ya no son los más adecuados.

Un PLAN inadecuado no es solamente malo para esa consulta en particular, es malo para toda la Base de Datos porque consultas concurrentes podrían sumarse al problema y tornar al Servidor en una verdadera tortuga de tres patas.

Tiempos de ejecución

Veamos ahora cuanto tarda ejecutar cada uno de los SELECTs de los ejemplos anteriores:

PLAN4

(haciendo clic en la imagen la verás más grande)

 La tabla PERSONAS tiene 3.516.272 registros y ejecutar el primer SELECT (el que tiene PLAN NATURAL y por lo tanto no usa índices) tardó 14,093 segundos.

PLAN5

(haciendo clic en la imagen la verás más grande)

 Al ejecutar el segundo SELECT (que usa al índice IDX_PERSONAS) el tiempo se redujo considerablemente, ahora es de solamente 1,282 segundos. ¿Y qué ocurrirá si quitamos el apellido CABRAL de la consulta y dejamos solamente al apellido TORRES? ¿El tiempo mejorará o empeorará? veamos:

PLAN6

(haciendo clic en la imagen la verás más grande)

 Esto puede parecer muy extraño, ya que ahora tenemos muchos más datos (ya que hay más apellidos ‘TORRES’ que ‘TORRES CABRAL’) sin embargo el tiempo de la consulta disminuyó.

¿Por qué?

Porque el Firebird recupera los datos por páginas y en una página generalmente caben muchos registros. Esos registros ya están en la memoria y por lo tanto mostrarlos es muy rápido.

Veamos ahora lo que sucede al volver a ejecutar el último SELECT

PLAN7

(haciendo clic en la imagen la verás más grande)

 El tiempo se redujo aún más, ahora ni siquiera tardó 1 segundo en finalizar.

¿Por qué?

Porque esa consulta ya estaba en la memoria, en el caché del Firebird, y por lo tanto no tuvo que ser leída desde el disco duro, con lo cual se aceleró aún más obtener los resultados pedidos.

Lo bueno de esto es que si el mismo usuario ejecuta el mismo SELECT o si algún otro usuario lo hace, como los datos ya están en la memoria mostrarlos es súper rápido.

Estos tiempos de ejecución fueron obtenidos con una tabla que tiene 3.516.272 registros y una computadora que ya tiene varios años: Pentium IV con 4 Gbytes de RAM y de un solo núcleo. En cualquier computadora nueva los tiempos serán mucho menores.

En Firebird si multiplicamos por 10 la cantidad de registros de una tabla no aumenta por 10 el tiempo de consulta. Si la tabla PERSONAS tuviera 35.162.720 registros el tiempo de ejecución de la consulta aún sería de alrededor de 1 segundo.

En cambio, en las tablas desktop (.DBF, Paradox, Access) si multiplicamos por 10 la cantidad de registros el tiempo de consulta se multiplica por 10, por 12, por 15.

Conclusión:

Entender lo que es el PLAN y aprender a usar el PLAN correcto hará que tus consultas sean rapidísimas. Sin embargo debes recordar que a veces el PLAN va decayendo en su rendimiento cuando le vas agregando índices o fórmulas a las tablas o mismo por el aumento de los datos así que es buena práctica revisar los planes periódicamente para asegurar que siguen siendo los más adecuados.

La gran diferencia en el rendimiento que se observa cuando se usa Firebird en comparación con tablas .DBF, Paradox y Access es que Firebird, al igual que Oracle, Sybase, MSSQL, usa un PLAN. Pero mientras que por usar esos SGBD debes pagar mucho dinero, por usar Firebird no pagas, es gratis.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN

El índice del blog Firebird21

Transacciones optimistas y transacciones pesimistas

2 comentarios

Este artículo está basado en el documento: “Transaçoes em Firebird” de Eugénio Reis.

Conceptos generales

  1. Normalmente las únicas operaciones que pueden bloquear un registro son las operaciones de escritura. En Firebird existe una excepción a esa regla: un SELECT crea un bloqueo conocido como shared lock (bloqueo compartido) el cual permite las lecturas y las escrituras al registro pero impide que la tabla sea borrada con un DROP. Una tabla puede ser borrada con el comando DROP solamente si ninguno de sus registros tiene un shared block, o sea si nadie está haciendo un SELECT a dicha tabla.
  2. La gran diferencia entre el tratamiento optimista y el tratamiento pesimista es la cantidad de tiempo durante el cual el registro queda bloqueado. En el optimista el tiempo es menor.
  3. Las alteraciones son registradas en un área aparte llamada log de transacciones
  4. El bloqueo pesimista tiende a saturar el uso del log porque fuerza a la Base de Datos a mantener las transacciones abiertas por mucho tiempo
  5. En el Firebird no se pueden tener lecturas sucias. Una lectura es sucia cuando la transacción T2 puede ver las alteraciones que está realizando la transacción T1 antes de que la transacción T1 realice el COMMIT correspondiente.
  6. Un registro jamás puede ser alterado al mismo tiempo por dos transacciones concurrentes

¿Es mejor el tratamiento optimista o el tratamiento pesimista?

En general, es preferible el optimista porque el pesimista tiende a arruinar el rendimiento de la Base de Datos, a crear situaciones de deadlock (bloqueos mortales) con más frecuencia, a generar filas de espera por la disponibilidad de los datos y a aumentar la competición entre los usuarios.

Cuanto mayor sea la cantidad de usuarios mayores serán los trastornos que el tratamiento pesimista causará porque a mayor cantidad de bloqueos, mayor cantidad de problemas.

Estudio de caso 1: control del stock

Si un cliente llega a la caja con el producto en la mano, no debe ser validado para saber si hay existencia de ese producto. La prueba de que hay está en las manos del cliente. El cajero inclusive debe ser capaz de realizar la venta aunque la Base de Datos esté off-line y más adelante cuando esté on-line se corre un proceso de actualización.

Es también común hacer un recuento físico del stock cada seis meses o un año para evitar que las discrepancias inevitables causadas por factores como hurto, pérdida, deterioro, etc., se vuelvan muy grandes.

Si un cliente llama por teléfono para preguntar si hay disponibilidad de un producto porque quiere comprarlo el vendedor no debería bloquear el registro sino hacer una reserva del mismo. Y aunque en la computadora él vea que hay existencia de ese producto podría ser falso porque hubo hurto.

Estudio de caso 2: cambio en la ficha del cliente

En general, sería muy raro que los datos de un cliente deban ser modificados por dos usuarios al mismo tiempo. Un caso posible es cuando el cliente utiliza una tarjeta de crédito y el usuario legítimo y quien le robó los datos de su tarjeta de crédito están realizando compras y pagando con esa tarjeta. Para estos casos una medida de seguridad es que entre un pago con tarjeta y el siguiente transcurra cierto tiempo. Claro que quien pagó primero será el aceptado.

Estudio de caso 3: reservas

Ocurre cuando el cliente desea reservar algo: un asiento en un avión o en un ómnibus, un auto que desea alquilar, una habitación en un hotel, una mesa en un restaurante, una entrada para un concierto, un producto específico, etc.

El producto específico puede ser el más ilustrativo porque posiblemente no existan dos productos exactamente iguales (“quiero comprar el cuadro titulado ‘Las princesas’ del pintor Joaquín Velázquez”. En este caso es evidente que el vendedor tiene un solo cuadro para vender)

El problema es que si se está vendiendo por Internet dos o más personas pueden estar queriendo comprar el mismo cuadro al mismo tiempo y quieren colocarlo en su “carrito de compras”.

Si la primera persona lo colocó en su carrito de compras ¿la segunda persona no puede hacerlo? Supongamos que se le da a la primera persona un tiempo prudencial para decidirse, ya que podría desistir de la compra, de 20 minutos. ¿Le hacemos esperar 20 minutos a la segunda persona para avisarle que el producto está disponible? Lo más probable es que la segunda persona desista y si la primera persona también lo hizo, entonces perdimos la venta.

Un tratamiento pesimista nos haría perder ventas (como en el ejemplo anterior) y también afectaría muy mal nuestra reputación como vendedores.

Estudio de caso 4: numeración secuencial

En los tres casos anteriores lo correcto es el tratamiento optimista, ahora veremos un caso en que el adecuado es el tratamiento pesimista.

Las situaciones en las cuales se necesitan números secuenciales que no pueden tener huecos entre ellos requieren de un tratamiento pesimista.

Ejemplos típicos son: numeración de las matrículas de los alumnos, números de patentes de los vehículos, Facturas de venta, numeración serial de productos, etc.

Esto se puede hacer de dos maneras:

  1. Recorriendo la tabla hasta encontrar el último número utilizado
  2. Teniendo una tabla auxiliar

Lo correcto, por velocidad y confiabilidad, es usar el método 2.

Entonces, nuestra tabla auxiliar tendría tres columnas:

  • AUX_IDENTI, identificador de la fila (generalmente SMALLINT es más que suficiente)
  • AUX_CODIGO, código de la secuencia (por ejemplo: “MAT”, “CUO”, “ASU”, “001-001-“)
  • AUX_ULTNUM, último número usado (normalmente SMALLINT aunque a veces se necesitaría INTEGER)

En líneas generales el algoritmo es el siguiente:

  1. Se abre la transacción
  2. UPDATE AUXILIAR SET AUX_ULTNUM = AUX_ULTNUM + 1 WHERE AUX_CODIGO = ‘MAT’. Este UPDATE bloqueará el registro inmediatamente y cualquier otro programa en la red que desee bloquear este mismo registro tendrá que esperar
  3. lnUltimoNumero = (SELECT AUX_ULTNUM FROM AUXILIAR WHERE AUX_CODIGO = ‘MAT’). Este SELECT es necesario para conocer cual es el valor actual de la columna AUX_ULTNUM
  4. Se graba en el registro y la columna adecuados de la tabla principal el valor de lnUltimoNumero
  5. Se realiza el COMMIT. Si tiene éxito, el valor de lnUltimoNumero estará grabado en la tabla auxiliar y en la tabla principal. Si falla, se hace un ROLLBACK y quedarán con sus valores anteriores. En ambos casos la transacción fue cerrada y todos los bloqueos liberados. Los demás procesos pueden generar nuevos números secuenciales a partir de ese momento.

Lo más crítico en este proceso es el tiempo durante el cual el registro de la tabla auxiliar queda bloqueado. Recuerda que si ese tiempo se prolonga causará trastornos a los demás usuarios. Los objetivos del tratamiento pesimista son conseguir seguridad y consistencia, pero debe conseguirse en transacciones lo más cortas posibles para no afectar negativamente a los demás usuarios.

Un punto importante cuando el tratamiento es pesimista es el orden en el cual se ejecutan el UPDATE y el SELECT. Deben realizarse siempre en el mismo orden: o siempre primero el UPDATE o siempre primero el SELECT. No importa cual de ellos se realice primero sino que en todos los procesos se mantenga el mismo orden. De no hacerlo así podrían ocurrir muchos deadlocks (bloqueos mortales) en la Base de Datos.

Conclusión:

En general deberíamos usar transacciones con tratamiento optimista porque eso reduce el tiempo en el cual los registros quedan bloqueados. Si un registro está bloqueado le causa trastornos a los demás usuarios que también quieren modificarlo. El tratamiento pesimista debe usarse cuando no hay alternativa, por ejemplo cuando se requieren números secuenciales que no tengan huecos (números faltantes) entre ellos. Y en ambos casos lo correcto es que las transacciones sean lo más cortas posibles.

Artículos relacionados:

Modos de bloqueo de las transacciones

Bloqueos mortales

Algo más sobre transacciones optimistas y transacciones pesimistas

¿Por qué en Firebird es preferible que las transacciones sean optimistas?

El índice del blog Firebird21

 

Older Entries Newer Entries