Usando NUMERIC y DECIMAL

3 comentarios

En Firebird tenemos dos tipos de datos numéricos que tienen una cantidad fija de lugares decimales: NUMERIC y DECIMAL. También tenemos dos tipos de datos numéricos cuya cantidad de lugares decimales es variable (en Matemática se les dice “flotantes”) que son: FLOAT y DOUBLE PRECISION.

En este artículo nos referiremos a NUMERIC y a DECIMAL, los cuales siempre tienen una cantidad fija de lugares decimales.

A la cantidad total de dígitos se la llama precisión y se la representa con la letra “p“. A la cantidad de lugares decimales se le llama escala (scale, en inglés) y se la representa con la letra “s“.

Por lo tanto, si una columna está definida como NUMERIC(5, 2) se la representa así: ppp.ss

La precisión debe estar entre 1 y 18. La escala debe estar entre 0 y 18. La escala siempre debe ser menor o igual que la precisión, nunca puede ser mayor.

Si quieres, puedes declarar una columna NUMERIC o DECIMAL sin especificar la precisión y en ese caso será convertida a INTEGER.

Lo importante a recordar es que en NUMERIC y en DECIMAL la parte decimal es siempre exacta. Se necesita que la parte decimal sea exacta cuando se trata de dinero o de cualquier otro número que resulta de contar o de realizar operaciones aritméticas sobre unidades.

Como el resultado de multiplicar o dividir números que tienen una parte decimal fija es predecible, es el motivo por el cual se los utiliza cuando se trata de dinero. A tu usuario no le puedes decir: “el cliente Juan Pueblo está debiendo más o menos 100 dólares”, el usuario quiere conocer la cantidad exacta de la deuda de Juan Pueblo, eso de “más o menos 100 dólares” no le va a gustar.

Como tanto NUMERIC como DECIMAL usan una escala se los suele llamar “escalados“. Otros sinónimos son: “números de punto fijo” o “números de coma fija“.

En el estandar SQL-92 tanto NUMERIC como DECIMAL restringen al número almacenado a estar dentro de la escala elegida. La diferencia entre ambos tipos de datos es en la forma en la cual la precisión es restringida. Cuando el tipo de dato es NUMERIC, la precisión es exactamente la declarada. En cambio, cuando el tipo de datos es DECIMAL la precisión es al menos igual a la declarada. Eso significa que NUMERIC es más estricto que DECIMAL.

Sin embargo, en Firebird NUMERIC y DECIMAL son idénticos excepto cuando la precisión es menor que 5. Ambos cumplen con el estandar SQL-92 para el tipo de datos DECIMAL y eso significa que NUMERIC no cumple con el estandar de SQL-92.

Si además de usar Firebird usas o has usado o piensas usar otro motor SQL entonces para el dinero utiliza NUMERIC porque es el recomendado según el estandar SQL-92. Si solamente usas y piensas usar Firebird, entonces puedes usar cualquiera de los dos.

Internamente, Firebird almacena a estos tipos de datos como SMALLINT, como INTEGER, o como BIGINT, dependiendo de la precisión declarada.

La forma de almacenarlos internamente es la siguiente:

  • Se almacena un número entero, o sea que no tiene decimales
  • Se almacena la escala a un factor menor que cero que representa a un exponente de 10

Cuando el número debe ser utilizado en una consulta o en una operación matemática se lo obtiene multiplicando el número entero almacenado por 10 elevado a la escala almacenada.

Ejemplo:

Un número ha sido declarado como NUMERIC(4, 3) y por lo tanto Firebird lo almacena como un SMALLINT.

Quieres guardar en esa columna el número 7,2348 pero como la escala de la columna es 3 y la escala del número que quieres guardar es 4 el Firebird lo redondea a 7,235

Por lo tanto, el Firebird guarda el número 7235 y el número -3

Cuando quieres utilizar ese número el Firebird multiplica 7235 por 10 elevado a la -3 y por lo tanto obtienes 7,235 ya que multiplicar un número por 10 elevado a la -3 es lo mismo que dividirlo por 1000. Y 7235 dividido por 1000 es igual a 7,235

Rango de valores

Tipo de datos                Precisión    Almacenado como

NUMERIC             1 a 4    SMALLINT
DECIMAL             1 a 4    INTEGER
NUMERIC y DECIMAL   5 a 9    INTEGER
NUMERIC y DECIMAL  10 a 18   BIGINT

El tipo de datos NUMERIC

El formato del tipo de datos NUMERIC es:

NUMERIC(p, s)

