Usando un PLAN

16 comentarios

Cuando va a ejecutar un SELECT el Firebird automáticamente busca el mejor plan para que esa consulta sea lo más rápida posible. El Firebird posee lo que se denomina un “optimizador de consultas”. La tarea de dicho optimizador es conseguir la consulta más rápida de todas.

¿Qué es un plan?

Es la lista de todos los índices u ordenamientos que usará una consulta. También puede darse el caso de que lo mejor sea no usar índices ni ordenar la consulta.

¿El Firebird siempre usa un plan?

Sí, siempre. Sea cual sea el SELECT que escribamos siempre tendrá un plan: el que le asignó automáticamente el Firebird o el que le especificamos manualmente nosotros.

¿Cómo se especifica un plan?

Dentro del comando SELECT existe una clásula llamada PLAN y es allí donde escribimos el plan que queremos usar

¿Por qué no usar siempre el plan que automáticamente asigna el Firebird?

Porque aunque en la gran mayoría de los casos esa es la mejor alternativa hay veces en que la elección del Firebird no es la más adecuada. Si sabemos los que es un plan y como funciona podemos darnos cuenta de que se podría mejorar el rendimiento usando un plan distinto. Afortunadamente podemos hacer eso.

¿En qué casos el Firebird puede elegir un mal plan?

En general cuando las estadísticas están desactualizadas y tenemos algún índice con baja selectividad (es decir: pocos valores distintos). El optimizador usa la selectividad en su algoritmo para decidir si debe usar un índice o no. Si la selectividad está desactualizada y no representa fielmente al índice entonces el optimizador puede usar un índice que no debería de usar o dejar de usar un índice que sí debería usar.

 Esta es la principal razón por la cual siempre debemos tener las estadísticas de nuestros índices actualizadas: para que el optimizador del Firebird utilice correctamente a los índices en cada SELECT.

¿Cómo le indico que debe recalcular la estadística de un índice para hallar su selectividad actual?

Con el comando:

SET STATISTICS NombreIndice;

¿Por qué debo pedirle que reconstruya las estadísticas?

Porque el Firebird solamente calcula las estadísticas cuando un índice es creado y después de un ciclo backup/restore. Eso implica que si no le pides que las reconstruya pueden estar desactualizadas.

¿Y cómo reconstruyo un índice?

Escribiendo:

ALTER INDEX NombreIndice INACTIVE;

ALTER INDEX NombreIndice ACTIVE;

¿Y por qué debo pedirle que reconstruya un índice?

Porque después de realizar muchas inserciones, borrados, o modificaciones que afectan a un índice el índice suele quedar desbalanceado y tú deberías asegurarte que está bien balanceado. Un índice desbalanceado es recorrido más lentamente. Escribiendo los comandos de arriba conseguirás balancearlo y por lo tanto mejorar la velocidad de consulta.

¿Cómo puedo saber cuál plan está usando el Firebird en cada consulta?

En ISQL, escribes:

SET PLAN;

Y luego, cada vez que escribas el comando SELECT lo primero que verás será el plan utilizado

PLAN4

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

En EMS SQL Manager, haces click en: Show SQL Editor (o presionas la tecla F12); o también puedes hacer click en: New SQL Editor (o presionas las teclas Shift + F12)

PLAN1

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

Verás una ventana donde podrás escribir tus comandos, luego de escribir tu SELECT en esa ventana haces click donde dice “Explain query” (explicar la consulta, en castellano)

PLAN2

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

Y en la parte inferior verás algo similar a esto:

PLAN3

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

¿Qúe significa la palabra “NATURAL” en un plan?

Que no se está usando ningún índice

¿Por qué a veces me dice que no se puede utilizar el plan especificado?

Porque en tu cláusula WHERE o en tu cláusula ORDER BY no estás teniendo todas las columnas que se usan en el índice

Ejemplo 1:

En la tabla CLIENTES tenemos una Primary Key compuesta por dos columnas: CLI_CODSUC (significa: código de la sucursal) y CLI_IDENTI (significa: identificador del cliente)

SELECT
   *
FROM
   CLIENTES
WHERE
   CLI_IDENTI > 0
PLAN
   (CLIENTES INDEX (PK_CLIENTES))

Al intentar ejecutar ese SELECT muestra el mensaje: “Index PK_CLIENTES cannot be used in the specified plan.” ¿Por qué muestra ese mensaje? Porque el índice PK_CLIENTES es un índice compuesto por dos columnas: CLI_CODSUC y CLI_IDENTI y arriba usamos la segunda columna solamente, al cambiar el SELECT anterior por este:

Ejemplo 2:

SELECT
   *
FROM
   CLIENTES
WHERE
   CLI_CODSUC = 0 AND
   CLI_IDENTI > 0
PLAN
   (CLIENTES INDEX (PK_CLIENTES))

todo funciona perfectamente porque en el WHERE se escribieron las dos columnas que componen al índice PK_CLIENTES. El Ejemplo 1. también habría funcionado perfectamente si en lugar de escribir: CLI_IDENTI > 0 hubiéramos escrito CLI_CODSUC = 0 ó también CLI_CODSUC > 0 ó cualquier otra combinación que involucre a la primera columna del índice.

¿Un plan puede involucrar a varias tablas?

Sí, por cada tabla que haya en tu SELECT en el plan se especificará cual índice se usará.

Ejemplo 3:

PLAN JOIN (V_ABM_VENTAS_CONTA V NATURAL,
           V_ABM_VENTAS_CONTA C INDEX (PK_CLIENTES),
           V_ABM_VENTAS_CONTA S INDEX (UQ_SUCURSALES))

