Symfony2, Doctrine, add\insert\update best solution for big count of queries

Question

Let's imagine we have this code:

while (true)
{
    foreach($array as $row)
    {
       $item = $em->getRepository('reponame')->findOneBy(array('filter'));

       if (!$item)
       {
           $needPersist = true;
           $item = new Item();
       }

       $item->setItemName()
       // and so on ...

       if ($needPersist)
       {
           $em->persist();
       }
    }
    $em->flush();
}

So, the point is that code will be executed a lot of times (while server won't die :) ). And we want to optimize it. Every time we:

1. Select already entry from repository.
2. If entry not exists, create it.
3. Set new (update) vars to it.
4. Apply actions (flush).

So question is - how to avoid unnecessary queries and optimize "check if entry is exist"? Because when there are 100-500 queries it's not so scary... But when it comes up to 1000-10000 for one while loop - it's too much.

PS: Each entry in DB is unique by several columns (not only by ID).

Answer 1

1. Instead of fetching results one-by-one, load all results with one query.

Eg. let's say your filter wants to load ids 1, 2, 10. So QB would be something like:

$allResults = ...
    ->where("o.id IN (:ids)")->setParameter("ids", $ids) 
    ->getQuery()
    ->getResults() ;

1. "foreach" of these results, do your job of updating them and flushing

2. While doing that loop, save ids of those fetched objects in new array

3. Compare that array with original one using array_diff. Now you have ids that were not fetched the first time

4. Rinse and repeat :)

5. And don't forget $em->clear() to free memory

While this can still be slow when working with 10.000 records (dunno, never tested), it will be much faster to have 2 big queries than 10.000 small ones.