Por ejemplo, NUMERIC(4, 2) define a un número que puede tener como máximo 4 dígitos, de los cuales 2 de ellos sí o sí corresponderán a la parte decimal.

Eso significa que el número 1,23 será almacenado como 1,23 pero el número 1,234 será almacenado como 1,23 porque la escala es 2 y por lo tanto no puede almacenarse con más de 2 dígitos decimales. También 1,2345678 será almacenado como 1,23. El número 567,89 no podrá ser almacenado porque la parte entera tiene 3 dígitos y solamente debería tener 2 dígitos, si intentamos almacenarlo el Firebird nos mostrará un error de overflow (número mayor que el máximo permitido para esa columna).

Sin embargo, como NUMERIC se guarda internamente como un SMALLINT y en SMALLINT se puede guardar desde el número -32.768 hasta el número 32.767 al declarar a una columna como NUMERIC(4, 2) el mayor número que podemos almacenar en ella no es 99,99 como podría suponerse viendo la declaración sino 327,67. Cuidado con esto porque NUMERIC no cumple el estándar SQL-92 ya que según ese estándar el mayor número tendría que ser 99,99.

El tipo de datos DECIMAL

El formato del tipo de datos DECIMAL es:

DECIMAL(p, s)

Similarmente a NUMERIC el mayor número que puede guardarse en una columna de este tipo no es el declarado. Por ejemplo si una columna se declara como DECIMAL(4, 1) uno podría pensar que el máximo número permitido sería 999,9 pero sin embargo el mayor número permitido es 214.748.364,7 y el menor número permitido es -214.748.364,8

¿Por qué eso? porque el tipo de datos DECIMAL se guarda internamente como un INTEGER y el rango de valores de INTEGER es de -2.147.483.648 hasta 2.147.483.647 y como en este ejemplo la escala es 1 entonces para hallar el rango de valores se divide por 10. Si la escala fuera 2 se dividiría por 100, si fuera 3 se dividiría por 1.000 y así sucesivamente.

Advertencia

El hecho de que puedas almacenar en una columna números mayores a los declarados para esa columna no significa que debas hacerlo. En futuras versiones de Firebird este comportamiento puede cambiar para adecuarse al estándar y si estás dependiendo de algo que está fuera del estándar podrás encontrarte con graves problemas más adelante.

Comportamiento de NUMERIC y DECIMAL en operaciones aritméticas

División

La precisión del resultado siempre es 18. La escala del resultado es la suma de las escalas del dividendo y del divisor. Esto implica que la escala del cociente siempre será mayor que las del dividendo y del divisor. Debes tener cuidado con esto si los números que empleas tienen muchos decimales o podrías sobrepasar la escala máxima que es 18 o necesitar una precisión mayor que 18 y en ambos casos eso te ocasionaría un error de overflow.

SELECT
   1234567.89 / 54321.2345
FROM
   RDB$DATABASE

El resultado de esa división es 22,727169 que como puedes ver tiene 6 dígitos decimales. ¿Por qué? porque el dividendo tiene 2 dígitos decimales y el divisor tiene 4 dígitos decimales y si sumas 2 + 4 obtienes 6.

SELECT
   1 / 3
FROM
   RDB$DATABASE

Aquí, uno esperaría que el resultado fuera 0,33333333 sin embargo el resultado es 0 (cero). ¿Por qué? porque tanto el dividendo como el divisor son números enteros y la escala de los números enteros es cero. Y como la escala del cociente es la suma de la escala del dividendo más la escala del divisor esa escala también debe ser cero (0 + 0 = 0).

Si queremos efectuar la división de arriba pero que el resultado tenga dos decimales entonces deberíamos escribir:

SELECT
   1.00 / 3
FROM
   RDB$DATABASE

o también podríamos escribir:

SELECT
   (1 + 0.00) / 3
FROM
   RDB$DATABASE

Multiplicación

Al igual que en la división, la escala del resultado es la suma de las escalas de los operandos.

Suma y Resta

La escala del resultado es la mayor de las dos escalas.

SELECT
   123.45 + 678.9
FROM
   RDB$DATABASE

El resultado de esta suma es 802,35 y como puedes ver la escala es 2. ¿Por qué la escala es 2? porque las escalas de los sumandos son 2 y 1. Como 2 es mayor que 1 entonces la escala del resultado es 2.

La precisión siempre es 18.

Artículos relacionados

Entendiendo NUMERIC y DECIMAL

Confusiones comunes al declarar una columna como NUMERIC o DECIMAL

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

Eligiendo el tipo de datos numérico que se usará en una columna

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

¿Por qué esta consulta a la Base de Datos está lenta?

1 comentario

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/