Doctrine 2 query builder vs entity persist performance

Question

Summary: which is quicker: updating / flushing a list of entities, or running a query builder update on each?

We have the following situation in Doctrine ORM (version 2.3).

We have a table that looks like this

cow
wolf
elephant
koala

and we would like to use this table to sort a report of a fictional farm. The problem is that the user wishes to have a customer ordering of the animals (e.g. Koala, Elephant, Wolf, Cow). Now there exist possibilities using CONCAT, or CASE to add a weight to the DQL (example 0002wolf, 0001elephant). In my experience this is either tricky to build and when I got it working the result set was an array and not a collection.

So, to solve this we added a "weight" field to each record and, before running the select, we assign each one with a weight:

$animals = $em->getRepository('AcmeDemoBundle:Animal')->findAll();

foreach ($animals as $animal) {
    if ($animal->getName() == 'koala') {
        $animal->setWeight(1);
    } else if ($animal->getName() == 'elephant') {
        $animal->setWeight(2);
    }
    // etc
    $em->persist($animal);
}
$em->flush();

$query = $em->createQuery(
    'SELECT c FROM AcmeDemoBundle:Animal c ORDER BY c.weight'
);

This works perfectly. To avoid race conditions we added this inside a transaction block:

$em->getConnection()->beginTransaction();

// code from above

$em->getConnection()->rollback();

This is a lot more robust as it handles multiple users generating the same report. Alternatively the entities can be weighted like this:

$em->getConnection()->beginTransaction();
$qb = $em->createQueryBuilder();
$q = $qb->update('AcmeDemoBundle:Animal', 'c')
            ->set('c.weight', $qb->expr()->literal(1))
            ->where('c.name = ?1')
            ->setParameter(1, 'koala')
            ->getQuery();
$p = $q->execute();

$qb = $em->createQueryBuilder();
$q = $qb->update('AcmeDemoBundle:Animal', 'c')
            ->set('c.weight', $qb->expr()->literal(2))
            ->where('c.name = ?1')
            ->setParameter(1, 'elephant')
            ->getQuery();
$p = $q->execute();

// etc

$query = $em->createQuery(
    'SELECT c FROM AcmeDemoBundle:Animal c ORDER BY c.weight'
);
$em->getConnection()->rollback();

Questions:

1) which of the two examples would have better performance?

2) Is there a third or better way to do this bearing in mind we need a collection as a result?

Please remember that this is just an example - sorting the result set in memory is not an option, it must be done on the database level - the real statement is a 10 table join with 5 orderbys.

score 0 · Answer 1 · answered Dec 15 '12 at 13:15

Initially you could make use of a Doctrine implementation named Logging (\Doctrine\DBAL\LoggingProfiler). I know that it is not the better answer, but at least you can implement it in order to get best result for each example that you have.

namespace Doctrine\DBAL\Logging;

class Profiler implements SQLLogger
{
    public $start = null;

    public function __construct()
    {
    }

    /**
     * {@inheritdoc}
     */
    public function startQuery($sql, array $params = null, array $types = null)
    {
        $this->start = microtime(true);
    }

    /**
     * {@inheritdoc}
     */
    public function stopQuery()
    {
        echo "execution time: " . microtime(true) - $this->start;
    }
}

In you main Doctrine configuration you can enable as:

$logger = new \Doctrine\DBAL\Logging\Profiler;
$config->setSQLLogger($logger);

Thanks for the answer. As we am using Symfony 2.1 we get the SQL times in the dev profiler anyway. The customer decided they want the querybuilder route anyway. The update/rollback method works perfectly. — mogoman, Dec 19 '12 at 13:20

Doctrine 2 query builder vs entity persist performance

1 Answers1