Procedimientos almacenados en SQL

Hace un par de semanas, una aplicación que desarrollé el año pasado dentro de un proyecto de IoT comenzó a fallar.

Era una aplicación que monitoreaba la posición de una pequeña flota de vehículos agrícolas, concretamente 6 tractores.

Cada tractor cuenta con un módulo GPS NEO-6M y una microcontroladora conectada a la batería de 12 Vcc.

Cada microcontroladora envía minutalmente la posición del tractor a un servidor que tengo en Azure.

La visualización de los datos en dashboard, en pantallas de tendencias, en mapa... se hace mediante una aplicación que hice con Angular.

El tema que te traigo es que la aplicación de Angular dejó de mostrar datos, sin embargo, los datos sí estaban en la base de datos.

Lo que estaba ocurriendo, es que el servidor estaba saturado en cuanto a memoria RAM y tardaba mucho en responder.

Ahora mismo está funcionando porque le he dado prioridad al servicio del PostgreSQL, que es el servidor de datos que estoy utilizando en este proyecto.

Además he optimizado los procedimientos almacenados que utilizaba para traer los datos minutales de los 6 tractores con una sola petición SQL-

En este artículo te quiero hablar sobre los procedimientos almacenados, qué son, qué me parecen...

Comencemos por el principio.

¿Qué son los procedimientos almacenados?

Los procedimientos almacenados rutinas que se guardan y ejecutan en un sistema de gestión de bases de datos.

Son secuencias de instrucciones que se almacenan en el servidor de la base de datos y se utilizan para realizar operaciones o tareas específicas en la base de datos.

Los procedimientos almacenados se escriben en un lenguaje de programación especializado que es compatible con el SGBD utilizado, como SQL (Structured Query Language) en la mayoría de los casos.

Pueden aceptar parámetros de entrada y devolver resultados o valores a través de parámetros de salida.

¿Qué ventajas tiene usar los procedimientos almacenados?

La principal ventaja de utilizar procedimientos almacenados es la eficiencia y la reutilización de código.

Al ser programas almacenados en el servidor de la base de datos, se evita la necesidad de enviar instrucciones SQL desde una aplicación cliente hasta el servidor cada vez que se requiere una operación en la base de datos.

Esto reduce la carga de red y mejora el rendimiento general del sistema.

Además, los procedimientos almacenados permiten encapsular la lógica de negocio en la base de datos, lo que proporciona una capa de seguridad y control de acceso adicional.

También facilitan el mantenimiento y la actualización de la lógica empresarial, ya que los cambios se pueden realizar en un único lugar (el procedimiento almacenado) en lugar de modificar múltiples aplicaciones cliente.

¿Qué desventajas tiene usar los procedimientos almacenados?

Históricamente las desventajas que se le suelen achacar a los procedimientos almacenados son:

• Complejidad: La creación y el mantenimiento de procedimientos almacenados pueden ser complejos.

  ❗ No estoy de acuerdo, el aprender a programar procedimientos almacenados es como todo, una vez que sabes lo ves fácil. Como cualquier lenguaje tiene condicionales, bucles...

• Acoplamiento con la base de datos: El uso excesivo de procedimientos almacenados puede aumentar el acoplamiento entre la lógica de la aplicación y la base de datos.

  Aunque es cierto lo del acoplamiento, hasta hoy no he tenido la necesidad de migrar de una base de datos a otra, sin embargo, sí he tenido que migrar las aplicaciones y el hecho de tener el core de la lógica en la base de datos me lo ha hecho más fácil.

• Dificultad de pruebas unitarias: Las pruebas unitarias pueden resultar más complicadas para los procedimientos almacenados.

  Esto puede dificultar la creación de pruebas efectivas y completas para garantizar la calidad del código.

Mi experiencia con los procedimientos almacenados

Hasta la fecha, el uso de los procedimientos almacenados me ha facilitado el desarrollo de aplicaciones.

En redes lentas o con una alta latencia el hecho de minimizar las peticiones al servidor de base de datos gracias a los procedimientos almacenados mejoró notablemente el rendimiento de mis aplicaciones.

Y para el desarrollo es una gozada, cuando aprendo más sobre SQL y optimizo el procedimiento almacenado, para que mi aplicación se beneficia del nuevo rendimiento no tengo que hacer nada en el backend ni en el frontend, modifico el procedimiento almacenado y a correr.

En el debate que existe entre procedimientos almacenados Sí/No mi voto claramente es sí 😉

Procedimientos almacenados en PostgreSQL

En este punto iré haciendo una recopilación de los procedimientos que me resulten más interesantes para mi yo del futuro 🤩

Extrayendo los últimos valores

En una misma tabla tengo valores minutales de contaminación de varios sensores.

Lo que quiero es obtener los últimos valores de unos sensores que le pase como input al procedimientos almacenado.

La instrucción SQL para crear el procedimiento almacenado sería:

            
CREATE OR REPLACE FUNCTION last_pm_values_v3(podsNumber integer[])
RETURNS TABLE (podSerialNumber integer, readingDatestamp timestamp without time zone) AS
$$
DECLARE
    podNumber integer;
    resultado RECORD;
BEGIN
    FOREACH podNumber IN ARRAY podsNumber
    LOOP
        FOR resultado IN
            SELECT pod_serial_number, reading_datestamp 
            FROM schema_gp.tb_pm 
            WHERE pod_serial_number=podNumber 
            ORDER By reading_datestamp DESC
            LIMIT 1
        LOOP            
            podSerialNumber := resultado.pod_serial_number;
            readingDatestamp := resultado.reading_datestamp;
            RETURN NEXT;
        END LOOP;	
    END LOOP;
    
    RETURN;
END;
$$
LANGUAGE plpgsql;

Hasta luego 🖖