Ejemplo de recursión (7). Números faltantes en una serie

1 comentario

Si en una tabla o en una vista o en un procedimiento almacenado seleccionable, tenemos una columna numérica y queremos saber si están todos los números o si falta alguno, podemos usar la técnica mostrada en este artículo para averiguarlo.

Para ello, mediante recursión crearemos una tabla virtual, que en nuestro ejemplo llamaremos RANGO_NUMEROS. Esa tabla virtual contendrá todos los números que nos interesan, de forma consecutiva. Es decir: 1, 2, 3, 4, 5, 6, 7, 8, 9, …

Desde luego que podemos empezar con cualquier número, no es obligatorio que empecemos con el número 1.

La tabla es virtual porque solamente existe en la memoria de la computadora y mientras dure la ejecución del SELECT principal, no existe dentro de la Base de Datos ni tampoco se guarda en el disco duro, solamente existe hasta que el SELECT principal finaliza, luego … desaparece totalmente.

Listado 1. Un SELECT para averiguar si hay números consecutivos faltantes

WITH RECURSIVE RANGO_NUMEROS AS (

   SELECT
      1 AS NUMERO
   FROM
      RDB$DATABASE

   UNION ALL

   SELECT
      NUMERO + 1 AS NUMERO
   FROM
      RANGO_NUMEROS
   WHERE
      NUMERO <= 36
)

SELECT
   NUMERO
FROM
   RANGO_NUMEROS
LEFT JOIN
   REVALUOSCAB
      ON NUMERO = RVC_IDENTI
WHERE
   RVC_IDENTI IS NULL

Como siempre que usamos recursión, debemos asignar el valor inicial (en nuestro ejemplo, es el número 1, pero puedes elegir otro número si quieres) y un valor final (en nuestro ejemplo, es 36).

El valor final es absolutamente necesario establecerlo porque de lo contrario la recursión continuaría indefinidamente. Bueno, en realidad, hasta que llegues al límite de recursiones permitidas o hasta que la computadora se quede sin memoria RAM.

Siempre que uses recursión debes establecer una condición de salida, es decir, una condición para que la recursión finalice. No tendría sentido de otro modo.

¿Que hicimos en el Listado 1.?

Primero, hemos creado una tabla virtual llamada RANGO_NUMEROS, cuyo contenido es una sola columna, llamada NUMERO, y cuyos valores van desde el 1 hasta el 37 de forma consecutiva, es decir sin que falte algún número. Están todos. Va hasta el 37 porque en el SELECT pusimos NUMERO + 1. Y como en el WHERE pusimos 36, entonces obtendremos un número más, en este caso 37.

Segundo, hemos hecho un LEFT JOIN de nuestra tabla virtual llamada RANGO_NUMEROS con la tabla REVALUOSCAB, la cual tiene los números que queremos verificar.

Tercero, pusimos la condición RVC_IDENTI IS NULL para que solamente nos muestre los números que están en la tabla virtual RANGO_NUMEROS y que no están en la tabla REVALUOSCAB. De esta manera, solamente los números que se encuentren en la tabla virtual RANGO_NUMEROS y que no se encuentren en la tabla REVALUOSCAB obtendremos en el resultado.

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

En la Captura 1. vemos el contenido de la columna RVC_IDENTI de la tabla REVALUOSCAB. Como puedes notar, faltan los números que van del 26 al 36.

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

Después de ejecutar el Listado 1. obtenemos como resultado lo que vemos en la Captura 2., es decir, todos los números faltantes.

Conclusión:

Hay varias técnicas para mostrar los números que faltan en una serie, en este artículo hemos visto una de esas técnicas, la cual emplea recursión. Saber usar recursión puede ayudarte en muchos casos, por lo tanto es muy bueno conocer como usarla.

Artículos relacionados:

Stored procedures recursivos

Entendiendo a las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

Usando recursividad con CTE

Ejemplo de recursión (1). Filas salteadas

Ejemplo de recursión (2). Numerar filas

Ejemplo de recursión (3). Fechas consecutivas

Ejemplo de recursión (4). Actualizando filas recursivamente

Ejemplo de recursión (5). Saldos acumulados

Ejemplo de recursión (6). Repitiendo las filas

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Agregando filas adicionales

7 comentarios

