Entendiendo los índices compuestos

Deja un comentario

Como sabes, cuando creas un índice éste puede tener una columna o más de una columna. Si el índice tiene una sola columna se lo llama “simple” y si tiene más de una columna se lo llama “compuesto”. ¿Y en qué casos el Firebird utiliza un índice compuesto?

Supongamos que tu índice está compuesto por tres columnas. Firebird podría no usar ese índice, o usar solamente la primera columna, o usar la primera columna y la segunda columna, o usar las tres columnas. Es decir que estas son las posibilidades:

  1. No se usa el índice compuesto
  2. Se usa solamente la primera columna
  3. Se usan la primera columna y la segunda columna
  4. Se usan la primera columna y la segunda columna y la tercera columna

¿De qué depende?

De lo que hayas escrito en la cláusula WHERE. Para que una columna de un índice compuesto se utilice las columnas anteriores deben ser comparadas por igualdad. No por menor, ni por mayor, ni por menor o igual, ni por mayor o igual, ni por distinto. Por igualdad.

 Ejemplo:

Supongamos que tenemos un índice compuesto por tres columnas, (MiColumna1, MiColumna2, MiColumna3). Si escribimos:

WHERE
   MiColumna1 >= 21 AND
   MiColumna2 >= 500 AND
   MiColumna3 <= 2000

se usará solamente la columna MiColumna1 del índice compuesto ¿Por qué? porque en MiColumna1 no se usó una igualdad. En cambio, si escribimos:

WHERE
   MiColumna1 = 21 AND
   MiColumna2 >= 500 AND
   MiColumna3 <= 2000

se usarán las columnas MiColumna1 y MiColumna2 del índice compuesto ¿por qué? porque MiColumna1 fue comparada por igualdad, entonces se usa esa columna y también la siguiente. En cambio, si escribimos:

WHERE
   MiColumna1 = 21 AND
   MiColumna2 = 500 AND
   MiColumna3 <= 2000

se usarán las columnas MiColumna1 y MiColumna2 y MiColumna3. ¿Por qué? porque MiColumna1 y MiColumna2 fueron comparadas por igualdad, entonces se usan esas dos columnas y también la siguiente.

¿Y si se requiere que en todos los casos se usen las tres columnas del índice?

En ese caso la solución es no crear un índice compuesto sino crear tres índices simples, uno por cada columna. De esta manera te asegurarás de que siempre las tres columnas utilicen un índice.

Conclusión:

Es muy importante entender en que circunstancias el Firebird usa los índices compuestos para no crearlos innecesariamente. No te olvides que cada índice ocupa espacio en el disco duro y además hace que todas las operaciones de inserción, actualización, y borrado se realicen más lentamente. Mejoran la velocidad de las consultas pero empeoran las demás operaciones.

Como todo, los índices compuestos tienen sus ventajas y sus desventajas. La ventaja es que es más fácil mantener un solo índice compuesto que varios índices independientes. La desventaja es que podrías tener un índice compuesto que usas muy poco porque en tus consultas usas muy pocas igualdades. Si ése es el caso entonces muy probablemente te convendrá tener varios índices simples y no un índice compuesto.

¿Tienes dudas sobre si te conviene o no crear un índice compuesto?

Haz pruebas. Créalo y verifica el rendimiento. Luego elimina el índice compuesto y crea índices simples y verifica el rendimiento. Compara esos rendimientos y así sabrás si te conviene o no tener un índice compuesto.

Artículo relacionado:

El índice del blog Firebird21

 

Paginando un SELECT

2 comentarios

En ocasiones el conjunto resultado de un SELECT se utiliza para imprimir informes. Por ejemplo, un informe de ventas, o un informe de cobranzas, los cuales el usuario quiere tener impresos en papel.

También puede ocurrir que al usuario le interese imprimir solamente alguna página en especial. Por ejemplo, cayó café sobre la página número cinco, se hizo un desastre, y por eso el usuario quiere volver a imprimir solamente la página cinco; las demás no, porque las demás no se mancharon con café.

Si sabemos cuantas filas del conjunto resultado se imprimen en cada página entonces es muy fácil obtener las filas que nos interesan.

Ejemplo 1:

  • En cada página se imprimen 40 filas
  • Queremos re-imprimir la página número 5
SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla
ROWS
   (5 - 1) * 40 + 1 TO 5 * 40

