title: Bulk Insert in Symfony and Doctrine: How to select batch size?
body: <p>I am working on a web app using <strong>Symfony 2.7</strong> and <strong>Doctrine</strong>. A Symfony command is used to perform an update of a large number of entities. </p>
  
  <p>I followed the <a href="https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/batch-processing.html#bulk-inserts" rel="nofollow noreferrer">Doctrine guidelines</a> and use <code>$entityManager-&gt;flush()</code> not for every single entity.</p>
  
  <p>This is die Doctrine example code:</p>
  
  <pre><code>&lt;?php
  $batchSize = 20;
  for ($i = 1; $i &lt;= 10000; ++$i) {
      $user = new CmsUser;
      $user-&gt;setStatus('user');
      $user-&gt;setUsername('user' . $i);
      $user-&gt;setName('Mr.Smith-' . $i);
      $em-&gt;persist($user);
      if (($i % $batchSize) === 0) {
          $em-&gt;flush();
      }
  }
  $em-&gt;flush(); //Persist objects that did not make up an entire batch
  </code></pre>
  
  <p>The guidelines say:</p>
  
  <blockquote>
    <p>You may need to experiment with the batch size to find the size that
    works best for you. Larger batch sizes mean more prepared statement
    reuse internally but also mean more work during flush.</p>
  </blockquote>
  
  <p>So I did try different batch size. The larger the batch size, the faster the command completes its task. </p>
  
  <p><strong>Thus the question is: What are the downsides of large batch sizes? Why not use <code>$entityManager-&gt;flush()</code> only once, after all entities have been updated</strong> </p>
  
  <p>The docu just says, that larger batch sizes "mean more work during flush". But why/when could this be a problem?</p>
  
  <p>The only downside I can see are <code>Exceptions</code> during the update: If the script stops before the saved changed where flushed, the changes are lost. Is this the only limitation?</p>