Consultas lentas, causas y soluciones

6 comentarios

¿Cuándo podemos decir que una consulta es lenta?

Cuando tarda más de 15 segundos en mostrar sus resultados. En épocas anteriores era admisible que para obtener un informe se tuviera que esperar media hora o inclusive más pero ahora si tarda más de 15 segundos los usuarios ya empiezan a desesperarse.

Entonces, veamos los motivos que pueden hacer lenta una consulta y como mejorar los tiempos:

1. No se está usando un índice. En las tablas pequeñas usar un índice o no es casi lo mismo pero en tablas que tienen miles o millones de filas la ganancia en velocidad es impresionante cuando se usa un índice.

2. Transacciones mal gestionadas. En general deberías usar SNAPSHOT y que la transacción sea READ-ONLY

3. Muchas filas modificadas o borradas. Si el informe necesita obtener los datos de filas que han sufrido muchas actualizaciones y aún no se recolectó la basura de esas actualizaciones la obtención de los datos será más lenta.

4. Empezó la recolección de basura en un mal momento. La recolección de basura hará que todas las operaciones en la Base de Datos sean más lentas que lo habitual. Para solucionarlo puedes revisar frecuentemente si la diferencia entre la transacción más antigua y la transacción activa se acerca al intervalo de recolección. Si lo hace entonces tienes dos soluciones: aumentas el intervalo por ejemplo a 30.000 ó lo dejas en 0 y luego te encargas de ejecutar la recolección de basura manualmente (pero no te olvides de hacerlo).

5. Estás usando una UDF que devuelve caracteres. Muchas UDFs devuelven una cantidad fija de caracteres (por ejemplo: 32.000). Eso implica que si por ejemplo las filas a mostrar son 10.000 el Firebird deberá ordenar 300 Mb de datos, lo cual es exageradamente mucho. Aquí la solución es usar CAST para reducir la cantidad de caracteres que devuelve la UDF.

6. Estás usando la claúsula ORDER BY en un stored procedure seleccionable. Esto ocurrirá aunque la columna usada en el ORDER BY tenga un índice en la tabla original. ¿Por qué? porque los stored procedures son código precompilado y por lo tanto no cambiarán cuando sean ejecutados. Eso implica que escribir:


SELECT
   *
FROM
   MiStoredProcedureSeleccionable
ORDER BY
   MiColumna

es un error.

Si necesitas mostrar los datos ordenados lo correcto es que la cláusula ORDER BY se encuentre adentro del stored procedure, no afuera de él.

7. Estás usando un disco lento. Los discos duros tienen distintas velocidades de acuerdo a su capacidad y a su tecnología. SCSI es mejor que SATA y ésta es mejor que PATA.

8. Tienes el Sistema Operativo y la Base de Datos en el mismo disco duro. Tener la Base de Datos en su propio disco, donde solamente se encuentre ella y nada más, mejora en mucho la velocidad.

9. Tienes poca memoria RAM. En este caso obligarás al Sistema Operativo a realizar frecuentes intercambios entre memoria y disco (a eso se le conoce como swap y es un proceso muy lento). Para aumentar la cantidad de memoria que el Firebird usa puedes modificar el archivo FIREBIRD.CONF

10. Estás usando SuperServer en una computadora multi-core y no pusiste el valor de CPU Affinity en el archivo FIREBIRD.CONF. Por defecto SuperServer solamente utiliza el primer procesador aunque la computadora tenga varios, pero cambiando el valor de CPU Affinity se le puede decir cuales procesadores usar.

Los índices son positivos

1 comentario

Como seguramente sabrás, los índices se usan para que las búsquedas sean muy rápidas (cláusula WHERE) y para que las tablas puedan mostrarse ordenadas según ese índice (cláusula ORDER BY).

Pero el Firebird no usa el índice siempre, solamente lo usa cuando habrá una ganancia de tiempo haciéndolo. Si no habrá ganancia de tiempo entonces accede a las filas de la tabla en su propio orden (el llamado NATURAL).

Si queremos que un índice se use, la condición siempre debe ser positiva ya que si la condición es negativa, el índice no será usado.

Estas condiciones sí usarán un índice (siempre que exista, desde luego)

WHERE MiNumero = 10
WHERE MiNumero > 10
WHERE MiNumero > 10 AND MiNumero < 20
WHERE MiNombre IS NULL
WHERE MiFecha BETWEEN '01/01/2010' AND '12/10/2010'

En cambio estas condiciones son negativas y por lo tanto no usarán un índice.

