SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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;

  2. #2
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well as far as I can tell this query should do all the work of your procedure:

    Code:
    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?

  3. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by jOOOL View Post
    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
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •