title: Doctrine2 performance: Insert/Update multiple rows
body: <p>Suppose that I have 6000 values and I am performing a <code>for</code> loop for cycle on them and perform <code>INSERT</code> or <code>UPDATE</code> actions using Symfony2 + Doctrine, which is the best/right way for execute those statements and keep good levels of performance?</p>
  
  <p><strong>First</strong></p>
  
  <pre><code>   for ($i = 0; $i &lt; 6000; $i++) {
      // SELECT HERE - need to find if Territory already exists 
      $entTerritory = $em-&gt;getRepository('PDOneBundle:Territory')-&gt;findOneBy(array('veeva_territory_id' =&gt; $soqlObj2['records'][$i]['Id']));
  
      if (!$entTerritory) {
          // if there is no territory, then we add
          $newTerritory = new Entity\Territory();
  
          // we set the values from veeva
          if ($soqlObj2['records'][$i]['Id'] !== null || $soqlObj2['records'][$i]['Id'] !== "") {
              $newTerritory-&gt;setVeevaTerritoryId($soqlObj2['records'][$i]['Id']);
              $newTerritory-&gt;setName($soqlObj2['records'][$i]['Name']);
  
              $em-&gt;persist($newTerritory);
              $em-&gt;flush(); // ---&gt; FLUSH HERE
          }
  
          $terrArr[] = $newTerritory-&gt;getId();
          $terrFailArr[] = $soqlObj2['records'][$i]['Name'];
      } else {
          $lastModifiedDate = new \DateTime(
              $soqlObj2['records'][$i]['LastModifiedDate']
          );
  
          if ($lastModifiedDate &gt; $entTerritory-&gt;getUpdatedAt()) {
              // obtained a territory, we update its data
              $entTerritory-&gt;setName($soqlObj2['records'][0]['Name']);
          }
  
          $em-&gt;flush(); // ---&gt; FLUSH HERE
  
          $terrArr[] = $entTerritory-&gt;getId();
      }
  }
  </code></pre>
  
  <p><strong>Second</strong></p>
  
  <pre><code>for ($i = 0; $i &lt; 6000; $i++) {
      // SELECT HERE - need to find if Territory already exists 
      $entTerritory = $em-&gt;getRepository('PDOneBundle:Territory')-&gt;findOneBy(array('veeva_territory_id' =&gt; $soqlObj2['records'][$i]['Id']));
  
      if (!$entTerritory) {
          // if there is no territory, then we add
          $newTerritory = new Entity\Territory();
  
          // we set the values from veeva
          if ($soqlObj2['records'][$i]['Id'] !== null || $soqlObj2['records'][$i]['Id'] !== "") {
              $newTerritory-&gt;setVeevaTerritoryId($soqlObj2['records'][$i]['Id']);
              $newTerritory-&gt;setName($soqlObj2['records'][$i]['Name']);
  
              $em-&gt;persist($newTerritory);
          }
  
          $terrArr[] = $newTerritory-&gt;getId();
          $terrFailArr[] = $soqlObj2['records'][$i]['Name'];
      } else {
          $lastModifiedDate = new \DateTime(
              $soqlObj2['records'][$i]['LastModifiedDate']
          );
  
          if ($lastModifiedDate &gt; $entTerritory-&gt;getUpdatedAt()) {
              // obtained a territory, we update its data
              $entTerritory-&gt;setName($soqlObj2['records'][0]['Name']);
          }
  
          $em-&gt;flush(); // ---&gt; FLUSH HERE
  
          $terrArr[] = $entTerritory-&gt;getId();
      }
  }
  
  $em-&gt;flush(); // ---&gt; FLUSH FOR INSERT HERE
  </code></pre>
  
  <p><strong>Third</strong></p>
  
  <pre><code>for ($i = 0; $i &lt; 6000; $i++) {
      // SELECT HERE - need to find if Territory already exists 
      $entTerritory = $em-&gt;getRepository('PDOneBundle:Territory')-&gt;findOneBy(array('veeva_territory_id' =&gt; $soqlObj2['records'][$i]['Id']));
  
      if (!$entTerritory) {
          // if there is no territory, then we add
          $newTerritory = new Entity\Territory();
  
          // we set the values from veeva
          if ($soqlObj2['records'][$i]['Id'] !== null || $soqlObj2['records'][$i]['Id'] !== "") {
              $newTerritory-&gt;setVeevaTerritoryId($soqlObj2['records'][$i]['Id']);
              $newTerritory-&gt;setName($soqlObj2['records'][$i]['Name']);
  
              $em-&gt;persist($newTerritory);
          }
  
          $terrArr[] = $newTerritory-&gt;getId();
          $terrFailArr[] = $soqlObj2['records'][$i]['Name'];
      } else {
          $lastModifiedDate = new \DateTime(
              $soqlObj2['records'][$i]['LastModifiedDate']
          );
  
          if ($lastModifiedDate &gt; $entTerritory-&gt;getUpdatedAt()) {
              // obtained a territory, we update its data
              $entTerritory-&gt;setName($soqlObj2['records'][0]['Name']);
          }
  
          $terrArr[] = $entTerritory-&gt;getId();
      }
  }
  
  $em-&gt;flush(); // ---&gt; FLUSH FOR INSERT AND UPDATE HERE
  </code></pre>
  
  <p>I have also found a topic around this <a href="http://labs.octivi.com/mastering-symfony2-performance-doctrine/" rel="nofollow">here</a> and they says:</p>
  
  <blockquote>
    <p>When you have to update multiple entities, retrieving them all from
    the database and iterating over ORM entities is known as a bad
    practice.</p>
    
    <p>You should never do like:</p>
  </blockquote>
  
  <pre><code>$friend = $em-&gt;getReference('Octivi\Entity\User', $friendId); $users =
  $this-&gt;findAll();
  
  foreach ($users as $user) {
      $user-&gt;setFriend($friend);
      $em-&gt;persist($user); }
  
  $em-&gt;flush(); 
  </code></pre>
  
  <blockquote>
    <p>Instead, you should rely on the UPDATE query:</p>
  </blockquote>
  
  <pre><code>$qb-&gt;update('Octivi:User', 'u')
       -&gt;set('u.friend', $friendId)
       -&gt;getQuery()-&gt;execute(); 
  </code></pre>
  
  <blockquote>
    <p>Thanks to it, we only execute one SQL UPDATE statement instead of N-updates for each User entity.</p>
  </blockquote>
  
  <p>So, what is the best way and why?</p>