Consejos para optimizar el rendimiento de las bases de datos

3 comentarios

IBExpert es una empresa alemana que se dedica a desarrollar programas para Firebird. Publicaron un documento con consejos para obtener el máximo rendimiento posible de una Base de Datos. Ese documento puedes mirarlo en:

http://ibexpert.net/ibe/uploads/Main/FBPerformanceRecommendations_1.pdf

Puede servirte de guía para aconsejar a tus clientes o para decidir que hardware y software utilizar.

Sus recomendaciones son:

  1. Cualquier sentencia (INSERT, UPDATE, DELETE, SELECT) que requiera más de 100 milisegundos para su ejecución debe ser revisada
  2. Cada SELECT que en su PLAN tenga NATURAL o SORT debe ser revisado
  3. Si las instrucciones SQL no están optimizadas, el programa que las usa no será bueno para la Empresa, sin importar las demás ventajas que el proveedor del software pueda ofrecer
  4. Todas las instrucciones SQL pobremente creadas deben ser reemplazadas por versiones optimizadas, para asegurar el confort de los usuarios y la usabilidad. No se debe aceptar, por ninguna razón, instrucciones SQL que no estén optimizadas. Pequeños cambios en esas instrucciones pueden mejorar la velocidad dramáticamente y disminuir la carga de trabajo del Servidor en gran medida
  5. Firebird es capaz de manejar la carga de trabajo concurrente de miles de usuarios, pero cuando cada usuario tiene la carga de trabajo de cientos de usuarios los resultados no serán buenos (y en esas condiciones con ningún SGBDR serán buenos).
  6. El almacenamiento de datos debe realizarse en dispositivos SSD. Mientras que un disco duro convencional admite hasta 200 IOPS (operaciones de entrada/salida por segundo) un SSD empresarial admite hasta 500.000 IOPS. La diferencia es inmensa. Otras ventajas de los SSD con respecto a los discos duros convencionales son: mucha mayor velocidad de escritura, velocidad de lectura 10 veces mayor, latencia de lectura/escritura cientos de veces más rápida, son inaudibles, menor consumo de energía, menor calor, tiempo medio entre fallos muy superior, no requieren defragmentación, menor peso, menor tamaño, soportan golpes muy fuertes, borrado seguro de los datos.
  7. Se debe usar un SSD para el Sistema Operativo, otro SSD para las bases de datos, otro SSD para las copias shadow. Se deben reemplazar los SSD cada 2 años (la misma recomendación se aplica a los discos duros tradicionales). La capacidad de almacenamiento de los SSD debe ser como mínimo 400% mayor que el tamaño de las bases de datos.
  8. Sistema Operativo Windows 7 Pro 64 bits o Windows 2008 R2x64. El único servicio activo debe ser RDP. No antivirus, no on-line backup. Todos los servicios no usados deben ser deshabilitados, especialmente Windows Update. Los accesos al sistema de archivos deben ser manejados por FTP, por ejemplo FileZilla, para transferir los archivos del backup a otra computadora. Linux también es ok pero tiene una variedad más amplia de problemas potenciales.
  9. Un disco RAM para guardar en él los archivos temporales es muy útil. En este caso la memoria de la computadora debe ser de 16 GB ó de 32 GB, más de eso no es necesario
  10. La replicación es opcional pero muy buena medida de seguridad porque si la computadora donde se encuentran actualmente las bases de datos sufre algún problema severo de hardware entonces se podrán seguir usando las bases de datos replicadas que se encuentran en otra computadora sin pérdida de datos.
  11. El Servidor de las bases de datos Firebird debe encontrarse en una computadora dedicada y en esa computadora solamente debe encontrarse el Servidor Firebird, nada más.

Artículo relacionado:

El índice del blog Firebird21

 

 

Anuncios

Un archivo de lotes para mantenimiento de la Base de Datos

2 comentarios

Por el uso (y abuso) diario las bases de datos normalmente van perdiendo performance, volviéndose más lentas todas las operaciones que se realizan en ellas. Una forma de mantener a la Base de Datos saludable es a través del siguiente archivo .BAT

CLS

@ECHO OFF

ECHO IMPORTANTE: El nombre de la Base de Datos NO DEBE TENER LA EXTENSION para que este archivo .BAT funcione bien.

@ECHO ON

SET ISC_USER=SYSDBA