Un lector del foro de este blog hizo un pedido interesante: mostrar una consulta de una forma especial.

CIUDADES

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

O sea que se tienen 3 tablas: PAISES, ESTADOS, CIUDADES, relacionadas mediante sus identificadores, tal y como debe ser.

Pero ¿cómo hacemos para mostrar las filas de la forma pedida?

Hay varias alternativas, en este artículo mostraremos una de ellas.

La idea es agregar a la tabla de CIUDADES, dos columnas:

  • Si mostraremos el nombre del País
  • Si mostraremos el nombre del Estado

Nuestras tablas por lo tanto tendrán las siguientes estructuras:

CIUDADES2

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

CIUDADES3

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

CIUDADES4.png

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

Los datos contenidos en esas tablas podemos ver a continuación:

CIUDADES6

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

CIUDADES7

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

CIUDADES8

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

Una consulta simple sería la siguiente:

Listado 1.

SELECT
   PAI_NOMBRE,
   EST_NOMBRE,
   CIU_NOMBRE
FROM
   CIUDADES
JOIN
   PAISES
      ON CIU_IDEPAI = PAI_IDENTI
JOIN
   ESTADOS
      ON CIU_IDEPAI = EST_IDEPAI AND
         CIU_IDEEST = EST_IDENTI
ORDER BY
   CIU_IDEPAI,
   CIU_IDEEST,
   CIU_IDENTI

CIUDADES5

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

Pero eso no es lo que queremos obtener, porque los nombres de los Países y de los Estados están repetidos, y lo que queremos es verlos una sola vez.

¿Y entonces, qué hacemos?

Si nos fijamos en la Captura 1. veremos que se muestran en total 18 filas aunque en la tabla de CIUDADES solamente hay 12 filas. Eso significa que se están mostrando 6 filas más. Por lo tanto, lo que debemos hacer es agregar esas 6 filas adicionales.

¿Y cómo lo conseguimos?

Mediante una construcción del Firebird que se llama stored procedure seleccionable.

Listado 2.

CREATE PROCEDURE SP_MOSTRAR_CIUDADES
   RETURNS(
      ftcNombrePais   TYPE OF COLUMN PAISES.PAI_NOMBRE,
      ftcNombreEstado TYPE OF COLUMN ESTADOS.EST_NOMBRE,
      ftcNombreCiudad TYPE OF COLUMN CIUDADES.CIU_NOMBRE)
AS
   DECLARE VARIABLE lcNombrePais    TYPE OF COLUMN PAISES.PAI_NOMBRE;
   DECLARE VARIABLE lcNombreEstado  TYPE OF COLUMN ESTADOS.EST_NOMBRE;
   DECLARE VARIABLE lcNombreCiudad  TYPE OF COLUMN CIUDADES.CIU_NOMBRE;
   DECLARE VARIABLE lcMostrarPais   TYPE OF COLUMN CIUDADES.CIU_MOSPAI;
   DECLARE VARIABLE lcMostrarEstado TYPE OF COLUMN CIUDADES.CIU_MOSEST;
BEGIN

   FOR SELECT
      PAI_NOMBRE,
      EST_NOMBRE,
      CIU_NOMBRE,
      CIU_MOSPAI,
      CIU_MOSEST
   FROM
      CIUDADES
   JOIN
      PAISES
         ON CIU_IDEPAI = PAI_IDENTI
   JOIN
      ESTADOS
         ON CIU_IDEPAI = EST_IDEPAI AND
            CIU_IDEEST = EST_IDENTI
   ORDER BY
      CIU_IDEPAI,
      CIU_IDEEST,
      CIU_IDENTI
   INTO
      :lcNombrePais,
      :lcNombreEstado,
      :lcNombreCiudad,
      :lcMostrarPais,
      :lcMostrarEstado
   DO BEGIN
      IF (lcMostrarPais = 'T') THEN BEGIN
         ftcNombrePais   = lcNombrePais;
         ftcNombreEstado = '';
         ftcNombreCiudad = '';
         SUSPEND;
      END
      IF (lcMostrarEstado = 'T') THEN BEGIN
         ftcNombrePais   = '';
         ftcNombreEstado = lcNombreEstado;
         ftcNombreCiudad = '';
         SUSPEND;
      END
      ftcNombrePais   = '';
      ftcNombreEstado = '';
      ftcNombreCiudad = lcNombreCiudad;
      SUSPEND;
   END

