Optimizando SuperServer: poniendo toda la Base de Datos en la memoria caché

5 comentarios

Hay un truco que podemos utilizar para optimizar a SuperServer, funciona también en Classic pero solamente con bases de datos mucho más pequeñas.

Como recordarás, si las filas que devolverá un SELECT ya se encuentran en la memoria caché entonces la velocidad de respuesta será muy alta porque la memoria RAM es muchísimo más rápida que el disco duro. Entonces, la idea es tener a toda la Base de Datos (o al menos a las tablas más utilizadas cuando tener a toda la Base de Datos no es posible) en la memoria caché.

Para poner a toda la Base de Datos en la memoria caché podríamos ejecutar el siguiente stored procedure:

Listado 1.

CREATE PROCEDURE LLENAR_CACHE
AS
   DECLARE VARIABLE lcNombreTabla   VARCHAR(1024);
   DECLARE VARIABLE lcComando       VARCHAR(1024);
   DECLARE VARIABLE lnCantidadFilas INTEGER;
BEGIN
   
   FOR SELECT
      RDB$RELATION_NAME
   FROM
      RDB$RELATIONS
   INTO
      :lcNombreTabla
   DO BEGIN
      lcComando = 'SELECT COUNT(*) FROM ' || lcNombreTabla;
      EXECUTE STATEMENT lcComando INTO :lnCantidadFilas;
   END

END;

Luego de ejecutar el comando EXECUTE PROCEDURE LLENAR_CACHE y el comando COMMIT, toda la Base de Datos se encontrará en la memoria caché (si hay suficiente espacio en la memoria caché, por supuesto). ¿Por qué? Porque cada vez que se lee una página de una tabla esa página es puesta en la memoria caché. La función COUNT(*) lee cada página de una tabla y por consiguiente coloca a cada página de esa tabla en la memoria caché. El stored procedure LLENAR_CACHE realiza esa operación para todas las tablas de la Base de Datos. Como resultado final, todas las páginas de todas las tablas se encontrarán en la memoria caché, si es que hay allí suficiente espacio libre.

¿Y si no hay suficiente espacio libre?

Entonces podríamos poner en la memoria caché a las tablas más utilizadas en los informes.

Listado 2.

CREATE PROCEDURE LLENAR_CACHE
AS
BEGIN
   SELECT COUNT(*) FROM BANCOS;
   SELECT COUNT(*) FROM PRODUCTOS;
   SELECT COUNT(*) FROM CLIENTES;
   SELECT COUNT(*) FROM PROVEEDORES;
   SELECT COUNT(*) FROM STOCK;
   SELECT COUNT(*) FROM MONEDAS;
END; 

Eso hará que todas las operaciones de lectura se realicen súper rápido. Pero durante el trabajo normal diario muchas operaciones de INSERT, UPDATE, y DELETE se irán ejecutando y en la memoria caché ya no se encontrarán las últimas filas insertadas, actualizadas, y borradas.

¿Y entonces?

Entonces lo que debemos hacer es volver a ejecutar el stored procedure LLENAR_CACHE para que nuevamente en la memoria caché se encuentre toda la Base de Datos (como en el caso del Listado 1.) o las tablas más utilizadas (como en el caso del Listado 2.).

Ese proceso podríamos hacerlo manualmente (por ejemplo, cada hora) pero es mucho más inteligente pedirle al Sistema Operativo que realice esa tarea.

Para ello crearemos un archivo de script y un archivo batch.

Listado 3.

CONNECT MiBaseDatos.FDB USER SYSDBA PASSWORD masterkey;

-- Primera vez
EXECUTE PROCEDURE LLENAR_CACHE;
COMMIT;
SHELL PING 192.0.2.2 -n 1 -w 36000 > NUL;

-- Segunda vez
EXECUTE PROCEDURE LLENAR_CACHE;
COMMIT;
SHELL PING 192.0.2.2 -n 1 -w 5000 > NUL;

-- Finalizar
EXIT;

Escribimos el contenido del Listado 3. en el bloc de notas y lo grabamos con el nombre LLENAR_CACHE.SQL

¿Qué hace este archivo de script?