SET ISC_PASSWORD=masterkey

SET CarpetaFirebird=C:\Program Files\Firebird\Firebird_2_5\bin\

SET CarpetaBaseDatos=E:\SQL\DATABASES\

SET CarpetaBackup=E:\SQL\BACKUPS\

SET NombreBaseDatos=INTEGRAL

C:

CD "%CarpetaFirebird%"

GFIX "%CarpetaBaseDatos%%NombreBaseDatos%.FDB" -shut single -force 0

GFIX "%CarpetaBaseDatos%%NombreBaseDatos%.FDB" -validate -full -ignore

GFIX "%CarpetaBaseDatos%%NombreBaseDatos%.FDB" -mend -ignore

GFIX "%CarpetaBaseDatos%%NombreBaseDatos%.FDB" -online

IF EXIST "%CarpetaBackup%%NombreBaseDatos%_GBAK.LOG" DEL "%CarpetaBackup%%NombreBaseDatos%_GBAK.LOG"

GBAK "%CarpetaBaseDatos%%NombreBaseDatos%.FDB" "%CarpetaBackup%%NombreBaseDatos%.FBK" -e -g -ig -l -nt -b -v -y "%CarpetaBackup%%NombreBaseDatos%_GBAK.LOG"

IF EXIST "%CarpetaBackup%%NombreBaseDatos%_GBAK2.LOG" DEL "%CarpetaBackup%%NombreBaseDatos%_GBAK2.LOG"

IF EXIST "%CarpetaBaseDatos%%NombreBaseDatos%_NEW.FDB" DEL "%CarpetaBaseDatos%%NombreBaseDatos%_NEW.FDB"

GBAK "%CarpetaBackup%%NombreBaseDatos%.FBK" "%CarpetaBaseDatos%%NombreBaseDatos%_NEW.FDB" -o -r -v -REP -y "%CarpetaBackup%%NombreBaseDatos%_GBAK2.LOG"

GFIX "%CarpetaBaseDatos%%NombreBaseDatos%.FDB" -shut -force 0

E:

IF EXIST "%NombreBaseDatos%_ANTERIOR.FDB" DEL "%NombreBaseDatos%_ANTERIOR.FDB"

RENAME "%NombreBaseDatos%.FDB" "%NombreBaseDatos%_ANTERIOR.FDB"

IF EXIST "%NombreBaseDatos%.FDB" DEL "%NombreBaseDatos%.FDB"

RENAME "%NombreBaseDatos%_NEW.FDB" "%NombreBaseDatos%.FDB"

C:

GFIX "%CarpetaBaseDatos%%NombreBaseDatos%.FDB" -online

@PAUSE

Como seguramente ya sabes, los archivos .BAT son los archivos de lotes (batch, en inglés) del antiguo Sistema Operativo D.O.S. y que el Windows ha heredado. En estos archivos .BAT puedes escribir varios comandos, los cuales se ejecutarán en el mismo orden establecido.

Este archivo .BAT realiza las siguientes tareas:

  1. Borra la pantalla
  2. Muestra un mensaje al usuario
  3. Establece las variables de entorno. Son todas las que empiezan con la palabra SET. Fíjate que para no estar escribiendo el nombre del usuario y su password en cada comando GFIX y GBAK que hay más abajo, se asignaron sus valores a dos variables. No es obligatorio hacer así, pero se escribe menos.
  4. El nombre de la Base de Datos debe escribirse sin la extensión (o sea, sin el .FDB). Por eso, aunque el nombre real de la Base de Datos usada en este ejemplo es INTEGRAL.FDB en la variable se escribió solamente INTEGRAL
  5. El comando GFIX -shut single -force 0 permite que solamente el usuario SYSDBA pueda estar conectado a la Base de Datos, nadie más. El -force 0 cerrará inmediatamente todas las conexiones. ¿Qué implica eso? Que este archivo .BAT solamente debe ejecutarse cuando nadie está usando la Base de Datos. De lo contrario, los usuarios que estén conectados perderán la conexión y posiblemente el trabajo que estaban haciendo.
  6. Los siguientes dos comandos GFIX sirven para validar y corregir errores en la Base de Datos
  7. El cuarto comando GFIX vuelve a permitir que otros usuarios se conecten a la Base de Datos
  8. Antes de realizar el backup se verifica si existe el archivo LOG (en los archivos de LOG se guarda información con respecto a la ejecución de un comando). Si existe, se lo borra
  9. Se realiza el backup y las tareas que realizó se guardan en un archivo de LOG, que es un archivo de texto que puede ser revisado en cualquier momento
  10. Idénticamente, antes de restaurar el backup se verifica si existen su archivo de LOG y un archivo con el mismo nombre del que será restaurado. Si existen, se los borra
  11. Se realiza la restauración del backup
  12. Nuevamente se desconecta a todos los usuarios. Esto es necesario para que el nombre de la Base de Datos pueda ser cambiado, si alguien la estuviera usando, su nombre no podría cambiarse
  13. Se cambian los nombres de dos archivos
  14. Y finalmente, se vuelve a permitir que todos los usuarios puedan conectarse a la Base de Datos

