Doctrine2性能：插入/更新多行

Question

Suppose that I have 6000 values and I am performing a for loop for cycle on them and perform INSERT or UPDATE actions using Symfony2 + Doctrine, which is the best/right way for execute those statements and keep good levels of performance?

First

   for ($i = 0; $i < 6000; $i++) {
    // SELECT HERE - need to find if Territory already exists 
    $entTerritory = $em->getRepository('PDOneBundle:Territory')->findOneBy(array('veeva_territory_id' => $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->setVeevaTerritoryId($soqlObj2['records'][$i]['Id']);
            $newTerritory->setName($soqlObj2['records'][$i]['Name']);

            $em->persist($newTerritory);
            $em->flush(); // ---> FLUSH HERE
        }

        $terrArr[] = $newTerritory->getId();
        $terrFailArr[] = $soqlObj2['records'][$i]['Name'];
    } else {
        $lastModifiedDate = new \DateTime(
            $soqlObj2['records'][$i]['LastModifiedDate']
        );

        if ($lastModifiedDate > $entTerritory->getUpdatedAt()) {
            // obtained a territory, we update its data
            $entTerritory->setName($soqlObj2['records'][0]['Name']);
        }

        $em->flush(); // ---> FLUSH HERE

        $terrArr[] = $entTerritory->getId();
    }
}

Second

for ($i = 0; $i < 6000; $i++) {
    // SELECT HERE - need to find if Territory already exists 
    $entTerritory = $em->getRepository('PDOneBundle:Territory')->findOneBy(array('veeva_territory_id' => $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->setVeevaTerritoryId($soqlObj2['records'][$i]['Id']);
            $newTerritory->setName($soqlObj2['records'][$i]['Name']);

            $em->persist($newTerritory);
        }

        $terrArr[] = $newTerritory->getId();
        $terrFailArr[] = $soqlObj2['records'][$i]['Name'];
    } else {
        $lastModifiedDate = new \DateTime(
            $soqlObj2['records'][$i]['LastModifiedDate']
        );

        if ($lastModifiedDate > $entTerritory->getUpdatedAt()) {
            // obtained a territory, we update its data
            $entTerritory->setName($soqlObj2['records'][0]['Name']);
        }

        $em->flush(); // ---> FLUSH HERE

        $terrArr[] = $entTerritory->getId();
    }
}

$em->flush(); // ---> FLUSH FOR INSERT HERE

Third

for ($i = 0; $i < 6000; $i++) {
    // SELECT HERE - need to find if Territory already exists 
    $entTerritory = $em->getRepository('PDOneBundle:Territory')->findOneBy(array('veeva_territory_id' => $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->setVeevaTerritoryId($soqlObj2['records'][$i]['Id']);
            $newTerritory->setName($soqlObj2['records'][$i]['Name']);

            $em->persist($newTerritory);
        }

        $terrArr[] = $newTerritory->getId();
        $terrFailArr[] = $soqlObj2['records'][$i]['Name'];
    } else {
        $lastModifiedDate = new \DateTime(
            $soqlObj2['records'][$i]['LastModifiedDate']
        );

        if ($lastModifiedDate > $entTerritory->getUpdatedAt()) {
            // obtained a territory, we update its data
            $entTerritory->setName($soqlObj2['records'][0]['Name']);
        }

        $terrArr[] = $entTerritory->getId();
    }
}

$em->flush(); // ---> FLUSH FOR INSERT AND UPDATE HERE

I have also found a topic around this here and they says:

When you have to update multiple entities, retrieving them all from the database and iterating over ORM entities is known as a bad practice.

You should never do like:

$friend = $em->getReference('Octivi\Entity\User', $friendId); $users =
$this->findAll();

foreach ($users as $user) {
    $user->setFriend($friend);
    $em->persist($user); }

$em->flush(); 

Instead, you should rely on the UPDATE query:

$qb->update('Octivi:User', 'u')
     ->set('u.friend', $friendId)
     ->getQuery()->execute(); 

Thanks to it, we only execute one SQL UPDATE statement instead of N-updates for each User entity.

So, what is the best way and why?

Answer 1

In my opinion, using SQL is the best/performance way for batch process. Doctrine is known to take a lot of memory and might quickly reach the allowed memory size.