Hello,

I have a performance problem and need to optimize a stored procedure. In
my simple bookkeeping table, each row contains an amount (in Euros) and
the current balance of the bank account as well as the balances of the
respective booking account used in that row (referenced by "ID"). The
table is this (excerpt):

Create Table BookLines
(
LineNum Integer not null,
ID Integer not null,
Amount decimal(17,2) not null,
BankBalance decimal(17,2) not null,
IDBalance decimal(17,2) not null,
Constraint PK_BookLines Primary Key (LineNum)
);

Customers want to be able to modify their data, not just append lines.
So I have written a stored procedure to recalculate all balances in the
whole table. With a few thousand rows in a table, this can become slow.

Can this be optimized or do I need to make a DLL?

CREATE PROCEDURE Recalculate(FromLine INTEGER)
AS
declare variable LineNum Integer;
declare variable maxLineNum Integer;
declare variable currentid Integer;
declare variable thisid Integer;
declare variable Amount decimal(17,2);
declare variable IDBalance decimal(17,2);
declare variable BankBalance decimal(17,2);
BEGIN
BankBalance=0;
FOR SELECT Amount,LineNum
FROM BookLines
ORDER BY LineNum
INTO :Amount,:LineNum
DO BEGIN
BankBalance = BankBalance + Amount;
UPDATE BookLines
SET kontrollsumme = :BankBalance
WHERE LineNum= :LineNum;
END

maxLineNum=LineNum;

IDBalance=0;
currentid=0;
FOR SELECT Amount,ID,LineNum
FROM BookLines
ORDER BY ID,LineNum
INTO :Amount,:thisid,:LineNum
DO BEGIN
if (thisid>currentid) then begin
IDBalance=0;
currentid=thisid;
end
IDBalance=IDBalance+Amount;
UPDATE BookLines
SET IDsumme = :IDBalance
WHERE LineNum= :LineNum;
END
END ^

-----------------------

Thanks.

Tobias Giesen