Si en el punto 14. obtienes un error, no te preocupes, significa que todos ya podían conectarse y le volviste a pedir que todos se pudieran conectar.

Algunos puntos importantes a recordar:

  1. Este archivo .BAT debe estar en la computadora del Servidor, no funcionará remotamente
  2. Debes crear un archivo de texto con la extensión .BAT para que funcione. Para mayor facilidad, puedes colocar un acceso directo en el escritorio.
  3. Si ejecutas este archivo .BAT periódicamente, te asegurarás que tu Base de Datos siempre se encuentre en buen estado y que todas las operaciones en ella sean lo más rápidas posibles
  4. Debes ejecutarlo solamente cuando estás seguro que nadie está conectado a la Base de Datos, porque este archivo .BAT desconectará automáticamente a todos quienes estén conectados y eso les podría ocasionar pérdida de sus trabajos
  5. La carpeta BACKUPS debe existir, si no existe obtendrás un mensaje de error y este .BAT no cumplirá su misión
  6. Debes establecer los valores de las variables de entorno (las que empiezan con SET) acordemente a tu situación particular. Las que ves aquí son solamente un ejemplo
  7. Antes de renombrar los archivos (casi al final del .BAT) se cambia a la unidad E:, porque en esa unidad tengo yo mis bases de datos. En tu caso, debes colocar allí la letra que corresponda (C:, D:, E:, F:, la que sea)

Artículo relacionado:

El índice del blog Firebird21

SQL_RENDIMIENTO. Verificando la velocidad de las operaciones

4 comentarios

Muchas veces nos interesa saber que tan rápidas son las operaciones INSERT, UPDATE, DELETE, SELECT en una computadora Cliente o mismo en el Servidor del Firebird.

Esa información puede ser muy útil para recomendar la compra de más memoria RAM, un mejor procesador, un router de mejor calidad, etc.

La mayoría de las empresas disponen de varias computadoras y las velocidades con las cuales se realizan las operaciones en ellas no son las mismas, así que puede resultarnos muy provechoso poder determinar cuales son las computadoras lentas. Esa lentitud puede estar en el Servidor, en el Cliente, o en ambos.

Por ese motivo hice un programa que me ayudará con esa tarea y ahora lo comparto con los lectores de este blog. El enlace para descargarlo es:

http://www.mediafire.com/download/eudns020fs6xf64/SQL_RENDIMIENTO.ZIP

Está desarrollado en Visual FoxPro y tiene todos los programas fuente incluidos para que quienes conozcan dicho lenguaje puedan realizarle todas las modificaciones que crean pertinentes. También podría servirles como base para crear otros programas similares.

SQL_RENDIMIENTO1

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

Como puedes ver en la Captura 1, es bastante auto-explicativo pero si tienes alguna duda puedes preguntarme.

Para usarlo:

  1. Copia la Base de Datos de nombre VELOCIDAD.FDB en un disco duro de la computadora donde está instalado el Servidor del Firebird
  2. Crea una carpeta en la computadora Cliente
  3. Descomprime el archivo SQL_RENDIMIENTO.ZIP en esa carpeta
  4. Ejecuta el programa SQL_RENDIMIENTO.EXE

También podrías ejecutarlo desde un pen-drive pero eso no es recomendable pues allí tendrás que controlar una variable más: la velocidad del pen-drive, pues si es USB 3.0 será mucho más rápido que USB 2.0 y este será mucho más rápido que USB 1.0, y en una computadora Cliente podrías tener USB 2.0 y en otra USB 3.0 y en otra USB 1.0 y por lo tanto la comparación no sería justa.

