Most efficient way to do batch INSERT IGNORE using doctrine 2

doctrine-orm, mysql, php, symfony

Solution

You can do one `SELECT` to fetch records that already exist in database, and later just skip these records. Additionally, try to execute `flush()` and `clear()` just once or play around with the batch size. I would also suggest to use transaction (if you use 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());
} 

Problem

Original source