CODEWITHSUNDEEP
symfonydoctrinedoctrine-ormquery-optimization
David Barreto
David Barreto

Reputation: 9517

How to optimize a DQL query in Symfony2?

I have an entity called Abono that is the parent class of several other entities like Cheque, Efectivo, Debito and so on. In my application I'm querying all the objects that are instances of the class Abono using a repository class with a DQL statement like this (the code is a simplified version of the real one):

$dql = "SELECT ab FROM FranquiciaBundle:Abono AS ab";
return $em->createQuery($dql)
          ->getResult();

In my template I'm showing the results like this (again, simplified code):

{% for abono in abonos %}
    <tr>
        <td>{{ abono.id }}</td>
        <td>{{ abono.tipo }}</td>
    </tr>
{% endfor %}

I have 5000 records on that table and the profiler is telling me that the app is hitting 5000 times the database instead of just once as I expected, so I change que repository a bit using getArrayResult() instead of getResult() but the problem is that the sentence {{ abono.tipo }} is a method call of the object, not a property stored in the database so it never gets hydrated into the result array.

So my question is, how can I get an array of objects hitting the database just once?

UPDATE: The method getTipo() of the class Abono returns the Late Static Binding class name of every object, it's not an association.

public function getTipo()
{
    $className = get_called_class();
    $pos = strrpos($className,'\\');
    return substr($className, $pos+1);
}

Upvotes: 2

Views: 1440

Answers (3)

David Barreto
David Barreto

Reputation: 9517

Ok I found the origin of my problem.

It seems that when querying for objects that are the inverse side of an association, doctrine will always fetch those entities to create proxy instances, even if those entities are not needed in the controller or the template. So the huge amount of hits to the database that the profile bar was showing me, was really doctrine getting in the way searching for information that I was not asking for.

The solution is to force the use of partial load in the repository like this:

$dql = "SELECT ab FROM FranquiciaBundle:Abono AS ab";
return $em->createQuery($dql)
          ->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true)
          ->getResult();

More information can be found here

Upvotes: 6

MGP
MGP

Reputation: 3031

Erik is right, however you shouldn't have to go down sql to optimize, a doSelectJoinAll will do the trick.

Upvotes: 1

Erik
Erik

Reputation: 1057

You can use a fetch join to grab the related information and hydrate it along with the first query.

"SELECT ab, tipo FROM FranquiciaBundle:Abono AS ab JOIN ab.tipo"

See also: http://doctrine-orm.readthedocs.org/en/2.0.x/reference/dql-doctrine-query-language.html#joins

Upvotes: 2

Related Questions