Mientras el programa está verificando las velocidades va mostrando unos mensajes en el campo de edición “Comentarios” y al finalizar la verificación muestra un resúmen que puede servirte de guía para encontrar las fallas. Ese resúmen puede ser guardado en un archivo de texto (cuya ubicación y nombre debes colocar a continuación de: “Grabar en el archivo”).

Las demoras en el Cliente típicamente son mayores que las demoras en el Servidor porque el primero necesita que los datos vayan al Servidor y regresen y eso toma su tiempo. Es por eso que se muestran ambas demoras.

El programa califica con una nota (EXCELENTE, MUY BUENO, BUENO, ACEPTABLE, REGULAR, MALO, MUY MALO, PÉSIMO) a ambas computadoras, dependiendo del tiempo total con la cual se realizaron todas las operaciones. También da unos consejos (que en versiones posteriores podrían ampliarse) para mejorar esas velocidades. Debes tener en mente que esa calificación está basada en el procesamiento de 1.000.000 de filas y que si varías dicha cantidad de filas la calificación podría variar ya que cuantas más filas proceses el rendimiento mejorará (si multiplicas por 10 la cantidad de filas el tiempo que se demora en procesarlas el Firebird no se multiplica por 10, sino quizás por 2).

Otro punto muy importante a considerar es el siguiente: una Base de Datos totalmente vacía es mucho más rápida que una Base de Datos en la cual ya se realizaron operaciones aunque se hayan eliminado luego todas esas operaciones. ¿Qué significa esto? que siempre antes de ejecutar el programa SQL_RENDIMIENTO.EXE debes hacer un ciclo backup/restore en la Base de Datos VELOCIDAD.FDB y usar la Base de Datos restaurada para la verificación (o aún más rápido, copia la Base de Datos original, la que descargaste en el archivo .ZIP, en el Servidor) . Si no haces así, entonces cada nueva ejecución de SQL_RENDIMIENTO.EXE demorará más y eso no será justo

Entonces, antes de verificar una computadora:

  1. Copia el archivo VELOCIDAD.FDB que está en tu pen-drive en el disco duro del Servidor
  2. Crea una carpeta en el disco duro de la computadora Cliente y coloca allí a SQL_RENDIMIENTO
  3. Verifica esa computadora

Haz esto siempre, no te olvides, o los resultados mostrados no serán correctos.

Artículo relacionado:

El índice del blog Firebird21

Recreando índices y calculando estadísticas

7 comentarios

En este artículo habíamos visto como recalcular las estadísticas de todos los índices de todas las tablas:

Selectividad de los índices

Y en este otro artículo habíamos visto como podemos reindexar todos los índices de todas las tablas:

Recreando todos los índices de todas las tablas

en el presente artículo combinaremos ambos stored procedures en uno solo porque de esa manera nos resultará más fácil el mantenimiento de los índices.

SET TERM ^ ;

CREATE PROCEDURE SP_MANTENIMIENTO_INDICES
RETURNS(
   tcNombreTabla  VARCHAR(31),
   tcNombreIndice VARCHAR(31),
   tcRestriccion  VARCHAR(11),
   tcReindex      VARCHAR( 2),
   tcStatistics   VARCHAR( 2))
AS
BEGIN

   FOR SELECT
      RDB$RELATION_NAME,
      RDB$INDEX_NAME
   FROM
      RDB$INDICES
   ORDER BY
      RDB$RELATION_NAME
   INTO
      :tcNombreTabla,
      :tcNombreIndice
   DO BEGIN
      tcReindex     = '';
      tcStatistics  = '';
      tcRestriccion = (SELECT RDB$CONSTRAINT_TYPE FROM RDB$RELATION_CONSTRAINTS WHERE RDB$INDEX_NAME = :tcNombreIndice);
      tcRestriccion = IIF(tcRestriccion IS NULL, '', tcRestriccion);
      IF (NOT EXISTS(SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE RDB$INDEX_NAME = :tcNombreIndice) AND LEFT(tcNombreIndice, 4) <> 'RDB$') THEN BEGIN
         EXECUTE STATEMENT 'ALTER INDEX ' || :tcNombreIndice || ' INACTIVE ;' ;
         EXECUTE STATEMENT 'ALTER INDEX ' || :tcNombreIndice || ' ACTIVE ;' ;
         tcReindex = 'SI';
      END
      EXECUTE STATEMENT 'SET STATISTICS INDEX ' || :tcNombreIndice || ';' ;
      tcStatistics = 'SI';
      IF (LEFT(tcNombreIndice, 4) <> 'RDB$') THEN
         SUSPEND;
   END

