MySQL use one Insert statement instead of cursor loop

Question

I have this trigger and it is working well but I read that using cursors and loops is not good for performance..

So How can I replace using cursor and loop by one insert into statement like the that one in update part:

CREATE TRIGGER `after_insert_invoice` AFTER INSERT ON `invoicetbl`
 FOR EACH ROW BEGIN
  DECLARE v_finished INTEGER DEFAULT 0;
  DECLARE my_hour INT(11) DEFAULT 0;
  DECLARE my_day INT(11) DEFAULT 0;
  DECLARE my_month INT(11) DEFAULT 0;
  DECLARE current_item_id BINARY(16);
  DECLARE current_item_total_price DECIMAL(11, 2);
  DECLARE current_item_count DECIMAL(11, 3);

  DECLARE cur CURSOR FOR     SELECT  item_id, item_total_price, item_count
        FROM  invoice_itemtbl
        WHERE  invoice_id = NEW.invoice_id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

  SET my_month = EXTRACT(YEAR_MONTH FROM NEW.invoice_date);
  SET my_day = CONCAT(my_month, LPAD(DAY(NEW.invoice_date), 2, '0'));
  SET my_hour = CONCAT(my_day, LPAD(HOUR(NEW.invoice_date), 2, '0'));

  IF NEW.invoice_type = 0 THEN

        OPEN cur;

        start_loop: LOOP

        FETCH cur INTO current_item_id,
                       current_item_total_price,
                       current_item_count;

        IF v_finished = 1 THEN 
            LEAVE start_loop;
        ELSE

(continued...)

        INSERT
          INTO  sales_result_company_item_month(company_id, currency_id,
                time_value, item_id, result, invoices_count, item_qty )
          VALUES  (NEW.company_id, NEW.currency_id, my_month, current_item_id,
                    current_item_total_price, 1, current_item_count )
          ON DUPLICATE KEY 
          UPDATE  result = result + current_item_total_price,
                  invoices_count = invoices_count + 1,
                  item_qty = item_qty + current_item_count;

        END IF;
        END LOOP;

      CLOSE cur;

  END IF;

  IF NEW.invoice_type = 1 THEN

    //like here:
    UPDATE  sales_result_company_item_month m
    INNER JOIN  
    (
        SELECT  item_id, returns_count, returns_total
            FROM  invoice_returns
            WHERE  return_invoice_id = NEW.invoice_id
    ) f  ON m.item_id = f.item_id SET m.result = m.result - f.returns_total,
        m.item_qty = m.item_qty - f.returns_count
    WHERE  m.company_id = NEW.company_id
      AND  m.currency_id = NEW.currency_id
      AND  m.time_value = my_month;

  END IF;

END

(Copied from Comment -- after 'fixing' problem, including the elimination of Cursor):

INSERT
     INTO  sales_result_company_item_month
         (company_id, currency_id, time_value, item_id,
          result, invoices_count, item_qty) 
SELECT  NEW.company_id, NEW.currency_id, my_month, t.item_id,
        t.item_total_price, 1, t.item_count
    FROM  invoice_itemtbl t
    WHERE  invoice_id = NEW.invoice_id
    ON DUPLICATE KEY UPDATE 
        result = result + t.item_total_price,
        invoices_count = invoices_count + 1,
        item_qty = item_qty + t.item_count;

score 2 · Accepted Answer · answered Apr 13 '17 at 22:37

2

Yes, you can probably get rid of the Cursor.

It is possible to do

INSERT INTO t ( ... )
    ON DUPLICATE KEY UPDATE ...
    SELECT ...;

In that, the SELECT fetches all the rows to "upsert". You will probably need VALUES() in the UPDATE clause to distinguish between selected columns and the columns already in the table. See examples in the manual.

If you wish to discuss this question further, please

Provide SHOW CREATE TABLE for the relevant tables,
Get rid of the kruft (eg, my_day, which is not used)
Prefix local variables with _, or otherwise distinguish them from table columns.

current_item_count DECIMAL(11, 3) puzzles me: What "count" needs 3 decimal places?

answered Apr 13 '17 at 22:37

Rick James

135,179
13
127
222

thank you very much. this is the result query: `INSERT INTO sales_result_company_item_month (company_id, currency_id, time_value, item_id, result, invoices_count, item_qty) SELECT NEW.company_id, NEW.currency_id, my_month, t.item_id, t.item_total_price, 1, t.item_count FROM invoice_itemtbl t WHERE invoice_id = NEW.invoice_id ON DUPLICATE KEY UPDATE result = result + t.item_total_price, invoices_count = invoices_count + 1, item_qty = item_qty + t.item_count;` – user2241289 Apr 19 '17 at 15:20
`current_item_count` it was INT(11) and I change the type without changing name. thanks. – user2241289 Apr 19 '17 at 15:23
1

I added your "solution" to your question -- for other readers. – Rick James Apr 19 '17 at 15:59

MySQL use one Insert statement instead of cursor loop

1 Answers1