En este caso vemos que la vista V_ABM_VENTAS_CONTA tiene:

  • Un orden NATURAL (o sea que no se usará ningún ordenamiento específico)
  • Un orden PK_CLIENTES (o sea que se usará la Primary Key de la tabla CLIENTES)
  • Un orden UQ_SUCURSALES (o sea que se usará la Unique Key de la tabla SUCURSALES)

Como las tres tablas de esa vista tienen alias, en lugar de ver los nombres de las tablas verás sus alias (en este caso V para la tabla principal (VENTAS), C para una de las tablas usadas en el JOIN (que se llama CLIENTES) y S para la otra tabla usada en el JOIN (que se llama SUCURSALES)

¿Puedo tener un ORDER en un plan si no tengo la cláusula ORDER BY en el SELECT?

No, porque eso no tiene sentido; el plan solamente usa las columnas que se especifican en el SELECT.

Ejemplo 4:

La tabla ASIENTOSCAB tiene un índice llamado ASC01. Ese índice está compuesto por las columnas: ASC_ANOEJE, ASC_CODSUC, ASC_NUMERO (año del ejercicio contable, código de la sucursal, número del asiento)

Este SELECT nos mostrará el error de que el índice no puede ser usado en el plan especificado:

SELECT
   *
FROM
   ASIENTOSCAB
PLAN
   (ASIENTOSCAB INDEX (ASC01))

¿Por qué el error? porque el SELECT no tiene una cláusula WHERE ni una cláusula ORDER BY. En cambio este SELECT sí funcionará:

SELECT
   *
FROM
   ASIENTOSCAB
WHERE
   ASC_ANOEJE = 2013
PLAN
   (ASIENTOSCAB INDEX (ASC01))

El SELECT de arriba funciona porque en la cláusula WHERE se especificó la primera columna del índice ASC01. El SELECT de abajo también funcionará:

SELECT
   *
FROM
   ASIENTOSCAB
PLAN
   (ASIENTOSCAB INDEX (ASC01))
ORDER BY
   ASC_ANOEJE

Este SELECT funciona porque en la cláusula ORDER BY se especificó la primera columna del índice ASC01.

¿Cuándo se debería especificar un PLAN manualmente?

Cuando la consulta es lenta. Cualquier consulta que tarde más de 5 segundos en mostrar sus resultados amerita que se averigüe el motivo y uno de esos motivos puede ser que está usando un mal plan.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Trazabilidad en el tiempo

1 comentario

Cuando diseñas una Base de Datos generalmente tienes muchas formas de hacerlo, dicen que si a 100 personas les pides que diseñen una Base de Datos te encontrarás con más de 100 resultados (porque algunos te entregarán más de una).

Sin embargo, hay algunos consejos que pueden ser relevantes. Más allá de lo que seguramente ya conoces (normalización de las tablas, claves primarias subrogadas, índices en las columnas que se consultan frecuentemente, triggers en todas las tablas para evitar datos incorrectos, etc.) hay un punto que no todos conocen o toman en cuenta y que puede ser de vital importancia: trazabilidad en el tiempo.

¿Qué significa trazabilidad en el tiempo?

Que puedas retrotraer, que puedas regresar, tu Base de Datos a cualquier instante anterior.

¿Y para qué necesitaría retrotraer la Base de Datos a un instante anterior?

Para saber exactamente lo que tenías registrado en un determinado momento. Eso te permitirá responder preguntas tales como:

    • ¿Cuál era la cantidad en stock del producto XYZ el día 22 de abril de 2013 a las 09:15?
    • ¿Cuál era el saldo en cuenta corriente el día 23 de abril de 2013 a las 07:00?
    • ¿Cuánto se había vendido en la sucursal de Londres el día 24 de abril de 2013 a las 11:00 horas de acá?
    • ¿Cuáles fueron los cambios de precios de venta del producto XYZ el año pasado?
    • ¿Cuál era el precio de venta del producto XYZ antes de que el empleado Juan Pérez lo cambiara “por accidente”?
    • ¿Cuántos clientes tenía la sucursal de Nueva York el día 19 de abril de 2013, cuánto se les había vendido a esos clientes, cuánto se les había cobrado?

¿Cómo se consigue la trazabilidad en el tiempo?

  1. Nunca, jamás, una fila (registro) puede ser borrada. Lo que se hace es cambiar el valor de una columna de ACTIVO a INACTIVO
  2. Cada vez que: a) un fila es insertada o b) una fila es puesta en INACTIVO o c) una fila es modificada, se guardan en una tabla de log:
    • El identificador de la Sucursal
    • El nombre o identificador de la computadora
    • El nombre del usuario que insertó, inactivó o modificó
    • El nombre de la tabla
    • La fecha en la cual ocurrió la inserción, inactivación o modificación
    • La hora en la cual ocurrió la inserción, inactivación o modificación
    • El número de la transacción que insertó, inactivó o modificó
    • El valor anterior que tenía cada columna que fue modificada
  3. El punto 2. se realiza a través de un trigger AFTER INSERT OR UPDATE OR DELETE

¿Vale la pena que una Base de Datos tenga trazabilidad en el tiempo?

Sí, porque los usuarios muchas veces necesitan información de lo que ocurrió en el pasado, no solamente de lo que está ocurriendo ahora.

¿Cuál es la desventaja de tener una Base de Datos trazable en el tiempo?

Que puede llegar a ser muy grande, por eso suele ser recomendable que para ello utilices una Base de Datos auxiliar.