Basándome en este artículo:

http://www.firebirdfaq.org/faq13/

decidí escribir uno similar (no es una traducción literal, es el artículo en el cual me basé), ya que es una pregunta muy frecuente por quienes utilizan Firebird.

Entonces, si tu consulta está lenta ¿qué debes hacer?

1. Verifica el PLAN de tu consulta.

Si lo único que quieres hacer es verificar el PLAN entonces no necesitas ejecutar la consulta. En ISQL escribirías SET PLANONLY; y en EMS SQL Manager harías click sobre la opción “Explain query”

CONSULTAS1

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

CONSULTAS2

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

CONSULTAS3

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

Si ves la palabra NATURAL en cualquier tabla que no sea la tabla principal (la tabla principal es la que se encuentra a la derecha de la cláusula FROM) entonces has encontrado el problema.

Si en ese SELECT utilizas la cláusula WHERE o la cláusula JOIN, verifica que todas las columnas de esas cláusulas tengan índices. Si una columna tiene un índice pero ese índice no está siendo usado eso puede ser porque el índice es ascendente (por defecto todos los índices son ascendentes) y allí se estaría necesitando un índice descendente. Si el orden ascendente o descendente está correcto y aún así no se lo está usando entonces lo más probable es que sus estadísticas estén desfasadas y deberás ejecutar el comando SET STATISTICS para que el índice sea usado.

Si usas vistas con uniones, como no puedes indexar una vista, lo recomendable es que uses Firebird 2.0 ó posterior porque las versiones anteriores no usan índices en las columnas que se encuentran en las cláusulas WHERE y JOIN de una vista.

2. Verifica los parámetros de tu transacción

Si el PLAN está ok entonces los parámetros que está usando tu transacción no son los más adecuados. Para los SELECT lo recomendable es que dichos parámetros sean: READ ONLY, SNAPSHOT, WAIT. Si tu no especificas los parámetros de una transacción entonces el Servidor del Firebird utilizará los parámetros por defecto, que son READ WRITE, SNAPSHOT, WAIT.

3. Verifica si no tienes demasiadas filas modificadas o borradas

Si tienes muchas filas modificadas o borradas quizás la recolección de basura empezó en un mal momento para tí. Verifica las estadísticas de la Base de Datos y la diferencia entre la transacción más antigua y la transacción activa. Puedes aumentar el intervalo del sweep o ponerlo en cero y verificar si así mejoró la velocidad de la consulta.

4. Trata de usar un disco RAM

Si estás usando Classic, entonces siempre es una buena idea crear un disco RAM para usarlo como almacenamiento compartido para todos los ordenamientos que no usan un índice (o sea, cuando usas la cláusula ORDER BY pero las columnas no están indexadas).

Debes modificar el archivo FIREBIRD.CONF (que se encuentra en la misma carpeta donde instalaste el Firebird) para que la entrada TempDirectories tenga dos carpetas: primero, la del disco RAM y luego la carpeta Temp.

5. Verifica las funciones UDF que retornan caracteres

Las UDF (User Defined Functions, Funciones Definidas por el Usuario) que retornan caracteres pueden estar retornando muchísimos más caracteres de los que realmente se necesitan. Por ejemplo, la función LPAD() retorna 32.000 bytes. Si usas esa función en una cláusula GROUP BY u ORDER BY y tu SELECT te muestra 10.000 filas eso significa que el Servidor tuvo que ordenar más de 300 MB de datos (32.000 bytes de cada fila por 10.000 filas), lo cual consume muchísimo tiempo. En este caso la solución es ejecutar la función CAST() para acortar el resultado devuelto por la función LPAD() a la longitud máxima que puede tener la columna. Por ejemplo, si sabes que la columna no puede tener más de 60 caracteres entonces la acortas a 60 caracteres y así conseguirás que el ordenamiento sea muchísimo más rápido que cuando cada columna tenía 32.000 caracteres.

6. Verifica si estás usando la cláusula ORDER BY fuera de un stored procedure seleccionable