En este ejemplo se obtendrán las filas 161 a 200, o sea todas las filas que corresponden a la página número 5, ya que cada página tiene 40 filas.

Ejemplo 2:

  • En cada página se imprimen 40 filas
  • Queremos re-imprimir las páginas números 5 y 6
SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla
ROWS
   (5 - 1) * 40 + 1 TO (5 + 1) * 40

En este ejemplo se obtendrán las filas 161 a 240, o sea todas las filas que corresponden a la página número 5 ó a la página número 6.

Forma general

La forma general, para usarla con cualquier números de página, cualquier cantidad de páginas y con cualquier cantidad de filas, es:

SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla
ROWS
   (nPaginaNro - 1) * nCantidadFilas + 1 TO (nPaginaNro + (nCantidadPaginas - 1)) * nCantidadFilas

En esta fórmula hay que reemplazar a nPaginaNro por el número de la primera página que queremos imprimir (en nuestro ejemplo sería 5). Hay que reemplazar a nCantidadFilas por la cantidad de filas que tiene cada página (en nuestro ejemplo sería 40). Hay que reemplazar a nCantidadPaginas por la cantidad de páginas que queremos imprimir (en nuestro último ejemplo sería 2).

Artículo relacionado:

El índice del blog Firebird21

 

¿Importa dónde se ponen las condiciones de un JOIN?

Deja un comentario

Cuando escribes una consulta que tiene JOIN y que también tiene WHERE tienes la posibilidad de poner las condiciones tanto en el JOIN como en el WHERE, o sea donde prefieras.

¿Eso le importa al optimizador de consultas del Firebird?

Es decir, ¿te conviene poner las condiciones en el JOIN, en el WHERE, o es indiferente?

La buena noticia es que es indiferente, no importa donde pongas la condición porque el optimizador elegirá la que devuelva los resultados más rápido. Un optimizador sería muy malo si para hacer bien su tarea dependiera del lugar donde pones las condiciones. Yel optimizador de Firebird es muy bueno.

Entonces, no te preocupes donde pones la condición, puedes ponerla en el JOIN o en el WHERE, que el optimizador de Firebird siempre elegirá la mejor alternativa.

Artículo relacionado:

El índice del blog Firebird21

 

Discos SSD. Pueden aumentar la velocidad en un 300%

6 comentarios

Como seguramente ya sabes, los discos más rápidos que existen en este momento son los SSD, velocidades típicas son de alrededor de 500 MB/s, lo cual es más que suficiente para la gran mayoría de las empresas.

Ahora, un equipo de ingenieros japoneses consiguieron incrementar esa velocidad solamente por software, es decir que no se debe comprar un disco SSD nuevo. Pero no solamente la velocidad se incrementa, también redujeron el consumo en un 60% y el número de ciclos escribir/borrar aumentaron en un 55%.

Justamente el principal defecto de muchos discos SSD es que la cantidad de escrituras es limitada antes de que empiecen a fallar, así que cualquier mejora en ese sentido es siempre muy bienvenida.

Puedes encontrar más información (en inglés) sobre este tema aquí:

http://techon.nikkeibp.co.jp/english/NEWS_EN/20140522/353388/

Artículo relacionado:

El índice del blog Firebird21

 

Cuando usar el predicado EXISTS()

1 comentario

Firebird dispone de un predicado llamado EXISTS() el cual nos dice si el resultado de un SELECT tiene al menos una fila.

La forma general de usarlo es la siguiente:

EXISTS (MiConsulta)

Ejemplo 1:

SELECT
   PRD_CODIGO,
   PRD_NOMBRE
FROM
   PRODUCTOS P
WHERE
   EXISTS(SELECT
             D.MOV_IDEPRD
          FROM
             MOVIMDET D
          WHERE
             D.MOV_IDEPRD = P.PRD_IDENTI AND
             D.MOV_CANTID >= 10)

Esta consulta nos mostrará todos los productos cuya cantidad vendida en una Factura sea mayor o igual que 10.

Ejemplo 2:

BEGIN

   IF (EXISTS(SELECT MiColumna FROM MiTabla WHERE MiCondición)) THEN
      -- Se cumplió la condición
   ELSE
      -- No se cumplió la condición
   END

END

 Esta construcción usaríamos dentro de un stored procedure, un trigger o un EXECUTE BLOCK.

Verificamos si se cumple la condición y luego de acuerdo al resultado realizamos una acción u otra.

