title: MySQL: Fill a table within a Stored Procedure efficiently
body: <p>I am testing performance in a <em>MySQL</em> Server and filling a table with more than 200 million of records. The Stored Procedure is very slow generating the big SQL string. Any help or comment is really welcome.<br /></p>
  
  <p><strong>System Info:</strong><br /></p>
  
  <ul>
  <li><strong><em>Database:</em></strong> MySQL 5.6.10 InnoDB database (test). </li>
  <li><strong><em>Processor:</em></strong> AMD Phenom II 1090T X6 core, 3910Mhz each core. </li>
  <li><strong><em>RAM:</em></strong> 16GB DDR3 1600Mhz CL8. </li>
  <li><strong><em>HD:</em></strong> Windows 7 64bits SP1 in SSD, mySQL installed in SSD, logs written in mechanical hard disk.</li>
  </ul>
  
  <p>The Stored Procedure creates a INSERT sql query with all the values to be inserted into the table.<br /></p>
  
  <pre><code>DELIMITER $$
  USE `test`$$
  
  DROP PROCEDURE IF EXISTS `inputRowsNoRandom`$$
  
  CREATE DEFINER=`root`@`localhost` PROCEDURE `inputRowsNoRandom`(IN NumRows BIGINT)
  BEGIN
      /* BUILD INSERT SENTENCE WITH A LOS OF ROWS TO INSERT */
      DECLARE i BIGINT;
      DECLARE nMax BIGINT;
      DECLARE squery LONGTEXT;
      DECLARE svalues LONGTEXT;
  
      SET i = 1;
      SET nMax = NumRows + 1;
      SET squery = 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE) VALUES ';
      SET svalues = '("1", "a1", 100, 1, 500000, "2013-06-14 12:40:45"),';
  
      WHILE i &lt; nMax DO
          SET squery = CONCAT(squery, svalues);
          SET i = i + 1;
      END WHILE;
  
      /*SELECT squery;*/
      SET squery = LEFT(squery, CHAR_LENGTH(squery) - 1);
      SET squery = CONCAT(squery, ";");
      SELECT squery;
  
      /* EXECUTE INSERT SENTENCE */
      /*START TRANSACTION;*/
      /*PREPARE stmt FROM squery;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      */
  
      /*COMMIT;*/
  END$$
  DELIMITER ;
  </code></pre>
  
  <p><br />
  <strong>Results:</strong></p>
  
  <ol>
  <li>Concatenating 20000 strings takes about 45 seconds to be processed:</li>
  </ol>
  
  <blockquote>
    <p>CALL test.inputRowsNoRandom(20000);</p>
  </blockquote>
  
  <ol start="2">
  <li>Concatenating 100000 strings takes about +5/12 minutes O_O:</li>
  </ol>
  
  <blockquote>
    <p>CALL test.inputRowsNoRandom(100000);</p>
  </blockquote>
  
  <p><sub>
  <em>Result (ordered by duration) - stateduration (summed) in sec || percentage</em><br />
  freeing items   0.00005  50.00000<br />
  starting  0.00002  20.00000<br />
  executing 0.00001  10.00000<br />
  init 0.00001  10.00000<br />
  cleaning up 0.00001  10.00000<br />
  Total 0.00010  100.00000<br />
  <br />
  <em>Change Of STATUS VARIABLES Due To Execution Of Query</em><br />
  variable value description<br />
  Bytes_received 21 Bytes sent from the client to the server<br />
  Bytes_sent 97 Bytes sent from the server to the client<br />
  Com_select 1 Number of SELECT statements that have been executed<br />
  Questions 1 Number of statements executed by the server<br />
  <br /></sub></p>
  
  <p><strong>Tests:</strong><br />
  I have already tested with different MySQL configurations from 12 to 64 threads, setting cache on and off, moving logs to another hardware disk...<br />
  Also tested using TEXT, INT..<br />
  <br />
  <strong>Additional Information:</strong></p>
  
  <ul>
  <li>Performance links: <a href="https://dba.stackexchange.com/questions/5666/possible-to-make-mysql-use-more-than-one-core">general&amp;multiple-cores</a>, <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html" rel="nofollow noreferrer">configuration</a>, <a href="http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html" rel="nofollow noreferrer">optimizing IO</a>, <a href="https://dba.stackexchange.com/questions/15146/using-multiple-cores-for-single-mysql-queries-on-debian">Debiancores</a>, <a href="https://dba.stackexchange.com/questions/21290/how-to-get-the-most-out-of-mysql-on-a-quadcore-machine-with-16-gb-of-ram">best configuration</a>, <a href="https://dba.stackexchange.com/questions/23189/proper-tuning-for-30gb-innodb-table-on-server-with-48gb-ram/23214#23214">config 48gb ram</a>..</li>
  <li>Profiling a SQL query: <a href="https://dba.stackexchange.com/questions/6526/query-runs-a-long-time-in-some-newer-mysql-versions/6542#6542">How to profile a query</a>, <a href="https://stackoverflow.com/questions/17042760/storing-changes-on-entities-is-mysql-the-proper-solution?noredirect=1#comment24768877_17042760">Check for possible bottleneck in a query</a><br /></li>
  </ul>
  
  <p><br />
  <strong>Questions:</strong><br /></p>
  
  <ul>
  <li>Is something wrong in the code? If I send 100000 strings to build the final SQL string, the result of <strong><code>SELECT squery;</code></strong> is a NULL string. Whats happening? (error must be there but I dont see it).</li>
  <li>Can I improve the code in any way to speed it up? </li>
  <li>I have read some operations in Stored Procedures can be really slow, should I generate the file in C/Java/PHP.. and <a href="https://stackoverflow.com/questions/10971597/import-and-insert-sql-gz-file-into-database-with-putty">send it to mysql</a>? 
  
  <blockquote>
    <p>mysql -u mysqluser -p databasename &lt; numbers.sql</p>
  </blockquote></li>
  <li>MySQL seems to <a href="https://serverfault.com/questions/278729/mysql-which-cpu-is-better">use only one core for one single SQL query</a>, would <a href="http://nginx.org" rel="nofollow noreferrer">nginx</a> or other database system: <a href="https://dba.stackexchange.com/questions/2918/about-single-threaded-versus-multithreaded-databases-performance/2948#2948">Multithreadted DBs</a>, <a href="http://cassandra.apache.org" rel="nofollow noreferrer">Cassandra</a>, <a href="http://redis.io/" rel="nofollow noreferrer">Redis</a>, <a href="http://www.mongodb.org" rel="nofollow noreferrer">MongoDB</a>..) achieve better performance with stored procedures and use more than one CPU for one query? (Since my single query is using only 20% of total CPU with about 150 threads).
  <br />
  <br /></li>
  </ul>
  
  <p><strong>UPDATE:</strong><br /></p>
  
  <ul>
  <li>Efficent way of filling the table, <a href="https://stackoverflow.com/a/17139749/921456">check peterm answer below</a>.</li>
  <li><a href="https://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-mode">Performance of Stored Procedure, modern RDBMS or inline
  queries.</a></li>
  </ul>