Aging procedure on daily basis

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;

Well as far as I can tell this query should do all the work of your procedure:

UPDATE 
   client c 
SET 
   Bal = 
   (SELECT 
       Sum(TotalDue) 
    FROM 
       invoices 
    WHERE 
        Invoices.ClientCode = c.ClientCode 
        AND 
        DateDiff(CurDate(),Date(InvDate)) = 0
    ) 

I don’t know how to improve the speed of the query though. Have you indexed Invoices.ClientCode?

And also c.ClientCode?
What is the datatype used for the InvDate column?
The DateDiff(CurDate(), Date(InvDate)) could be slowing down your query considerably as needs to execute two functions for each found row. If you could eliminate at least one of them that would help :slight_smile:
Correct me if I’m wrong, but isn’t DateDiff(CurDate(),Date(InvDate)) = 0 the same as CurDate() = Date(InvDate) ?
That would save you to compare to 0 and a DateDiff