Comentarios:

El predicado EXISTS() es mucho más rápido que la función agregada COUNT(*) porque EXISTS() finaliza cuando encuentra una fila que cumple la condición en cambio COUNT(*) cuenta todas las filas.

Así, si por ejemplo una tabla tiene 1.000.000 de filas, y la segunda fila cumple la condición entonces allí mismo ya finaliza EXISTS() y no recorre todas las otras filas porque no es necesario, ya sabe que la condición se cumplió. Pero COUNT(*) debe recorrer a las 1.000.000 de filas para saber cual es esa cantidad.

¿Cuándo es eficiente usar el predicado EXISTS()?

Usar EXISTS() a veces es una buena opción y a veces es una opción pésima ¿por qué? porque EXISTS() va recorriendo secuencialmente todas las filas de la tabla hasta encontrar una que cumpla la condición. Eso implica que si la tabla tiene pocas filas entonces EXISTS() finalizará rápidamente pero si la tabla tiene muchas filas entonces EXISTS() se puede demorar una eternidad si no existe lo buscado o existe pero está cerca del final.

Por lo tanto, si vas a usar el predicado EXISTS() debes preguntarte:

  • ¿Qué tan rápido es el hardware?
  • ¿Cuántas filas tiene la tabla AHORA?
  • ¿Cuántas filas tendrá la tabla dentro de un año, dos años, tres años, …, ocho años?
  • ¿Cuánto tiempo puede demorar la consulta sin que el usuario quiera romper una silla de la bronca?

En general, y al momento de escribir este artículo, si una tabla tiene 100.000 filas o menos entonces se puede usar EXISTS() sin mayor problema. Desde luego que a medida que vaya pasando el tiempo esa cantidad de filas se incrementará porque el hardware será cada vez más y más rápido.

Si sabes que tu tabla tiene o tendrá o puede llegar a tener varios millones de filas, entonces no uses el predicado EXISTS() porque la consulta será extremadamente lenta.

Así que, hoy por hoy, si estás 100% seguro de que la tabla nunca alcanzará a las 100.000 filas, puedes usar EXISTS() sin problema. Pero si la tabla tiene, tendrá, o puede llegar a tener más de 100.000 filas entonces verifica la velocidad con la computadora más lenta que tienes disponible y si obtienes un tiempo de respuesta aceptable.

Artículos relacionados:

Los predicados existenciales

El índice del blog Firebird21

 

Confusiones comunes al declarar una columna como NUMERIC o DECIMAL

6 comentarios

Cuando en una tabla se declara una columna para que sea de tipo NUMERIC o de tipo DECIMAL es muy frecuente que se la declare mal. El error les ocurre más frecuentemente a los desarrolladores que están acostumbrados a usar los lenguajes xBase (dBase, Clipper, FoxPro, etc.)

¿Por qué?

Porque en los xBase si se declara a una columna como:

NUMERIC (5, 3)

eso significa que en esa columnas se guardarán 5 caracteres:

1 dígito en la parte entera

1 punto decimal

3 dígitos en la parte decimal

O sea que el primer número (el 5 en nuestro ejemplo) indica la cantidad total de caracteres incluyendo el punto decimal; y el segundo número (el 3 en nuestro ejemplo) indica la cantidad de decimales. Pero en SQL en general y en Firebird en particular: no se hace así.

Para conocer la forma en que se almacenan las columnas de tipo NUMERIC o DECIMAL en Firebird puedes leer estos dos artículos:

Entendiendo NUMERIC y DECIMAL

Usando NUMERIC y DECIMAL

Veamos algunos ejemplos, si declaramos a una columna como:

NUMERIC(5, 3)

En xBase el máximo valor posible es 9.999; en SQL es 99999.999

NUMERIC(6, 2)

En xBase el máximo valor posible es 999.99; en SQL es 999999.99

NUMERIC(7, 5)

En xBase el máximo valor posible es 9.99999; en SQL es 9999.99999

NUMERIC(10, 6)

En xBase el máximo valor posible es 999.999999; en SQL es 999999999.999999

La forma en que se declaran las columnas de tipo NUMERIC en los xBase es sin duda más fácil de comprender y de recordar, pero en Firebird se hace de otra manera y por supuesto es a la manera de Firebird la que debemos usar cuando declaramos una columna en Firebird.