Primero, se conecta a nuestra Base de Datos. Segundo, ejecuta el stored procedure llamado LLENAR_CACHE. Tercero, para finalizar la transacción hace un COMMIT. Cuarto, ejecuta el comando PING del Sistema Operativo donde la dirección IP mostrada es una IP que nunca existe y se usa normalmente para pruebas, la opción -n indica la cantidad de repeticiones, la opción -w indica la cantidad de milisegundos (36.000 milisegundos = 1 hora), y al redirigir a NUL no se ve salida en la pantalla.

En el Listado 3. el stored procedure LLENAR_CACHE se ejecutó 2 veces, con una hora de diferencia entre esas ejecuciones. Si la Base de Datos se usará durante 10 horas seguidas cada día, entonces deberíamos escribir 10 veces el EXECUTE PROCEDURE, el COMMIT, y el SHELL. Para que el Listado 3. no sea muy largo solamente escribí 2 veces esos comandos, en tu caso podrías necesitar escribirlos 8 veces, 10 veces, 14 veces, etc.

Listado 4.

C:
CD "\ARCHIVOS DE PROGRAMA\FIREBIRD\FIREBIRD_2_5\BIN"
ISQL -INPUT C:\USERS\WALTER\DESKTOP\LLENAR_CACHE.SQL

¿Qué hace este archivo batch?

Primero, se ubica en la unidad C:. Segundo, se ubica en la carpeta donde se encuentra el programa ISQL.EXE. Tercero, ejecuta el programa ISQL.EXE con la opción -INPUT y el nombre completo del archivo de script.

En síntesis, ejecuta al programa ISQL.EXE pidiéndole que ejecute los comandos que se encuentran en el archivo de script.

¿Cómo ejecutamos ese archivo batch?

Tenemos dos formas:

  • Manualmente
  • Automáticamente

Manualmente sería haciendo clic en él cada día antes de que el primer usuario empiece a trabajar, por ejemplo a las 7:30 de cada día. El problema es que podríamos olvidarnos de hacer ese clic o llegamos tarde al trabajo o estamos de vacaciones, etc.

Automáticamente sería mediante el Programador de Tareas del Windows. Podríamos programar una tarea que se ejecute cada vez que se enciende el Servidor o cada día a las 7:30. También podríamos hacerlo en nuestra aplicación: si detectamos que nadie está usando la Base de Datos entonces ejecutamos el archivo batch.

Conclusión:

Si la arquitectura que usamos es SuperServer entonces podemos conseguir que los resultados de las consultas sean rapidísimos poniendo a toda la Base de Datos en la memoria caché. Si no tenemos suficiente memoria para poner a toda la Base de Datos en la memoria caché, entonces pondremos allí a las tablas más utilizadas.

Dependiendo de nuestro caso crearíamos un stored procedure como el mostrado en el Listado 1. o como el mostrado en el Listado 2.

Luego creamos un archivo de script como el mostrado en el Listado 3., el cual se encargará de llenar la memoria caché. Como los usuarios estarán insertando, actualizando, y borrando filas de las tablas el contenido de la memoria caché se irá quedando desactualizado. Por eso cada hora (o el tiempo que te parezca conveniente) se vuelve a llenar la memoria caché con el contenido actual de cada tabla.

Finalmente, creamos un archivo batch que se encargará de ejecutar al programa ISQL.EXE teniendo como entrada al archivo de script. Ese archivo batch debería ser ejecutado cada día antes de que el primer usuario se conecte a la Base de Datos. Esto podríamos hacerlo manualmente o mucho mejor, automáticamente.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Algunos ejemplos de funciones agregadas

Deja un comentario

En otros artículos ya habíamos visto las funciones agregadas, aquí hay algunos ejemplos más de como usarlas.

SELECT
   SUM(IIF(MOV_CODSUC = 0, MOV_CANTID * MOV_PRECIO, 0)) AS TOTAL0,
   SUM(IIF(MOV_CODSUC = 1, MOV_CANTID * MOV_PRECIO, 0)) AS TOTAL1
FROM
   MOVIMDET