END^

En este stored procedure solamente recreamos los índices nuestros y que además no estén relacionados con una restricción pero recalculamos las estadísticas de todos los índices (los internos del Firebird y los de las restricciones también). Como normalmente no nos interesa mostrar los índices internos del Firebird sino solamente los nuestros entonces al SUSPEND lo colocamos dentro de un IF.

A este stored procedure lo llamaríamos así:

SELECT * FROM SP_MANTENIMIENTO_INDICES

Y aquí está una captura de lo que obtendremos al ejecutar este SELECT:

MANTENIMIENTO1

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

Como puedes ver, allí están el nombre de la tabla, el nombre del índice, la restricción, si fue reindexado o no, y si se recalcularon sus estadísticas. Este SELECT puede resultarte muy útil para usarlo en un programa que le muestre al usuario el mantenimiento de los índices.

Artículos relacionados:

Usando índices en Firebird

Selectividad de los índices

Recreando todos los índices de todas las tablas

El índice del blog Firebird21

Recreando todos los índices de todas las tablas

3 comentarios

Como hemos visto en estos artículos:

Recreando los índices de las tablas

Usando índices en Firebird

es muy importante que todos los índices de todas nuestras tablas estén bien balanceados para que cuando realicemos las operaciones de INSERT, UPDATE, DELETE no se pierda más tiempo del debido en mantenerlos actualizados. Un índice desbalanceado tarda más en actualizarse que uno correctamente balanceado; por ese motivo debemos tratar de tener siempre a todos los índices de todas las tablas bien balanceados.

Los siguientes comandos tienen por objetivo recrear un índice, o reindexarlo como también se dice:

ALTER INDEX MiIndice INACTIVE

ALTER INDEX MiIndice ACTIVE

y funcionan muy bien pero … ¿y si queremos reindexar no solamente un índice sino todos los índices de todas las tablas? Evidentemente escribir esos comandos para cada índice de cada tabla tomará mucho tiempo, será muy tedioso y además correremos el riesgo de olvidarnos de alguno. Por ello, escribí el siguiente stored procedure que se encarga de dicha tarea:

SET TERM ^ ;

CREATE PROCEDURE SP_ACTUALIZAR_INDICES
AS
   DECLARE VARIABLE lcNombreIndice VARCHAR(31);
BEGIN

   FOR SELECT
      RDB$INDEX_NAME
   FROM
      RDB$INDICES
   WHERE
      LEFT(RDB$INDEX_NAME, 4) <> 'RDB$'
   INTO
      :lcNombreIndice
   DO BEGIN
      IF (NOT EXISTS(SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE RDB$INDEX_NAME = :lcNombreIndice)) THEN BEGIN
         EXECUTE STATEMENT 'ALTER INDEX ' || :lcNombreIndice || ' INACTIVE ;' ;
         EXECUTE STATEMENT 'ALTER INDEX ' || :lcNombreIndice || ' ACTIVE ;' ;
      END
   END

END^

SET TERM ; ^

¿Qué hace este stored procedure?

  1. Obtiene los nombres de todos los índices de todas las tablas cuyas primeras 4 letras sean distintas que “RDB$”. ¿Por qué eso? porque los nombres de las tablas y de los índices de los metadatos siempre empiezan con “RDB$” y los nombres de nuestras tablas y de nuestros índices no deberían empezar con esas letras. En otras palabras, lo que se obtiene son los nombres de nuestros índices, no los nombres de los índices que usa internamente el Firebird
  2. Los índices de las restricciones (Primary Key, Foreign Key, Unique Key) no pueden ser desactivados, el Firebird no lo permite. Y es muy lógico, si se desactivara el índice de una restricción entonces el Firebird no podría verificar esa restricción, por lo tanto hace la fácil: no te permite desactivar el índice de una restricción. Entonces en el stored procedure se verifica que el índice no pertenezca a una restricción. Eso se consigue buscando su nombre en la tabla RDB$RELATION_CONSTRAINTS pues en esa tabla se guardan los datos de todas las restricciones.
  3. Si el nombre del índice no empieza con RDB$ (o sea, si es un índice nuestro) y no es el índice asociado a una restricción entonces se lo inactiva y se lo activa. Este ciclo desactivar/activar tiene por efecto recrear al índice y nos asegura que el nuevo índice esté correctamente balanceado.

