title: mysql stored procedure slow may need prepared statement
body: <p>Data comes in via a zip file in a group of csv files which are used to populate holding tables. Some can include new data or updates of existing entries. The holding tables are used for intermediate processing then are used for import to the working tables.</p>
  
  <p>In an effort to speed up the process I have been writing stored procedures to update the working tables. On my laptop one such file with only 730 records is taking about a minute to do its stuff.</p>
  
  <p>I did consider making the 'insert   ... on duplicate key update' into a prepared statement inside the SP but some of the other tables have many more fields and I could find no good guide on how to write such a complex one.</p>
  
  <p>Here is my stored procedure:</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS impLuRacodes;
  DELIMITER $$
  CREATE DEFINER=`root`@`localhost` PROCEDURE impLuRacodes()  
  BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_ATID varchar(10);
  DECLARE v_Code varchar(10);
  DECLARE v_IssType varchar(10);
  DECLARE v_Category varchar(10);
  DECLARE v_CNumber int(4);
  DECLARE v_CDesc varchar(255);
  DECLARE v_ColCode varchar(6);
  DECLARE v_LLike int(3);
  DECLARE v_LifeS int(3);
  DECLARE v_PropS int(3);
  DECLARE v_BusS int(3);
  DECLARE c_1 CURSOR FOR 
       SELECT `ATID`, `Code`, `IssType`, `Category`, `CNumber`, `CDesc`, `ColCode`, `LLike`, `LifeS`, `PropS`, `BusS` 
       FROM lu_racodes_temp;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  OPEN c_1;
  REPEAT
     FETCH c_1 INTO v_ATID, v_Code, v_IssType, v_Category, v_CNumber, 
                    v_CDesc, v_ColCode, v_LLike, v_LifeS, v_PropS, v_BusS;
     INSERT INTO lu_racodes  (ATID, `Code`, IssType, Category, CNumber, 
                 CDesc, ColCode, LLike, LifeS, PropS, BusS) 
     VALUES(v_ATID, v_Code, v_IssType, v_Category, v_CNumber, v_CDesc, 
            v_ColCode, v_LLike, v_LifeS, v_PropS, v_BusS) 
     ON DUPLICATE KEY UPDATE 
            ATID= v_ATID, `Code`= v_Code, IssType= v_IssType, Category= v_Category, 
            CNumber= v_CNumber, CDesc= v_CDesc, ColCode= v_ColCode, LLike=v_LLike, 
            LifeS= v_LifeS, PropS= v_PropS, BusS= v_BusS;
  UNTIL done END REPEAT; 
  CLOSE c_1;
  END $$
  </code></pre>
  
  <p>Alternatively, is it possible to put the Select inside the 'Insert ... On Duplicate Key' instead of using a cursor - again something I have had no joy in finding a clear answer to.</p>
  
  <p>Thanks to the guidance I rewrote the code to put the select inside and execution time dropped to 0.156 seconds for a first population. I was concerned about the syntax of the update section with the reference to VALUES as it was a Select statement bringing in the data but changing a couple of rows in the source table did get updated and still with only a 0.235 second time. So here is the code:</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS impLuRacodes;
  DELIMITER $$
  CREATE DEFINER=`root`@`localhost` PROCEDURE impLuRacodes()  
  BEGIN
  INSERT INTO lu_racodes  (`ATID`, `Code`, `IssType`, `Category`, `CNumber`, `CDesc`, `ColCode`, `LLike`, `LifeS`, `PropS`, `BusS`) SELECT `ATID`, `Code`, `IssType`, `Category`, `CNumber`, `CDesc`, `ColCode`, `LLike`, `LifeS`, `PropS`, `BusS` FROM lu_racodes_temp ON DUPLICATE KEY UPDATE `ATID`= VALUES(`ATID`), `Code`= VALUES(`Code`), `IssType`= VALUES(`IssType`), `Category`= VALUES(`Category`), `CNumber`= VALUES(`CNumber`), `CDesc`= VALUES(`CDesc`), `ColCode`= VALUES(`ColCode`), `LLike`= VALUES(`LLike`), `LifeS`= VALUES(`LifeS`), `PropS`= VALUES(`PropS`), `BusS`= VALUES(`BusS`);
  END
  $$
  DELIMITER ;
  </code></pre>