Si tienes un stored procedure seleccionable que devuelve muchas filas, éste puede ser lentísimo si tienes una cláusula ORDER BY en el SELECT que lo llama.

Esto ocurrirá aunque la columna usada en la cláusula ORDER BY tenga un índice.

¿Por qué?

Porque los stored procedures son código precompilado y por lo tanto ellos no pueden cambiar la forma en la cual la consulta es ejecutada y en consecuencia, la conexión entre la tabla original y las columnas mostradas se pierde.

¿Qué implica eso?

Que el Firebird necesita primero extraer todos los datos del stored procedure seleccionable en una carpeta de almacenamiento temporal y después ordenar esos datos y cuando terminó de ordenarlos recién empezar a mostrarlos.

¿Cuál es la solución?

No usar la cláusula ORDER BY en el SELECT que llama al stored procedure seleccionable o usar la cláusula ORDER BY dentro del stored procedure seleccionable.

Ejemplo 1:

CREATE PROCEDURE MiProc
   RETURNS(
      tnNumero INTEGER)
AS
BEGIN

   FOR SELECT MiColumna FROM MiTablaConMillonesDeFilas INTO :tnNumero DO
      SUSPEND;

END

Si ahora escribes:

SELECT tnNumero FROM MiProc

verás que se ejecuta muy rápido. Pero si escribes:

SELECT tnNumero FROM MiProc ORDER BY tnNumero

verás que se ejecuta súper lentamente. ¿Por qué? porque primero se extrajeron las “millones” de filas, luego se las ordenó  y después se las empezó a mostrar. O sea que el usuario no vio una sola fila hasta que todas fueron ordenadas.

La solución es escribir la cláusula ORDER BY dentro del stored procedure seleccionable, como se muestra a continuación:

CREATE PROCEDURE MiProc
   RETURNS(
      tnNumero INTEGER)
AS
BEGIN

   FOR SELECT MiColumna FROM MiTablaConMillonesDeFilas ORDER BY MiColumna INTO :tnNumero DO
      SUSPEND;

END

7. Verifica la configuración de tu computadora y del Firebird

Si todo lo anterior falló entonces seguramente tienes realmente muchísimos datos en tus tablas y deberás ocuparte de optimizar la configuración.

  •  ¿Usas discos duros PATA, SATA o SCSI? los discos SCSI son más rápidos que los SATA y los SATA son más rápidos que los PATA
  • ¿Está la Base de Datos en el mismo disco duro que el Sistema Operativo? Usar el mismo disco duro reduce el rendimiento
  • ¿Está la Base de Datos en el mismo duro que las carpetas temporales del Firebird? Usar el mismo disco duro reduce el rendimiento
  • ¿Usas la versión de Firebird 1.5 o posterior? deberías hacerlo, porque Firebird 1.0 siempre hace el swap en el disco duro en cambio desde Firebird 1.5 el swap se hace en memoria si hay suficiente memoria libre para hacerlo
  • ¿Le estás dando suficiente memoria RAM a Firebird? En el archivo FIREBIRD.CONF puedes establecer la cantidad de memoria por defecto y la cantidad de memoria máxima.
  • ¿Estás usando Classic? en ese caso puedes probar aumentando el espacio que usan los buffers de las páginas en la memoria RAM. Para ello, usa GSTAT -h y lee la línea Page Buffers para ver cuanto está usando cada cliente y después aumenta DefaultDbCachePages en el archivo FIREBIRD.CONF
  • ¿Estás usando una CPU de varios núcleos (multi-core) y SuperServer? en este caso deberás establecer la opción CPU Affinity en el archivo FIREBIRD.CONF

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/04/24/usando-un-disco-ram-para-aumentar-la-velocidad/

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

https://firebird21.wordpress.com/2013/05/09/usando-indices-correctos-para-aumentar-la-velocidad-de-las-consultas/

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

https://firebird21.wordpress.com/2013/04/28/tablas-agregadas/