Otra versión del stored procedure:

Aquí hay otra versión del stored procedure de arriba, en esta es seleccionable para que puedas ver los nombres de los índices que se están recreando.

SET TERM ^ ;

CREATE PROCEDURE SP_ACTUALIZAR_INDICES
RETURNS(
   tcNombreIndice VARCHAR(31))
AS
BEGIN

   FOR SELECT
      RDB$INDEX_NAME
   FROM
      RDB$INDICES
   WHERE
      LEFT(RDB$INDEX_NAME, 4) <> 'RDB$'
   INTO
      :tcNombreIndice
   DO BEGIN
      IF (NOT EXISTS(SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE RDB$INDEX_NAME = :tcNombreIndice)) THEN BEGIN
         EXECUTE STATEMENT 'ALTER INDEX ' || :tcNombreIndice || ' INACTIVE ;' ;
         EXECUTE STATEMENT 'ALTER INDEX ' || :tcNombreIndice || ' ACTIVE ;' ;
         SUSPEND ;
      END
   END

END^

SET TERM ; ^

Y llamarías a este stored procedure así:

SELECT * FROM SP_ACTUALIZAR_INDICES

El problema con los índices de las restricciones

¿Y qué pasa con los índices de las restricciones? ¿No pueden quedar desabalanceados? Por supuesto que sí pueden estar desabalanceados, son índices comunes, no son mágicos. Pero como nosotros no podemos recrearlos tenemos una sola alternativa: realizar un ciclo backup/restore porque al restaurar un backup se crean nuevamente todos los índices, los de las restricciones incluidos.

Artículos relacionados:

Recreando los índices de las tablas

Usando índices en Firebird

El índice del blog Firebird21

Replicación 2. Una captura de pantalla

4 comentarios

Esta es una de las ideas que tengo para el programa de replicación, aunque esta captura de pantalla no está relacionada con el tema sirve para ilustrar los conceptos.

REPLICA2_1

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

En el panel de la izquierda se muestran todas las opciones del programa, con la información que resulte pertinente (1).

Cuando una de esas opciones se está ejecutando mostrarla resaltada para que el usuario pueda saber fácilmente cual opción se está ejecutando (2).

En el panel de la derecha pedirle que ingrese los datos que el programa necesita conocer antes de empezar la replicación o mostrarle el progreso de la replicación, por supuesto con los correspondientes mensajes explicativos (3).

Creo que este tipo de pantallas le facilita al usuario entender lo que está haciendo el programa y son útiles al menos para los usuarios principiantes. Para los usuarios avanzados podríamos tener otro tipo de pantallas.

¿Qué te parece la idea? ¿te gusta o no? ¿Algunos puntos negativos que puedas comentar?

Artículo relacionado:

El índice del blog Firebird21

Usando índices en Firebird

7 comentarios

Como seguramente ya sabes, los índices sirven para dos cosas:

  1. Buscar datos
  2. Mostrar las consultas ordenadas

Para poder realizar efectivamente ambas tareas los índices deben encontrarse en buen estado y es tu responsabilidad asegurarte de que eso sea así. El mantenimiento de los índices de Firebird debe contemplar dos puntos:

  1. Que el índice esté bien balanceado
  2. Que las estadísticas de los índices sean las correctas

Balanceando los índices:

Si un índice no está bien balanceado entonces las operaciones INSERT, UPDATE, DELETE, FECTCH y SELECT de esa tabla tomarán más tiempo del debido, haciendo lentas operaciones que podrían ejecutarse más rápidamente. Para balancear un índice debes escribir:

ALTER INDEX MiIndice INACTIVE

ALTER INDEX MiIndice ACTIVE

Este ciclo de inactivar/activar un índice tiene como efecto volver a crearlo. Es el equivalente al comando REINDEX de los lenguajes xBase (dBase, Clipper, FoxPro, etc.). Es conveniente ejecutar esos comandos después de haber realizado muchas operaciones en la tabla (por ejemplo: 20.000 ó más) para tener la seguridad de que el índice se encuentra bien balanceado.

