Usando índices correctos para aumentar la velocidad de las consultas

4 comentarios

Cada vez que escribimos un SELECT o una vista tenemos dos opciones:

  1. Que esté optimizado
  2. Que no esté optimizado

Que esté optimizado significa que se ejecuta con la mayor velocidad posible. Leyendo solamente el SELECT que escribimos muchas veces puede ser difícil comprobar si está optimizado o no, sobre todo cuando dicho SELECT es muy largo y hay varias tablas involucradas en él.

Afortunadamente disponemos de programas gráficos que nos facilitan muchísimo esa tarea. En este artículo se usa el programa EMS SQL Manager (tiene una versión Lite que es gratis). Veamos un ejemplo:

Tenemos una tabla llamada SUCURSALES que tiene estas filas:

CONSULTAS1

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

Esta tabla tiene un índice llamado UQ_SUCURSALES que está compuesto por la columna SUC_CODIGO

 Tenemos una tabla llamada BANCOS que tiene estas filas:

CONSULTAS1

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

Esta tabla tiene un índice llamado PK_BANCOS que está compuesto por estas dos columnas: BAN_CODSUC y BAN_IDENTI

Escribimos un SELECT y luego verificamos si está optimizado o no:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE AS BAN_NOMSUC,
   B.BAN_IDENTI,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO

Hacemos click en “Explain query” y vemos esto:

CONSULTAS4

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

que es el PLAN utilizado por el Firebird y que nos indica que la tabla B (o sea BANCOS) no está usando un índice, por eso aparece la palabra “NATURAL” a su derecha. La tabla S (o sea SUCURSALES) sí está usando un índice y ese índice se llama UQ_SUCURSALES.

También podemos verlo de forma gráfica haciendo click en “Performance Analysis”

CONSULTAS3

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

 La tabla de SUCURSALES sí está usando un índice, pero aunque esa tabla tiene solamente 4 filas fueron procesadas 16 filas. Eso está mal y debe ser mejorado.

A su vez, la tabla BANCOS no está usando un índice. Si lo que queremos es obtener todas las filas sin un orden específico esto está bien, es lo correcto. ¿Por qué? porque el orden NATURAL se procesa más rápido que cualquier índice. Sin embargo, si queremos que las filas sean mostradas ordenadas según algún otro criterio, no usar índice está mal y debe ser mejorado.

Rápidamente podemos saber si una tabla está usando un índice o no mirando el color del cilindro: un color azul significa que sí usa un índice y un color rojo granate significa que no usa un índice.

¿Por qué la tabla de BANCOS no está usando un índice?

Porque nunca se le pidió al Firebird que usara un índice en esa tabla

¿Cómo se le pide al Firebird que use un índice en un SELECT?

Mediante las cláusulas WHERE, JOIN, ORDER BY

Veamos ahora lo que sucede cuando escribimos este otro SELECT:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE AS BAN_NOMSUC,
   B.BAN_IDENTI,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO
WHERE
   B.BAN_CODSUC >= 0

Hacemos click en “Explain query” y vemos esto:
CONSULTAS5

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

O sea que ahora sí la tabla de BANCOS está usando un índice. Y al hacer click sobre “Performance Analysis” vemos esto:

CONSULTAS6

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

¿Y qué pasa si en el WHERE en lugar de escribir B.BAN_CODSUC >= 0 escribimos B.BAN_IDENTI > 0?

Después de todo, el índice está compuesto por ambas columnas: B.BAN_CODSUC y B.BAN_IDENTI, así que la pregunta es ¿obtendremos el mismo resultado?

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE AS BAN_NOMSUC,
   B.BAN_IDENTI,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO
WHERE
   B.BAN_IDENTI > 0

El nuevo PLAN es este:

CONSULTAS7

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

Lo cual nos indica que la tabla S (o sea: SUCURSALES) no está usando un índice. Y si hacemos click sobre “Performance Analysis” obtenemos:

CONSULTAS8

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

Finalmente, cambiamos nuevamente la cláusula WHERE y observamos lo que ocurre:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE AS BAN_NOMSUC,
   B.BAN_IDENTI,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO
WHERE
   B.BAN_CODSUC >= 0 AND
   B.BAN_IDENTI > 0

Ahora el PLAN del SELECT es este:

CONSULTAS9

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

El cual nos indica que ambas tablas (SUCURSALES y BANCOS) están usando índices. Y al hacer click sobre “Performance Analysis” vemos:

CONSULTAS10 (haciendo click en la imagen la verás más grande)

Conclusión:

 Cuando la consulta está optimizada devuelve sus resultados mucho más rápidamente que cuando no lo está. Por lo tanto debemos siempre tratar de que todas nuestras consultas estén optimizadas. Para ello contamos con dos ayudas:

  • el PLAN de la consulta
  • el Análisis de la Performance de la consulta

Mediante el PLAN podremos saber cuales tablas están usando índices y cuales son los índices que utilizan. A veces el Firebird utiliza un índice que no es el más apropiado y en ese caso podemos obligarle a que use otro índice mejor (mira el enlace de más abajo)

