Cómo mejorar el rendimiento de una sentencia SELECT que calcula un total acumulado.

15 Jun, 2023 Programación 0

Estoy usando Firebird 2.5.2 y estoy tratando de averiguar cómo mejorar el rendimiento de una declaración SELECT que calcula un total acumulado. Aquí está la situación:

Una tabla “PAGOS” (simplificada para una mejor visión general)

El valor en “CANTIDAD” siempre es positivo. Si se trata de un pago entrante o saliente, se define a través de “TIPO_DE_PAGO”. Ambos determinan el valor en “CANTIDAD_CALCULADA”, es decir, por razones de cálculo, el valor aquí es either “-100” o “100”.

Un procedimiento almacenado para calcular la suma:

El usuario puede definir la cuenta bancaria y el rango de fechas y los resultados se muestran en una cuadrícula, donde además de todos los campos de “PAGOS”, se muestra el campo “SALDO_ACTUAL”, que muestra el saldo / el total en el momento pasado el pago correspondiente. Independientemente del rango de fechas definido para los registros a mostrar, se deben contar todos los registros anteriores a un registro (basado en PAYMENT_DATE) para ese registro.

El problema: es muy lento, cuanto más registros existen.

He buscado antes de publicar aquí y vi las siguientes soluciones:
– Igual que la mía, pero sin el procedimiento almacenado, es decir, con una subconsulta dentro de la declaración SELECT.
– Una consulta con la tabla PAGOS autoasociada.
– Una consulta que utiliza funciones analíticas.

El rendimiento de los dos primeros es aproximadamente el mismo, es decir, muy lento. Antes de realizar pruebas, también había intentado agregar más índices.

El tercero es posible solo con Firebird 3.0, aparentemente. Dado que todavía estoy en Firebird 2.5, me gustaría preguntar cómo podría mejorar el rendimiento de mi declaración SELECT.

Por supuesto, existe la opción de almacenar el total correspondiente con cada registro directamente una vez que se almacena, pero el problema aquí es que una vez que se elimina o modifica un pago, el total de todos los registros posteriores al eliminado / modificado se vuelve incorrecto.

SELECT p.*, SUM(CASE WHEN p2.PAYMENT_TYPE = 1 THEN p2.CALCAMOUNT ELSE -p2.CALCAMOUNT END) + p.CALCAMOUNT AS CURRENTBALANCE FROM PAYMENTS p LEFT JOIN PAYMENTS p2 ON p2.BANKING_ACCOUNTID = p.BANKING_ACCOUNTID AND p2.PAYMENT_DATE < p.PAYMENT_DATE WHERE p.BANKING_ACCOUNTID = :BANKING_ACCOUNTID AND p.PAYMENT_TYPE IN (1,2) AND p.PAYMENT_DATE BETWEEN '01.11.2021' AND '30.11.2021 23:59:59' GROUP BY p.ID, p.PAYMENT_TYPE, p.BANKING_ACCOUNTID, p.AMOUNT, p.CALCAMOUNT, p.PAYMENT_DATE ORDER BY p.PAYMENT_DATE

Answer

davy.ai

June 15, 2023 at 4:18 pm
Como experto en TI especializado en SQL y Firebird, hay algunos pasos que puedes tomar para mejorar el rendimiento de la declaración select que calcula un total acumulado en Firebird 2.5.2.
1. Usar índices apropiados: Ya has creado índices en las columnas relevantes, pero podrías tratar de agregar un índice compuesto en las columnas utilizadas en la cláusula WHERE de la declaración select, es decir (ID, BANKING_ACCOUNTID, PAYMENT_TYPE, PAYMENT_DATE).
2. Reescribir el procedimiento almacenado: En lugar de usar un procedimiento almacenado, podrías tratar de reescribir la consulta como una declaración select anidada. Esto eliminaría la necesidad de llamar al procedimiento almacenado para cada fila en el conjunto de resultados.
Aquí tienes un ejemplo de cómo podrías reescribir la declaración select:
```
SELECT p.*,
SUM(CASE WHEN p2.PAYMENT_TYPE = 1 THEN p2.CALCAMOUNT ELSE -p2.CALCAMOUNT END) + p.CALCAMOUNT AS CURRENTBALANCE
FROM PAYMENTS p
LEFT JOIN PAYMENTS p2 ON p2.BANKING_ACCOUNTID = p.BANKING_ACCOUNTID AND p2.PAYMENT_DATE < p.PAYMENT_DATE
WHERE p.BANKING_ACCOUNTID = :BANKING_ACCOUNTID AND p.PAYMENT_TYPE IN (1,2) AND p.PAYMENT_DATE BETWEEN '01.11.2021' AND '30.11.2021 23:59:59'
GROUP BY p.ID, p.PAYMENT_TYPE, p.BANKING_ACCOUNTID, p.AMOUNT, p.CALCAMOUNT, p.PAYMENT_DATE
ORDER BY p.PAYMENT_DATE
```
En esta consulta, hacemos una unión izquierda de la tabla de pagos consigo misma en Banking_AccountID y Payment_Date, y sumamos el total acumulado utilizando una declaración CASE en lugar del procedimiento almacenado.
1. Actualizar a Firebird 3.0: Como mencionaste, Firebird 3.0 ofrece soporte para funciones analíticas, lo que podría mejorar potencialmente el rendimiento. Si la actualización es una opción, podrías explorar esta ruta.
En resumen, para mejorar el rendimiento de la declaración select que calcula un total acumulado en Firebird 2.5.2, podrías usar índices apropiados, reescribir el procedimiento almacenado como una declaración select anidada o actualizar a Firebird 3.0 para aprovechar las funciones analíticas.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Cómo mejorar el rendimiento de una sentencia SELECT que calcula un total acumulado.

Answer

davy.ai