En este artículo encontrarás más información:

https://firebird21.wordpress.com/2013/03/03/recreando-los-indices-de-las-tablas/

Recalculando las estadísticas:

El optimizador de consultas del Firebird revisa la estadística de un índice para decidir si lo utilizará en el PLAN de una consulta o no. Si la estadística de un índice no está actualizada entonces podría dejar de utilizar un índice que sí debería haber utilizado y el resultado será que tendrás una consulta mucho más lenta de lo que debería haber sido si el optimizador usaba el índice.

Es por ese motivo que siempre debemos asegurarnos de tener las estadísticas actualizadas, para que la decisión del optimizador sea la mejor posible. Como el Firebird calcula la estadística solamente cuando crea el índice y después de un ciclo backup/restore, es tu responsabilidad recalcularla periódicamente porque todas las operaciones de INSERT, UPDATE, DELETE que realices en esa tabla afectarán a esa selectividad.

Para recalcular la estadística de un índice debes escribir:

SET STATISTICS INDEX MiIndice

Puedes encontrar más información en este artículo:

https://firebird21.wordpress.com/2013/03/09/selectividad-de-los-indices/

¿Cuáles columnas indexar?

Puede ser tentador pensar que si se indexan todas las columnas de una tabla se conseguirá mucha velocidad en todas las consultas a esa tabla. Eso generalmente es falso. Indexar una columna que tiene pocos valores distintos es un error porque lo más probable es que el optimizador de consultas no utilice ese índice, y todas las operaciones de inserción, actualización, borrado que involucren a esa columna modificarán al índice y eso lleva tiempo entonces ¿para qué indexar esa columna si su índice nunca será usado y actualizarlo hará las operaciones más lentas?

La decisión de indexar o no una columna debe estar basada en si se obtiene una ganancia de velocidad considerable utilizándolo. Si no se obtiene una ganancia de velocidad o si la ganancia de velocidad obtenida es muy pequeña, lo correcto es no indexar esa columna para no gastar recursos en ella.

No tiene sentido indexar una columna si no se realizan búsquedas utilizando esa columna ni se ordenan las consultas según esa columna ni se obtiene una buena ganancia de tiempo.

Tener los índices correctos en cada tabla, y solamente los índices correctos, es una decisión de diseño que debe estar bien estudiada para evitar indexar una columna que no debería estar indexada o dejar de indexar una columna que sí debería estarlo.

Para complicar las cosas, un índice que ahora es bueno dentro de unos meses podría dejar de serlo. Y viceversa. Porque al aumentar grandemente la cantidad de datos en la tabla la selectividad varía y por lo tanto la bondad de indexar o no esa columna. Es por lo tanto necesario que al menos en las tablas más grandes de nuestra Base de Datos periódicamente verifiquemos los índices, su selectividad, y si son o no usados en los PLANES de los SELECTS. Un índice que nunca es utilizado en una búsqueda o en el ordenamiento de una consulta no tiene razón de ser, está de más, está sobrando, es inútil, solamente gasta recursos.

Tampoco es correcto indexar una columna y que su índice se use poquísimo, quizás una o dos veces al mes en una consulta para ganar 2 ó 3 segundos.

Conclusión:

Tener siempre los índices bien balanceados y con sus estadísticas actualizadas es lo que debes preocuparte en conseguir para que todas las operaciones en tus tablas (INSERT, UPDATE, DELETE, FETCH, SELECT) sean lo más rápidas posibles. Un índice en mal estado de conservación perjudica más de lo que ayuda. Por ello es conveniente que tengas un stored procedure que se encargue de esas tareas, el cual podrá ser llamado desde tu aplicación (el programa que hiciste en Visual FoxPro, Delphi, C, C++, etc.)

En muchos lenguajes de programación es el propio programador quien decide si utilizará un índice o no y en caso afirmativo cual índice utilizará, pero en el caso de Firebird es el optimizador de consultas quien toma esa decisión (si no se le ha dicho lo contrario) y basa esa decisión en la selectividad de los índices. ¿Quiéres que tome la decisión más correcta? entonces la selectividad de tus índices debe estar actualizada.

Artículos relacionados:

Recreando los índices de las tablas

Selectividad de los índices

Usando un PLAN

Usando índices correctos para aumentar la velocidad de las consultas

Optimizando las consultas

El índice del blog Firebird21

Older Entries