Mediante el Análisis de la Performance podremos saber que tan eficiente es el uso de un índice. No se tienen los mismos resultados con cualquier índice, algunos son mejores que otros

El PLAN y el Análisis de la Performance debemos usarlos juntos, no nos servirá de mucho usar al uno sin el otro. Si solamente usamos el PLAN no sabremos que la performance es baja aunque se esté usando un índice. Si solamente analizamos la performance no sabremos cual índice se está usando y por lo tanto tampoco sabremos que se puede usar otro índice con el cual obtener mejores resultados.

Cuando lo que queremos es obtener los datos y no nos interesa obtenerlos con un orden en especial entonces utilizar NATURAL en la tabla principal (la que se encuentra a continuación de la cláusula FROM) es lo mejor, ya que es lo más rápido que podemos conseguir.

Artículos relacionados:

https://firebird21.wordpress.com/2013/04/30/usando-un-plan/

https://firebird21.wordpress.com/2013/05/03/algo-mas-sobre-plan/

https://firebird21.wordpress.com/2013/05/02/esta-la-vista-optimizada/

https://firebird21.wordpress.com/2013/05/07/detectando-una-consulta-que-esta-tardando-mucho/

https://firebird21.wordpress.com/2013/03/09/consultas-lentas-causas-y-soluciones/

Anuncios

Creando una copia SHADOW

4 comentarios

Firebird posee una característica que en ciertas circunstancias puede ser muy útil: crear una Base de Datos “shadow”.

Aunque la palabra “shadow” significa “sombra”, en Informática se la traduce como “espejo”.

Una Base de Datos shadow es una Base de Datos que es idéntica a la original y que es actualizada en el mismo momento en el cual la original es actualizada.

¿Para qué sirve?

Para poder recuperar al instante una Base de Datos que se dañó por un problema físico del disco duro, un problema en la red o porque fue borrada.

¿Cómo funciona?

En un disco duro distinto al disco duro de la Base de Datos original se tiene una versión idéntica a la cual se la conoce como copia “shadow”, todo lo que se inserte, borre o modifique en la Base de Datos original automáticamente también será insertado, borrado o modificado en la Base de Datos shadow.

¿Por qué el disco duro debe ser distinto?

En realidad ambas bases de datos podrían estar en el mismo disco duro pero eso no tendría mucho sentido ya que la principal utilidad de la copia shadow es servir de respaldo cuando ocurre un problema de daño físico en la Base de Datos original. Y en ese caso lo más probable es que todo el disco duro se haya tornado inaccesible, no solamente una parte de él.

Por lo tanto, lo normal es que ambas bases de datos se encuentren en discos duros distintos.

¿Si alguien borró o modificó una fila por error, la copia shadow  servirá de respaldo para recuperar esa fila a su estado anterior?

No, porque la copia shadow es exactamente igual a la Base de Datos original. Cualquier inserción, borrado o modificación en la Base de Datos original se refleja al instante en la copia shadow. La copia shadow solamente es útil cuando ocurre un daño físico en el disco duro original, o la red no funciona o se borró la Base de Datos original. Por lo tanto, si alguien borró por error o por intención una fila en la Base de Datos original, esa fila también estará borrada en la copia shadow.

¿Cuál es el beneficio de tener una copia shadow?

Que si ocurre un desastre en el hardware que dañó, borró o volvió inaccesible a la Base de Datos original, se puede recuperar rápidamente la copia shadow la cual estará exactamente igual a como estaba la Base de Datos original antes de que el desastre ocurriera. El mismo beneficio se tendrá si la Base de Datos fue borrada o fue dañada por un virus o algún otro programa o usuario malicioso.

¿Qué se debe de tener en cuenta cuándo se utiliza una copia shadow?

  1. La copia shadow es una copia exacta de la Base de Datos original. Todo lo que se inserta, borra o modifica en ésta instantáneamente se refleja en aquella
  2. Su utilidad es servir de respaldo en el caso de daño físico del disco duro, de problema grave en la red, o si la Base de Datos original fue borrada o dañada
  3. Cuando se activa una copia shadow, ésta empieza a funcionar en ese mismo instante
  4. Funciona en forma invisible, los programadores nada deben hacer para que funcione
  5. No se requiere acceso exclusivo a la Base de Datos para crearla
  6. Pueden tenerse varias copias shadow a la vez
  7. No es una protección contra corrupción de datos causada por problemas de software
  8. Es un método de todo o nada. Cuando se activa una copia shadow, toda la Base de Datos es activada, no se puede activar solamente algunas tablas o algunos stored procedures, o algunos triggers, etc. Cuando se la activa, se la activa totalmente.
  9. Debe estar en un disco duro fijo, no puede estar en un disco duro removible, ni en un pen-drive, ni en un disco mapeado de la red
  10. No es un sustituto para backups. No creas que por tener shadows puedes dejar de hacer backups periódicamente.
  11. Como cualquier otro archivo de la computadora, puede ser dañado o borrado.
  12. No te puedes conectar a una copia shadow. Nunca trates de conectarte a una copia shadow ni de moverla a otra carpeta, ni de borrarla. Cuando sea necesario conectarse a ella el Firebird sabe muy bien como hacerlo

