3 Replies Latest reply: Jan 18, 2013 10:01 AM by Sven W. RSS

    Update statement taking long time

    Sid_ Z.
      Hi All,

      The following query is taking too much time for update the 100000 records.
      Please tell me how can this query time get reduced.
              
       DECLARE
       
         CURSOR cur IS
           SELECT c.account_id FROM crm_statement_fulfilled_sid c;
       
       BEGIN
         FOR i IN cur LOOP
           UPDATE crm_statement_fulfilled_sid a
              SET a.rewards_expired = (SELECT abs(nvl(SUM(t.VALUE), 0))
                                         FROM crm_reward_txns t
                                        WHERE txn_date BETWEEN
                                              (SELECT (decode(MAX(csf.procg_date) + 1,
                                                              NULL,
                                                              to_date('01-May-2011',
                                                                      'DD-MM-YYYY'),
                                                              MAX(csf.procg_date) + 1))
                                                 FROM crm_statement_fulfilled csf
                                                WHERE csf.procg_date < '01-aug-2012'
                                                  AND account_id = i.account_id) /*points_start_date_in*/
                                          AND '01-aug-2012'
                                          AND reason = 'RE'
                                          AND t.account_id = i.account_id);
         
         END LOOP;
       
       END;
      Any help?


      Sid
        • 1. Re: Update statement taking long time
          SomeoneElse
          Don't use a loop.

          Use a single update statement. You've got most of it anyway.
          • 2. Re: Update statement taking long time
            Hoek
            First of all read:
            {message:id=9360002}
            and
            {message:id=9360003}
            and make a habit out of posting your database version, execution plan etc., it is all explained in those links.

            Second: you're doing row-by-row (= slow-by-slow) processing.
            See if you can rewrite your code into a single SQL UPDATE statement that will process an entire SET at once.
            http://tkyte.blogspot.nl/2006/10/slow-by-slow.html
            • 3. Re: Update statement taking long time
              Sven W.
              Hoek wrote:
              ...
              Second: you're doing row-by-row (= slow-by-slow) processing.
              See if you can rewrite your code into a single SQL UPDATE statement that will process an entire SET at once.
              Well to be nitpicking. The op already does a single big update. He just does it again and again and again for each account id. Each time completely overwriting the values from the last account id.