How to improve performance of a select statement, which calculates a running total-CodePudding

I'm using Firebird 2.5.2 and am trying to figure out, how to improve performance of a select statement, which calculates a running total. Here is the situation:

A table "PAYMENTS" (simplified for a better overview)

CREATE TABLE PAYMENTS 
(
  ID                    INTEGER             NOT NULL,
  PAYMENT_TYPE          SMALLINT            DEFAULT 0,
  BANKING_ACCOUNTID     INTEGER             DEFAULT -100,
  AMOUNT                DOUBLE PRECISION,
  CALCAMOUNT            DOUBLE PRECISION,
  PAYMENT_DATE          TIMESTAMP
);

ALTER TABLE PAYMENTS ADD CONSTRAINT PK_PAYMENTS PRIMARY KEY (ID);
CREATE GENERATOR PAYMENTS_PRIMARYKEY;

CREATE ASC INDEX PAYMENTS_AMOUNT ON PAYMENTS (AMOUNT);
CREATE ASC INDEX PAYMENTS_BANKACCOUNT ON PAYMENTS (BANKING_ACCOUNTID);
CREATE ASC INDEX PAYMENTS_BDATE ON PAYMENTS (PAYMENT_DATE);

ALTER TABLE PAYMENTS ADD CONSTRAINT FK_PAYM_BANKING_ACCOUNTID FOREIGN KEY (BANKING_ACCOUNTID) REFERENCES BANKINGACCOUNTS (ID) ON UPDATE CASCADE ON DELETE SET DEFAULT;

The value in "AMOUNT" is always positive. Whether it's an incoming or outgoing payment, is defined via "PAYMENT_TYPE". Both determine the value in "CALCAMOUNT", i.e. for calculation reasons the value here is either "-100" or "100".

A stored procedure to calculate the sum:

SET TERM  ^^ ;
create procedure SP_BALANCE_FOR_DATE_AND_BANKID (PAYMENT_DATE timestamp, BANKING_ACCOUNTID integer) returns (BALANCE double precision)
as
declare variable TempAmount double precision;
begin
    TempAmount = 0;
 
    select sum(CALCAMOUNT) as SUMME from PAYMENTS where (PAYMENT_DATE < :PAYMENT_DATE) and (BANKING_ACCOUNTID = :BANKING_ACCOUNTID) into TempAmount;
    if (TempAmount is null) then TempAmount = 0;
     
    BALANCE = TempAmount;
    
    suspend;
end
 ^^
SET TERM ;  ^^

The actual select statement:

select PAYMENTS.*,
case when PAYMENT_TYPE = 1 then ((select BALANCE from SP_BALANCE_FOR_DATE_AND_BANKID(PAYMENT_DATE, BANKING_ACCOUNTID))   AMOUNT)
else ((select BALANCE from SP_BALANCE_FOR_DATE_AND_BANKID(PAYMENT_DATE, BANKING_ACCOUNTID)) - AMOUNT) end
as CURRENTBALANCE
from PAYMENTS
where (ID > 0) and (BANKING_ACCOUNTID = :BANKING_ACCOUNTID) and ((PAYMENT_TYPE = 1) or (PAYMENT_TYPE = 2)) and (PAYMENT_DATE >= '01.11.2021') and (PAYMENT_DATE <= '30.11.2021 23:59:59')
order by PAYMENT_DATE

The user can define the bank account and the date range and the results are being showed in a grid, where additionally to all fields from "PAYMENTS" the field "CURRENTBALANCE" is being displayed, which shows the balance / the total at the time after the appropriate payment. Regardless of the defined date range for the records to display, all records before a record (based on PAYMENT_DATE) have to be counted for that record.

The problem: it's very slow, the more records exist.

I've searched prior to posting here and saw the following solutions:

Same as mine, but without the stored procedure, i.e. with a subquery within the select statement
A query with self-joined PAYMENTS table
A query using analytic functions

The performance of the first two is about the same, i.e. very slow. Before testing, I had tried to add more indexes too.

The third one is possible with Firebird 3.0 only, apparently. Since I'm on Firebird 2.5 still, I would like to ask, how I could improve the performance of my select statement?

There is of course the option to store the appropriate total with each record directly once it's being stored, but the problem here is, that once a payments gets deleted or modified, the total of all records after the deleted / modified one becomes incorrect.

CodePudding user response：

You should put whole your query into the stored procedure calculating and returning running total in a simple loop. It will be the fastest way.

Something like this:

CREATE PROCEDURE RUNNING_BALANCE(BIGINT ACCOUNTID, DATE_BEGIN DATE, DATE_END DATE)
RETURNS (BALANCE DOUBLE PRECISION)
AS
DECLARE t SMALLINT;
DECLARE a DOUBLE PRECISION;
BEGIN
 BALANCE = 0;

 select sum(CALCAMOUNT) from PAYMENTS
  where (PAYMENT_DATE < :DATE_BEGIN)
   and (BANKING_ACCOUNTID = :ACCOUNTID)
  into :BALANCE;

 FOR select PAYMENT_TYPE, AMOUNT from PAYMENTS
   where (ID > 0)
    and (BANKING_ACCOUNTID = :ACCOUNTID)
    and ((PAYMENT_TYPE = 1) or (PAYMENT_TYPE = 2))
    and (PAYMENT_DATE >= :DATE_BEGIN)
    and (PAYMENT_DATE < :DATE_END   1)
   order by PAYMENT_DATE
  INTO :t, :a DO
  BEGIN
   IF (t = 1) THEN
     BALANCE = BALANCE   a;
   ELSE
     BALANCE = BALANCE - a;
   SUSPEND;
  END
END