WHERE MiNumero <> 10
WHERE MiNumero IS NOT 10
WHERE MiNombre IS NOT NULL
WHERE MiFecha IS NOT BETWEEN  '01/01/2010' AND '12/10/2010'

¿Conclusión?

Evita los operadores NOT y <> siempre que puedas evitarlos, porque usarlos no te reportará beneficios.

Selectividad de los índices

3 comentarios

Un concepto muy pero muy importante en Firebird es lo que se llama “selectividad de los índices”.

¿Qué significa eso?

Los índices sirven para dos cosas:

  • Para buscar un valor en una tabla
  • Para obtener los datos de una consulta ordenados

Pero tienen sus desventajas también:

  • Ocupan espacio en la Base de Datos (y por lo tanto en el disco duro)
  • Agregarle, borrarle o modificarle un dato a un índice toma su tiempo, no es instantáneo. Si la tabla tiene pocas filas entonces sus índices también serán pequeños y las inserciones rapidísimas pero si la tabla es grande (varias millones de filas)  los índices serán gigantescos e insertarles nuevos datos será bastante más lento

El peor índice, el inútil, el que para nada sirve es aquel que tiene todos sus valores idénticos. Por ejemplo, una tabla tiene una columna llamada SUCURSAL, esa tabla tiene millones de filas, pero en todas esas filas la columna SUCURSAL tiene el valor de cero. Por lo tanto, un índice según la columna SUCURSAL es totalmente inútil.

El mejor índice, el perfecto, es el que tiene todos sus valores distintos. Esa es justamente una de las grandes ventajas de tener Primary Keys e Unique Keys, que sus índices están garantizados de tener todos los valores distintos.

Un índice muy malo es el que tiene pocos valores distintos. Por ejemplo en una tabla de PERSONAS hay una columna para guardar el sexo (masculino o femenino) de cada persona. Se crea un índice sobre esa columna. Pésima idea.

La selectividad de un índice es un número que nos dice cuan efectivo es ese índice y se calcula según esta fórmula:

Selectividad = 1 / Cantidad_de_filas_con_valores_distintos

La mejor selectividad que podría tener un índice se calcula según esta fórmula:

Mejor_Selectividad = 1 / Cantidad_total_de_filas_de_la_tabla

Cuanto más se acerque la selectividad a la mejor selectividad posible, mucho mejor. Veamos algunos ejemplos en una tabla según la columna NOMBRE:

ALICIA, GRACIELA, SUSANA                     Selectividad = 1 / 3 = 0,33   Mejor = 1 / 3
ALICIA, GRACIELA, MIRTHA, SUSANA             Selectividad = 1 / 4 = 0.25   Mejor = 1 / 4
ALICIA, GRACIELA, GRACIELA, MIRTHA, SUSANA   Selectividad = 1 / 4 = 0,25   Mejor = 1 / 5 = 0,20

En el tercer ejemplo la selectividad es de 0,25 (porque hay 4 valores distintos) la cual no es la mejor posible, la mejor posible hubiera sido 0,20 si todos los valores eran distintos. Recuerda que cuanto más se acerque la selectividad a la mejor selectividad posible es mucho mejor.

Cuando la selectividad no es igual a la mejor selectividad posible (como en el tercer ejemplo) eso significa que hay valores repetidos. Y cuantos más valores repetidos haya, mucho peor.

¿Cuál es la conclusión?

Que los buenos índices tienen muy pocos valores repetidos (el mejor caso posible es cuando ningún valor está repetido) y que no tiene sentido crear índices según columnas con muchos valores repetidos porque solamente harán perder tiempo cada vez que se inserte/borre/modifique una fila y no acelerarán las búsquedas ni las consultas.

IMPORTANTE: El Firebird guarda en la Base de Datos la selectividad de cada índice y usa ese número para decidir si usar el índice o no cuando haces una búsqueda o una consulta. Para asegurarte que la selectividad que usa es la correcta debes escribir:

SET STATISTICS INDEX MiIndice

después de haber insertado/borrado/modificado una gran cantidad de filas (por ejemplo, más de 20.000)

Si quieres de una sola vez recalcular las estadísticas de todas las tablas puedes escribir:

SET TERM ^ ;

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

FOR SELECT RDB$INDEX_NAME FROM RDB$INDICES INTO :lcNombreIndice DO
   EXECUTE STATEMENT 'SET STATISTICS INDEX ' || :lcNombreIndice || ';' ;

END^

SET TERM ; ^

El Firebird también actualiza todas las estadísticas de los índices cuando creas el índice o cuando restauras un backup.