En esta consulta, si el Código de la Sucursal es 0 se realiza la multiplicación y se halla el Total de esa Sucursal. Lo mismo con la Sucursal 1.

SELECT
   COUNT(DISTINCT IIF(MOV_CODSUC = 0, MOV_CANTID, NULL)) AS TOTAL0,
   COUNT(DISTINCT IIF(MOV_CODSUC = 1, MOV_CANTID, NULL)) AS TOTAL1
FROM
   MOVIMDET

En esta consulta contamos cuantas cantidades distintas hay en la Sucursal 0 y en la Sucursal 1 de la tabla MOVIMDET.

SELECT
   COUNT(DISTINCT IIF(MOV_CODSUC = 0, MOV_IDEPRD, NULL)) AS TOTAL0,
   COUNT(DISTINCT IIF(MOV_CODSUC = 1, MOV_IDEPRD, NULL)) AS TOTAL1
FROM
   MOVIMDET

Y en esta consulta contamos cuantos productos distintos hay en la tabla MOVIMDET (la columna MOV_IDEPRD es el Identificador del Producto).

Conclusión:

Lo interesante de los ejemplos es que muestran que dentro de las funciones agregadas se pueden escribir otras funciones.

Artículo relacionado:

El índice del blog Firebird21

 

Ejemplo Nº 051 – ¿Cómo saber si una tabla tiene registros?

4 comentarios

A veces queremos saber si una tabla tiene registros (filas). No nos interesa saber cuantas filas tiene sino si tiene alguna. Hay varias formas de obtener esa información:

Ejemplo 1:

SELECT
   COUNT(*)
FROM
   MiTabla

Esto funciona pero si la tabla tiene muchos millones de registros será muy lento porque el Firebird contará cuantas filas hay en la tabla y nos devolverá el resultado y esto tomará un buen tiempo. Pero no queremos saber cuantas filas hay en la tabla, solamente queremos saber si tiene filas o no y con este método estamos perdiendo mucho tiempo innecesariamente.

Ejemplo 2:

SELECT
   MIN(MiColumna)
FROM
   MiTabla

Si la tabla tiene un índice ascendente sobre la columna “MiColumna” entonces hallar el mínimo de esa columna será muy rápido. Este método es mucho más eficiente que el del Ejemplo 1, el resultado se obtiene mucho más rápido.

Ejemplo 3:

SELECT
   MiColumna
FROM
   MiTabla
WHERE
   MiColumna > 0
ROWS
   1 TO 1

Y este método es el más eficiente de los tres ya que el Firebird ni siquiera necesita llamar a una función sino simplemente verifica si la primera fila tiene un valor mayor que cero y si ese es el caso ya devuelve el resultado porque ROWS 1 TO 1 le dice que devuelva una fila. Desde luego que “MiColumna” debería ser de tipo numérico y todos sus valores mayores que cero. Si ese no es el caso entonces habría que poner una condición que se cumpla siempre, en el 100% de los casos, para que este método sea el más rápido. En general lo conveniente es que “MiColumna” sea la Primary Key, que es una columna autoincremental y por lo tanto siempre tiene valores mayores que cero.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

La función COUNT()

5 comentarios

Esta función agregada nos dice que cantidad de filas cumplen con la condición impuesta.

SELECT
   COUNT(*)
FROM
   PRODUCTOS

Nos dirá cuantas filas tiene la tabla PRODUCTOS.

SELECT
   PRD_PROCED,
   COUNT(*)
FROM
   PRODUCTOS
GROUP BY
   PRD_PROCED

nos dirá la cantidad de productos que hay de cada país de procedencia.

La función COUNT(*) también cuenta a las filas que tienen un valor de NULL en ellas. Para que no sean contadas en lugar de COUNT(*) debemos escribir COUNT(NombreColumna), como en el siguiente ejemplo:

SELECT
   PRD_PROCED,
   COUNT(PRD_PROCED)
FROM
   PRODUCTOS
GROUP BY
   PRD_PROCED

En este caso, se obtendrá la cantidad de productos que hay de cada país de procedencia, pero sin contar a los que tienen un valor de NULL en la columna PRD_PROCED.

 

.