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
MODIFIES SQL DATA
SQL SECURITY DEFINER
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;
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;
UNTIL done END REPEAT;
Well as far as I can tell this query should do all the work of your procedure:
Invoices.ClientCode = c.ClientCode
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
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