END;

Explicación:

Como necesitamos agregar 6 filas lo podemos conseguir fácilmente usando un stored procedure seleccionable.

Obtenemos y colocamos en variables locales los nombres de los Países, de los Estados, de las Ciudades, y si debemos mostrar el País, y si debemos mostrar el Estado.

Luego, colocamos en los parámetros de salida los valores que deseamos sean devueltos.

Como el stored procedure es seleccionable entonces debemos usar el comando SUSPEND cada vez que deseamos devolver los parámetros de salida.

Listado 3.

SELECT
   *
FROM
   SP_MOSTRAR_CIUDADES

Un stored procedure seleccionable puede ser tratado como si fuera una tabla. Al ejecutar el Listado 3. obtendremos el siguiente resultado.

CIUDADES9

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

Que es exactamente lo que deseábamos obtener.

Artículos relacionados:

Entendiendo a los stored procedures

Agregando filas adicionales (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

Eligiendo el tamaño adecuado de las páginas de la Base de Datos

Deja un comentario

En este artículo ya hemos visto lo que son las páginas de la Base de Datos:

Entendiendo las páginas de la Base de Datos

y sabemos que esas páginas pueden tener 3 tamaños posibles:

  • 4096 bytes
  • 8192 bytes
  • 16384 bytes

¿hay alguna diferencia en el rendimiento si la Base de Datos tiene alguno de esos tamaños de página?

Sí, si tiene el tamaño adecuado entonces todas las operaciones serán más rápidas (a veces, bastante más rápidas) que si tiene un tamaño inadecuado. Las operaciones que realiza el Firebird (INSERT, UPDATE, DELETE, SELECT, FETCH) siempre afectan a una o más páginas de la Base de Datos, por lo tanto utilizar el tamaño de página adecuado es importante

entonces la pregunta ahora es ¿cuál de esos tres tamaños es el más adecuado para mi Base de Datos?

 Pues bien, la respuesta más simple es “prueba y error”. O sea, pruebas con un tamaño, luego pruebas con otro, y luego pruebas con el tercero. Comparas los desempeños y eliges el que te pareció mejor.

Desde luego que “prueba y error” es una posibilidad. Como hay solamente 3 tamaños distintos entonces es factible de realizar. Sin embargo, podemos mejorar un poco nuestro análisis para determinar el tamaño más conveniente.

  1. Tamaño de la Base de Datos. Si alguna tabla tiene o tendrá más de 100.000.000 de filas entonces elige un tamaño de página de 16384 bytes porque en tablas tan grandes los índices también serán gigantescos y por lo tanto tendrán mucha profundidad (el Firebird usa índices B-Tree, y en tales índices una profundidad mayor que 3 empieza a ser problemática).
  2. El tamaño del caché que usa la Base de Datos. Las bases de datos de Firebird tienen una memoria caché, es decir usan una porción de la memoria RAM para realizar sus procesos. Un error frecuente de los principiantes es pensar “cuanto más grande el caché, mejor”. Bien, eso no es tan así. Si fuera tan sencillo entonces el Firebird por su propia cuenta se asignaría el caché más grande posible. En un Sistema Operativo de 32 bits la mayor cantidad de memoria que puede ser direccionada es de 4 Gb (o sea, 2 elevado a la 32), pero el Windows limita esa cantidad, para que un solo proceso no esté usando toda la memoria. Por defecto, un proceso puede usar como máximo 2 Gb aunque en el archivo CONFIG.INI puede cambiarse hasta 3 Gb. Si usamos SuperServer y en el archivo FIREBIRD.CONF ponemos en la entrada DefaultDbCachePages el número 100000 y el tamaño de nuestras páginas es de 16384 bytes entonces el caché de cada Base de Datos consumirá 1.6 Gb. Lo cual implica que podremos tener abierta una sola Base de Datos, porque 1.6 Gb por 2 es 3.2 Gb, que sobrepasa el máximo de 3 Gb que el Sistema Operativo nos permite direccionar. Pero lo peor es que un caché tan gigantesco tampoco nos asegura que nuestras operaciones serán rapidísimas ¿por qué? porque el propio Sistema Operativo usa su propio caché en operaciones repetitivas de lectura en disco y por lo tanto no se usará el caché del Firebird, ocupará mucha memoria pero no se lo usará ¿Y entonces? bueno, en general un tamaño de página de 16384 bytes y un tamaño de caché moderado (o sea, alrededor de 20000) es lo más adecuado.
  3. Cantidad de filas por página. A mayor tamaño de la página, mayor cantidad de filas se pueden guardar en ella y por lo tanto la Base de Datos necesita de menos páginas. Lo normal es que si una Base de Datos tiene pocas páginas sea menos propensa a corromperse que si tiene muchas páginas. En consecuencia, un tamaño de página de 16384 bytes es preferible porque será más difícil que la Base de Datos se corrompa.
  4. Tamaño del clúster. Cuando se formatea un disco duro se debe elegir el tamaño del clúster, el cual en NTFS es de 512 bytes por defecto pero puede ser cambiado.

Si el tamaño de la página es mayor que el del clúster entonces cuando se quiere leer una página se debe leer más de un clúster desde el disco duro y eso es lento. Por ejemplo:

Tamaño de la página = 4096 bytes

Tamaño del clúster = 512 bytes

implica que leer una sola página de la Base de Datos requiere leer 8 clústers en el disco duro (ya que 512 * 8 = 4096). Lo mismo cuando se quiere escribir en una página, se requerirá escribir en 8 clústers. Y si los clústers no están contiguos eso hará aún más lenta a la operación (nosotros no podemos saber si estarán contínuos o no, porque eso es de incumbencia del Sistema Operativo).

 Si el tamaño de la página es menor que el tamaño del clúster a veces puede ser beneficioso cuando se lee, sin embargo cuando se escribe se tardará más. Por ejemplo:

Tamaño de la página = 4096 bytes

Tamaño del clúster = 8192 bytes

Como el Sistema Operativo no puede leer menos que un clúster, un clúster es lo mínimo que puede leer desde el disco duro, cada vez que lea un clúster estará trayendo 2 páginas. Eso puede ser bueno si necesitaremos luego los datos que están en la segunda página pero si no es así entonces se leyeron 4096 bytes inútiles ¿por qué? porque los primeros 4096 bytes sí los usamos, esos fueron los que pedimos, pero los siguientes 4096 nunca los usamos y por lo tanto fueron leídos inutilmente. A su vez, cuando necesitemos escribir lo haremos por duplicado porque cuando escribamos en la primera página escribiremos en el clúster y cuando escribamos la segunda página también escribiremos en el clúster.

¿Lo mejor?

Que el tamaño de la página y el tamaño del clúster sean iguales.

El tamaño adecuado puede cambiar con el tiempo

Un punto muy, pero muy importante a tener en cuenta es el siguiente: el mejor tamaño de página hoy puede no ser el mejor dentro de un mes o dentro de un año.

¿Por qué?

Porque las bases de datos son dinámicas, no son estáticas, constantemente se les están insertando, actualizando, y borrando filas. Un tamaño de página excelente cuando la Base de Datos tenía una tamaño de 50 Mb puede ser horrible cuando creció hasta tener un tamaño de 2 Gb.

Así que debemos recordar que a veces cambiar el tamaño de las páginas puede ser una muy buena alternativa para que todas las operaciones se realicen más rápidamente.

Conclusión:

Si nuestra Base de Datos tiene un tamaño de página adecuado entonces todas las operaciones que se realicen en ella (INSERT, UPDATE, DELETE, SELECT, FETCH) serán rápidas. Pero si no es así, entonces esas operaciones serán más lentas de lo que deberían.

Como hay solamente 3 tamaños de página posibles entonces es muy fácil realizar tests de “prueba y error”. Sin embargo, también podemos tener en cuenta algunos parámetros para hallar el tamaño de página más adecuado y arriba se detallan esos parámetros.

Algo importante a tener en cuenta es que el tamaño del clúster del disco duro debe ser igual al tamaño de la página de la Base de Datos, para conseguir el máximo rendimiento posible.

Artículos relacionados:

Entendiendo las páginas de la Base de Datos

El índice del blog Firebird21

El foro del blog Firebird21

¿Cuándo se actualiza la estructura de una tabla?

4 comentarios

Si con un programa cambias la estructura de una tabla y con otro programa quieres usar esos cambios notarás que a veces no puedes hacerlo.

Veamos un ejemplo:

El usuario ejecuta su programa de Contabilidad y está trabajando con ese programa. Quiere introducir el nombre de un Proveedor y la columna le queda corta, digamos que solamente puede introducir 25 caracteres y él necesita introducir 31. Te llama y tú le dices: “ok, no hay problema, ya te actualizo la estructura de la tabla”.

Entonces vas, abres el EMS SQL Manager (o cual sea tu administrador gráfico), y cambias el ancho de la columna a 40, compilas la tabla, y está todo ok. Haces una prueba modificando esa columna en una fila cualquiera para que tenga 40 caracteres, le haces el COMMIT correspondiente y todo bien, funcionó perfecto. Desde luego, para terminar tu prueba borras los caracteres que le habías agregado a esa columna para probar, para que no queden registrados.

Ya has comprobado que aumentar el tamaño de la columna funcionó perfectamente entonces le llamas al usuario, y le dices: “ya está, ya podrás introducir hasta 40 caracteres en esa columna”.

El usuario feliz intenta nuevamente grabar los 31 caracteres que necesitaba grabar y … ¡¡¡NO FUNCIONA!!!

Te llama y te dice: “sigue sin funcionar, está igual que antes”

Entonces tú como eres muy educado a él no le dices una palabra pero dentro tuyo piensas: “¡¡¡la regranp…que lo recontraparió!!! ¡¡¡CÓMO QUE NO FUNCIONA!!! ¡¡¡Acabo de probar y funcionaba perfecto!!!”

Vuelves a probar y funciona bien. El usuario vuelve a probar y no le funciona.

¿Qué está pasando, cuál es el problema?

Que en tu programa los cambios sí funcionan, pero en el programa del usuario no. ¿Por qué?

Porque tú mantienes abierta a la Base de Datos en tu EMS SQL Manager (o cual sea tu administrador gráfico) y por lo tanto el usuario no se enterará de los cambios que hiciste hasta que no cierres la Base de Datos o salgas de ese programa.

Y en ocasiones, puede requerirse que el usuario también salga de su programa y vuelva a entrar.

Conclusión:

Que tú hagas un cambio a la estructura de una tabla no significa que al instante todos los usuarios se enteran de ese cambio, no es así. Para que se enteren debes salir del programa que usaste para realizar los cambios (el EMS SLQ Manager, el IBEXPERT, el FlameRobin, etc.) y en ocasiones el usuario también deberá salir del programa que él estaba usando. Solamente después de eso los cambios a la estructura de la tabla serán visibles.

Así que si haces cambios a una tabla y el usuario no ve esos cambios, no te desesperes. Simplemente sal de tu administrador gráfico y pídele al usuario que también salga del programa que estaba usando; cuando vuelva a entrar el asunto estará solucionado.

Artículo relacionado:

El índice del blog Firebird21

El comando INSERT

Deja un comentario

El comando INSERT (insertar) se utiliza para agregarle nuevas filas a una tabla. Tiene dos sintaxis:

Sintaxis 1:

INSERT INTO tabla | vista (<lista de columnas>)
VALUES (<lista de valores>)
[RETURNING <lista de columnas a retornar>
[INTO <lista con el nombre de las variables>]]

Se utiliza cuando se quiere agregar una fila a la tabla o vista.

Ejemplo:

INSERT INTO ALUMNOS
        (ALU_CODIGO, ALU_NOMBRE, ALU_APELLD)
VALUES  ('1253', 'MIRTHA', 'ROMERO');

Sintaxis 2:

INSERT INTO tabla | vista (<lista de columnas>)
SELECT <lista de valores> FROM ...
[RETURNING <lista de columnas a retornar>
[INTO <lista con el nombre de las variables>]]

 Ejemplo:

INSERT INTO GERENTES
         (GER_CODIGO, GER_NOMBRE)
   SELECT E.EMP_CODIGO, E.EMP_NOMBRE FROM EMPLEADOS E WHERE E.EMP_CARGOX = 'GERENTE'

Esta segunda sintaxis se utiliza cuando se quieren agregar muchas filas de una sola vez.

Si los valores que queremos agregarle a una tabla se encuentran en otra u otras tablas, esta segunda sintaxis es la adecuada porque es muy rápida.

Artículo relacionado:

El índice del blog Firebird21