¿Cuál es la sintaxis para crear una copia shadow?

CREATE SHADOW NúmeroCopia [AUTO | MANUAL] [CONDITIONAL] “NombreArchivo”

Ejemplo:

SHADOW1

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

  • Debes estar conectado a la Base de Datos en la cual crearás la copia shadow
  • El modo por defecto es AUTO. Eso significa que cuando la Base de Datos original se torne inaccesible automáticamente  se usará la copia shadow
  • El NúmeroCopia puede ser cualquier número entero, no necesariamente 1
  • El NombreArchivo no debe existir
  • No es obligatorio que el nombre del archivo sea ‘MiShadow1’, puede ser cualquier nombre
  • No es obligatorio que la extensión del archivo sea .shd, puede ser cualquiera o inclusive no tener extensión

¿Cómo se puede saber si la copia shadow existe?

Con el comando SHOW DATABASE;

SHADOW2

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

En este caso, como el modo es AUTO la copia shadow se activará automáticamente cuando la Base de Datos original no pueda ser accedida.

¿Cómo se activa una copia shadow cuyo modo es MANUAL?

Mediante el programa GFIX.EXE y la opción -activate, como se puede ver en esta imagen:

SHADOW3

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

¿Para qué sirve la cláusula CONDITIONAL?

Cuando el modo es AUTO y la Base de Datos se volvió inaccesible la copia shadow automáticamente toma su lugar y desde ese momento ya no hay una copia shadow (porque la copia que era shadow ahora es la Base de Datos en uso)

Cuando el modo es MANUAL la copia shadow no puede ser usada hasta que sea activada mediante GFIX -activate y desde que es activada ya no existe una copia shadow (porque la que era la copia shadow ahora es la Base de Datos en uso). Generalmente se elige MANUAL cuando se quiere que las conexiones a la Base de Datos se mantengan bloqueadas hasta que manualmente se habilite una nueva copia shadow.

En el caso de AUTO ya no hay una copia shadow. En el caso de MANUAL puede haberla si fue creada manualmente, en otro caso no.

Cuando el modo es AUTO la copia shadow toma rápidamente el lugar de la Base de Datos original y hasta que el administrador cree una nueva copia shadow  la nueva Base de Datos estará funcionando sin un shadow. Esta es una situación que en muchas situaciones podría ser inaceptable, porque si ocurre un nuevo desastre antes de crearse una nueva copia shadow todas las transacciones que ocurrieron se perderán para siempre.

Al usar la palabra clave CONDITIONAL se le dice al Firebird que cuando la copia shadow se convierta en la Base de Datos activa automáticamente cree una nueva copia shadow. De esta manera siempre se tendrá una copia shadow. En general, esta es la opción preferible de usar.

¿Qué más se debe hacer para que la copia shadow reemplace a la Base de Datos original?

Debes renombrarla para que pueda ser utilizada por los programas. Por ejemplo:

La Base de Datos original se llamaba: CONTA.FDB

La copia shadow se llamaba: SHADOWCONTA1.SHD

Después que la copia shadow se convirtió en la Base de Datos activa debes renombrarla como: CONTA.FDB para que todos los programas puedan conectarse a ella

¿Cómo se borra una copia shadow?

Si por alguna razón ya no quieres tener una copia shadow puedes escribir:

DROP SHADOW NúmeroCopia;

y la Base de Datos ya no tendrá la copia shadow NúmeroCopia. Por ejemplo:

DROP SHADOW 1

Harías esto cuando la copia shadow existe, es accesible, pero ya no la quieres usar por alguna razón.

¿Cómo se elimina la referencia a una copia shadow?

Si una copia shadow es borrada del disco duro o es renombrada o por alguna otra razón se vuelve inaccesible, será imposible conectarse a su Base de Datos original, todos los intentos serán rechazados:

SHADOW4

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

 En realidad, este problema generalmente ocurre cuando el modo es MANUAL, cuando el modo es AUTO la Base de Datos automáticamente elimina la referencia a la copia shadow y la conexión puede realizarse exitosamente.

El programa GFIX.EXE tiene una opción -kill que sirve para eliminar las referencias a las copias shadows inaccesibles.

SHADOW5

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

 A partir de este momento ya se podrá acceder a la Base de Datos, pero ésta se encontrará sin ninguna copia shadow. Si se desea que tenga una copia shadow habrá que crearla como se explicó más arriba.

CONCLUSIÓN:

Una buena característica del Firebird es que nos permite tener copias shadows muy fácilmente y gracias a ello si ocurre un desastre en el hardware no se perderán los datos, podrán ser recuperados al 100%.

Pero lo anterior no significa que deben dejarse de lado los backups periódicos, ya que las copias shadows y los backups sirven para cosas distintas: las copias shadows para tener copias que pueden ser utilizadas muy rápidamente en caso de fallas en el hardware o borrado físico de la Base de Datos; los backups para recuperar los datos que se tenían horas o días atrás y para resguardarse en caso de un incendio, robo de la computadora y situaciones similares.