Transactions and Doctrine performance

While there are few records, Doctrine works unnoticed. As the database grows, questions appear: how to make several changes as one unit, how to process a million rows, and why a page runs two hundred queries.

Transactions

flush() wraps its own queries in a transaction. When there are several flush() calls or raw queries, the transaction is set explicitly:

$em->wrapInTransaction(function (EntityManagerInterface $em) use ($from, $to, $amount) {
    $from->withdraw($amount);
    $to->deposit($amount);
 
    $em->persist(new Transfer($from, $to, $amount));
});

If an exception occurs inside, every change is rolled back.

Locking a row

Two simultaneous withdrawals may read the same balance. A lock makes the second query wait:

use Doctrine\DBAL\LockMode;
 
$em->wrapInTransaction(function (EntityManagerInterface $em) use ($id, $amount) {
    $account = $em->find(Account::class, $id, LockMode::PESSIMISTIC_WRITE);
 
    $account->withdraw($amount);
});

Large tables

findAll() for a million records exhausts memory: Doctrine keeps every loaded object. Records are iterated one at a time, and memory is cleared now and then:

$query = $em->createQuery('SELECT u FROM App\Entity\User u');
 
foreach ($query->toIterable() as $index => $user) {
    $user->recalculateRating();
 
    if ($index % 200 === 0) {
        $em->flush();
        $em->clear();
    }
}
 
$em->flush();

clear() makes Doctrine "forget" the loaded objects.

A mass change in one query

When objects are not needed, changing rows straight in the database is faster:

$em->createQuery('UPDATE App\Entity\Post p SET p.published = false WHERE p.createdAt < :date')
    ->setParameter('date', new \DateTimeImmutable('-2 years'))
    ->execute();

Only the needed data

Objects are overkill for lists and reports. Arrays load faster:

$rows = $this->createQueryBuilder('p')
    ->select('p.id, p.title, COUNT(c.id) AS comments')
    ->leftJoin('p.comments', 'c')
    ->groupBy('p.id')
    ->getQuery()
    ->getArrayResult();

Indexes

Columns used for searching and ordering should have an index. It is described by an attribute above the entity:

#[ORM\Entity]
#[ORM\Index(name: 'post_created_idx', columns: ['created_at'])]
#[ORM\Index(name: 'post_status_date_idx', columns: ['status', 'created_at'])]
class Post
{
    // …
}

Create a migration afterwards. The columns of ManyToOne relationships get an index automatically.

Raw SQL

A complex report is easier to write in SQL:

$rows = $em->getConnection()->executeQuery(
    'SELECT DATE(created_at) AS day, COUNT(*) AS total FROM post WHERE created_at >= :from GROUP BY day',
    ['from' => '2026-01-01'],
)->fetchAllAssociative();

Values are still passed as parameters.

Where to look for the cause

The Doctrine tab of the profiler shows every query of a page, their time and the number of identical ones. A hundred identical queries signal the N+1 problem: the relationship should be loaded with leftJoin() and addSelect().

Common mistake

After $em->clear() every object loaded earlier becomes "detached": Doctrine no longer tracks it, and its changes are not saved. If an object — the current user, for instance — is needed after clearing, load it again with find().

Thanks for reading!