I’have created a procedure to update againg balances daily on the whole table which is below. There are over 50 thousand records in the table and it takes over 30 min to update all records.
What is the best way to update aging balances on daily basis?
CREATE DEFINER = ‘root’@‘localhost’ PROCEDURE Aging
()
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT ‘’
BEGIN
DECLARE pCode Varchar(20);
Declare done int default 0;
DECLARE cur1 CURSOR FOR SELECT ClientCode FROM Client;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO pCode;
IF NOT done THEN
Update client set Bal = (Select Sum(TotalDue) from invoices where Invoices.ClientCode = pCode and DateDiff(CurDate(),Date(InvDate)) =0) where Client.ClientCode = pCode;
End If;
UNTIL done END REPEAT;
CLOSE cur1;
END;