Optimización de KPIs en NaturalAloe: De vistas a tablas materializadas

En el proyecto de gestión documental para NaturalAloe, uno de los mayores desafíos fue optimizar la consulta de KPIs para dashboards que se actualizaban constantemente desde el frontend.

El problema inicial

Inicialmente implementé una vista que calculaba los KPIs de documentos (POEs, Políticas, Registros Maestros) en tiempo real. Esta vista realizaba cálculos complejos:

• Porcentajes de documentos vigentes vs obsoletos
• Conteos por áreas y departamentos
• Métricas de cumplimiento documental
• Estados de versionado y obsolescencia

-- Vista muestral por temas de privacidad (cálculos en tiempo real)
CREATE VIEW v_kpis_documentos AS
SELECT 
  (SELECT COUNT(*) FROM poes WHERE estado = 'vigente') * 100.0 / 
  (SELECT COUNT(*) FROM poes) as porcentaje_poes_vigentes,
  
  (SELECT COUNT(*) FROM politicas WHERE estado = 'vigente') * 100.0 / 
  (SELECT COUNT(*) FROM politicas) as porcentaje_politicas_vigentes,
  
  -- Más cálculos complejos...
FROM dual;

El problema: Cada consulta del frontend ejecutaba todos estos cálculos, generando carga computacional excesiva y tiempos de respuesta lentos.

La solución: Tablas materializadas

Paso 1: Creación de la tabla materializada

Creé una tabla que reflejara exactamente los datos que devolvía la vista:

CREATE TABLE t_kpis_materialized (
  id INT PRIMARY KEY AUTO_INCREMENT,
  porcentaje_poes_vigentes DECIMAL(5,2),
  porcentaje_politicas_vigentes DECIMAL(5,2),
  porcentaje_rm_vigentes DECIMAL(5,2),
  total_documentos_obsoletos INT,
  fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_fecha_actualizacion (fecha_actualizacion)
);

Paso 2: Stored Procedure de refresco

Implementé un SP que vacía y rellena la tabla con data fresca:

DELIMITER $$

CREATE PROCEDURE sp_refresh_kpis()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;
  
  START TRANSACTION;
  
  -- Vaciar tabla
  DELETE FROM t_kpis_materialized;
  
  -- Llenar con datos frescos de la vista
  INSERT INTO t_kpis_materialized (
    porcentaje_poes_vigentes,
    porcentaje_politicas_vigentes,
    porcentaje_rm_vigentes,
    total_documentos_obsoletos
  )
  SELECT 
    porcentaje_poes_vigentes,
    porcentaje_politicas_vigentes,
    porcentaje_rm_vigentes,
    total_documentos_obsoletos
  FROM v_kpis_documentos;
  
  COMMIT;
END$$

DELIMITER ;

Paso 3: Automatización inteligente

El SP se ejecuta automáticamente en dos escenarios:

1. Triggers en cambios relevantes:

-- Trigger en tabla de POEs
CREATE TRIGGER tr_poes_after_update
AFTER UPDATE ON poes
FOR EACH ROW
BEGIN
  IF OLD.estado != NEW.estado THEN
    CALL sp_refresh_kpis();
  END IF;
END;

2. Evento programado como respaldo:

-- Evento cada 30 minutos por si algo no se contempló
CREATE EVENT ev_refresh_kpis
ON SCHEDULE EVERY 30 MINUTE
DO CALL sp_refresh_kpis();

Implementación en el backend

En el backend (Node.js + Express), cambié las consultas:

// Antes: Consulta a la vista (lenta)
const getKPIs = async () => {
  const [rows] = await db.query('SELECT * FROM v_kpis_documentos');
  return rows[0];
};

// Después: Consulta a la tabla (instantánea)  
const getKPIs = async () => {
  const [rows] = await db.query(`
    SELECT * FROM t_kpis_materialized 
    ORDER BY fecha_actualizacion DESC 
    LIMIT 1
  `);
  return rows[0];
};

Resultados obtenidos

• Tiempo de respuesta: De ~2.3s a ~50ms (↓95%)
• Carga del servidor: Reducción significativa en uso de CPU durante consultas
• Experiencia de usuario: Dashboards que cargan instantáneamente
• Escalabilidad: Sistema preparado para mayor volumen de consultas

Lecciones aprendidas

1. Identificar el patrón de acceso: Los KPIs se consultaban frecuentemente pero cambiaban poco
2. Triggers selectivos: Solo refrescar cuando realmente hay cambios que afecten los KPIs
3. Evento de respaldo: Siempre tener un mecanismo para casos no contemplados
4. Monitoreo: La tabla incluye timestamp para auditar cuándo fue la última actualización

Consideraciones adicionales

• Consistencia eventual: Los KPIs pueden tener unos segundos de retraso vs datos en tiempo real
• Espacio de almacenamiento: Mínimo, solo una fila con métricas agregadas
• Mantenimiento: El evento programado evita que la data se vuelva obsoleta por casos edge

Esta optimización fue clave para mantener la performance del sistema mientras proporcionaba métricas precisas y actualizadas para la toma de decisiones operativas en NaturalAloe lo anteriores ejemplos se asimilan al trabjo realizado, sin embargo, por temas de privacidad no se puede mostrar la solución real.