Si recuerdas como se almacenan internamente las columnas NUMERIC y DECIMAL en Firebird entonces te resultará bastante fácil hallar el valor máximo que puedes almacenar en esas columnas. Si tienes dudas, puedes consultar los dos artículos cuyos enlaces encontrarás más arriba.

Artículos relacionados

Entendiendo NUMERIC y DECIMAL

Usando NUMERIC y DECIMAL

Determinando la precisión y la escala de una columna NUMERIC

El índice del blog Firebird21

 El foro del blog Firebird21

Determinando la precisión y la escala de una columna NUMERIC

4 comentarios

Como seguramente sabes, las columnas de tipo NUMERIC se usan para guardar números que tienen coma fija. Eso significa que la cantidad de decimales es siempre la misma.

Un número de coma flotante puede tener una cantidad infinita de decimales, en cambio un número de coma fija siempre tiene una cantidad fija de decimales. Por ejemplo, el resultado de dividir 1 entre 3 es:

1 / 3 = 0,3333333333333333333333333333333333333333333333…..

Pero en los números de coma fija se determina cuantos decimales tendrán. Por ejemplo, si se desea que tengan dos decimales:

1 / 3 = 0,33

Evidentemente el resultado no es exacto. Y lo podemos ver haciendo la operación inversa:

3 * 0,33 = 0,99

Si el resultado fuera exacto hubiéramos obtenido 1, pero no obtuvimos 1 sino que obtuvimos 0,99. Está muy cerca pero no es exacto.

Es por ese motivo que siempre que trabajamos con columnas de tipo NUMERIC debemos guardar en nuestras tablas al menos un decimal más de los que les mostraremos a los usuarios.

Así, si por ejemplo queremos que en las pantallas y en los informes los precios se vean con dos decimales estaría mal que los definiéramos como:

NUMERIC (8, 2)

porque perderíamos precisión si alguna vez hacemos una división. Lo correcto sería guardarlos como:

NUMERIC (10, 3)

ya que eso nos aseguraría de no perder precisión.

Las columnas de tipo NUMERIC se especifican como p, s

Siendo p la precisión y s la escala.

NUMERIC(10, 4) significa que la precisión es 10 y que la escala es 4.

¿Y hasta qué valores numéricos podemos guardar en columnas de tipo NUMERIC?

Para verificarlo creamos una tabla llamada NUMEROS con esta estructura:

NUMEROS1

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

Y luego le insertamos datos a cada columna, obteniendo estos resultados:

NUMEROS2

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

¿Qué podemos deducir viendo la Captura 2?

Que la escala es siempre la que hemos definido pero la precisión no.

Por ejemplo, NUMERIC(5, 3) tiene tres decimales, NUMERIC(6, 5) tiene cinco decimales. La cantidad de decimales (o sea, la escala) siempre coincide con lo que hemos definido pero la precisión (es decir, la parte entera) no.

NUMERIC(6, 6) tiene seis decimales. Coincide. Pero solamente tiene tres dígitos en la parte entera. No coincide.

¿Qué implica todo esto?

Que es un error creer que la precisión siempre coincidirá con la cantidad de dígitos de la parte entera. Por regla general NO COINCIDIRÁ y cuanto mayor sea la escala (o sea, la cantidad de decimales) menor será el número que se puede encontrar en la parte entera.

Conclusión:

El tipo de datos NUMERIC se utiliza para guardar números de coma fija. O sea números que siempre tendrán la misma cantidad de decimales.

El formato de estos números es NUMERIC (p, s)

siendo p la precisión y s la escala.

La escala s es siempre la definida y por lo tanto siempre sabremos cuantos decimales tendrán los números guardados en esa columna. Pero eso no ocurre con la precisión p ya que ésta va disminuyendo a medida que la escala s va aumentando.

No existe una regla mnemónica para saber cual debe ser la precisión para una caso dado. Por ejemplo, si queremos guardar números que podrán tener hasta 10 dígitos en la parte entera y 6 dígitos en la parte decimal ¿cómo declaramos a esa columna?

Pues mediante “prueba y error”.

Creamos una columna que tenga una precisión p de al menos 10 y una escala s de 6. Luego vamos aumentando la precisión p hasta encontrar el valor buscado.

Artículos relacionados:

Entendiendo NUMERIC y DECIMAL

Usando NUMERIC y DECIMAL

El índice del blog Firebird21

Older Entries