php - Doctrine2 性能 : Insert/Update multiple rows

Question

假设我有 6000 个值，我正在对它们执行 for 循环，并使用 Symfony2 + 执行 INSERT 或 UPDATE 操作 + Doctrine，这是执行这些语句并保持良好性能水平的最佳/正确方法？

首先

   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();
    }
}

第二

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

第三

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

我还找到了一个关于这个的话题 here他们说:

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.

那么，什么是最好的方法，为什么？

Answer 1

在我看来，使用 SQL 是批处理的最佳/性能方式。众所周知，Doctrine 会占用大量内存，并可能很快达到允许的内存大小。