Doctrine and the 100 000 row import

Row sixty thousand. Allowed memory size exhausted. The import script on one project died there, and the code was the obvious loop: read a row, persist() an entity, next row, flush() at the end. On a hundred test rows it worked perfectly. It was my loop.

The reason is the Unit of Work. Doctrine keeps every managed entity in memory, plus a snapshot of its original data for change tracking. Persist a hundred thousand entities and you hold a hundred thousand objects twice. This is not a bug. It is the price of the ORM’s main feature, and on a normal web request the price is invisible because the request dies young.

The standard fix is batching:

foreach ($reader->rows() as $i => $row) {
    $em->persist($this->makeEntity($row));
    if ($i % 500 === 0) {
        $em->flush();
        $em->clear();
    }
}
$em->flush();

flush() writes, clear() detaches everything and lets memory go. The memory graph turns from a ramp into a sawtooth.

Two traps. After clear() every previously loaded entity is detached, so references you kept across the border are stale now, re-fetch them. And in dev, disable the SQL logger. It quietly keeps every executed query in an array:

$em->getConnection()->getConfiguration()->setSQLLogger(null);

If the import inserts plain rows with no business logic per entity, skip the ORM and use DBAL with multi-row inserts. On our data it was about ten times faster. Also think about transaction size. One transaction around a hundred thousand rows holds locks for the whole run, one per row is slow, one per batch is the sane middle.

An ORM is built for the request cycle. An import is not a request. Doctrine can still do it, but you manage its memory by hand, and past some volume it is simpler to write the SQL. I wrote the SQL.