The Most Effective Way to Make INSERT IGNORE Using Doctrine 2

Question

I have a script that should get a list of records in a database, and then iterate over those that create new records in another table, if they do not already exist.

Currently im doing:

foreach($entries as $entry){ $newItem = new Item(); $newItem->setAttribute($entry->getAttribute()); $entityManager->persist($newItem); try{ $entityManager->flush(); } catch(\Exception $e){ if(!strpos($e->getMessage(),'Duplicate')){ throw $e; } $entityManager = $this->getDoctrine()->getManager(); //refreshes the entity manager } } 

However, doing it so very intensively, there are 1000 entries, and the script takes more than 10 minutes several times. I have seen other messages suggest that when batch processing like this, to clean up about every 20 or so, writes the problem that if one of these 20 is a duplicate, then the whole transaction dies, I'm not sure how I will return and try to find an invalid entry to exclude it before resubmitting them again.

Any help with this would be greatly appreciated.

Answer 1

You can do one SELECT to retrieve the records that already exist in the database, and then just skip those records. Also, try flush() and clear() only once or play with the batch size. I also suggest using a transaction (if you are using InnoDB).

 $this->_em->getConnection() ->beginTransaction(); try { $created = array(/* all primary keys that already exist */); $i = 1; $batchSize = sizeof($entries); foreach ($entries as $entry) { if (in_array($entry->getMyPrimaryKey(), $created)) { continue; } $newItem = new Item(); $newItem->setAttribute($entry->getAttribute()); $entityManager->persist($newItem); if (($i % $batchSize) == 0) { $this->_em->flush(); $this->_em->clear(); } $i++; } $this->_em->getConnection() ->commit(); } catch (\Exception $e) { $this->_em->getConnection() ->rollback(); $this->_em->close(); throw new \RuntimeException($e->getMessage()); }