symfony2 + doctrine2 - how lazy loading affect our applications?

Question

This will be a sort of Q/A for everyone that wonder about symfony2 + doctrine2 and performance.

I'm writing this Q/A because I've started to playing around SF2+doctrine for a new project and I wanted to do some performance tests. I've surfed the net but didn't find a complete answer for my doubts so I've decided to make my own one. Hope this will be useful for someone

Let's say we have two entities like the following

//all ORM declaration here
class Lodging
{
  //some declaration here

  /**
   * @ORM\ManyToOne(targetEntity="Currency")
   * @ORM\JoinColumn(name="currency_iso_code", referencedColumnName="iso_code")
   */
  protected $currency;

  //some methods here
}

//all ORM declaration here 
class Currency
{
  /**
   * @ORM\Id
   * @ORM\Column(type="string", length=3)
   */
  protected $iso_code;

  /**
   * @ORM\Column(type="string")
   */
  protected $description;

  //some methods here
}

Now you will made an entity type for Lodging where you will be able to create or - if the entity passed to the type has some prefetched values - to edit a Lodging object (let's concentrate on that case). In that form you also need the description of Currency, not only the $iso_code.

Question: is better to use ->findOneById() doctrine2 method or to write a DQL (or use query builder facility)?
Why?

Answer 1

PREMABLE

This answer has sense only if you're experiencing a slow page that's using lazy loads. For more common actions (load just one entity, load a "static page" or a cached one and so on) you could continue to use built in function provided by doctrine

---

Well, let's analyze some of the different methodologies you can follow

1 - findOneById()

Into controller you have chosen to follow more "common" and "fast" way to procede: use pre-existent findOneById() method. Wonderful.

Let's check performance

• Number of queries done: three query were done as we need one to retrieve Lodging object, the second one to fetch currently associated Currency (lazy loaded) and one to fetch all Currency entities (because you can change from one currency to other)
• Page loading time: about 500ms
• Memory usage: about 32MB

2 - Write a custom repository method

2.1 "Basic" DQL repository function

public function findLodgingById($id)
{
  $lodging = null;

  $q = $this->createQueryBuilder('lodging')
         ->select('lodging')
         ->where('lodging.id = :id')
         ->setParameter('id', $id)
         ->getQuery();

  $lodging_array = $q->getResult(); //will not fetch a single object but array
  if ($lodging_array) {
    $lodging = reset($lodging_array);
  }

  return $lodging;
}

Let's check performace

• Number of queries done: it shouldn't be a surprise for you but ... number of query done is always three! Of course you're not doing anything but same of findOneById() (an, maybe, even in a worst way!). You're taking advantage of lazy loading again.
• Page loading time: about 500ms. Loading time didn't change
• Memory usage: about 34MB. Memory usage is increased of 6,25 % (due to array?)

2.2 DQL with JOIN

public function findLodgingById($id)
{
  $lodging = null;

  $q = $this->createQueryBuilder('lodging')
         ->select('lodging')
         ->leftJoin('lodging.currency', 'currency')
         ->where('lodging.id = :id')
         ->setParameter('id', $id)
         ->getQuery();

  $lodging_array = $q->getResult(); //will not fetch a single object but array
  if ($lodging_array) {
    $lodging = reset($lodging_array);
  }

  return $lodging;
}

Let's check performace

• Number of queries done: The number of queries dind't change! But ... why? We are telling explicitly to doctrine2 to join currency entity but it seems to ignore that instruction. The answer is that we're not selecting currency entity also so doctrine2 will use, again, lazy loading facility.
• Page loading time: about 500ms. Loading time didn't change 2.1
• Memory usage: about 34MB. Memory usage didn't changed from 2.1

2.3 Let's try something better: Join with Currency selection

public function findLodgingById($id)
{
  $lodging = null;

  $q = $this->createQueryBuilder('lodging')
         ->select('lodging', 'currency')
         ->leftJoin('lodging.currency', 'currency')
         ->where('lodging.id = :id')
         ->setParameter('id', $id)
         ->getQuery();

  $lodging_array = $q->getResult(); //will not fetch a single object but array
  if ($lodging_array) {
    $lodging = reset($lodging_array);
  }

  return $lodging;
}

Let's check performace

• Number of queries done: Finally number of queries decreased! We reach two query instead of three. What query gone? Lazy loading of associated (current) currency is gone but, of course, you have to fetch all possible currency.
• Page loading time: about 350ms.
• Memory usage: about 34MB. Memory usage didn't changed

---

Definitive Solution (?)

public function findLodgingById($id)
{
  $lodging = null;

  $q = $this->createQueryBuilder('lodging')
         ->select('lodging')
         ->where('lodging.id = :id')
         ->setParameter('id', $id)
         ->getQuery();

  $q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true);

  $lodging_array = $q->getResult(); //will not fetch a single object but array
  if ($lodging_array) {
    $lodging = reset($lodging_array);
  }

  return $lodging;
}

The $q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true); line of code seems to save time and memory compared to other solutions.

• Number of queries done: Two, of course
• Page loading time: about 340ms.
• Memory usage: about 32MB.

PLEASE PAY ATTENTION

This solution doesn't let you change associated (Currency) entity as it will be betched with Query::HINT_FORCE_PARTIAL_LOAD, true

Comments

Results seems to be good for page loading time (memory usage of course will not change) and though performances seems to be only "a little" better, you shouldn't not focus ONLY onto results: here we're taking as an example only a simple snippet of code with just one lazy loading operation: think about an entity (or, worst, a lot of entity like blog posts with related comments) that will do wrong(*) lazy loading for every entity fetched and managed: you could reach even 50-70 query for a single page (and of course, in this case, you could notice easily performace benefits due to "single" query)

(*) Why I say wrong? Because if you can migrate the fetching-logic of your objects elsewhere or if you already know what entity/attribute you need, so your contents aren't dynamic and you can know them before use, lazy-loading is not only useless but also harmful.
Contrariwise if you can't know at "coding writing time" what properties or entities you'll need, of course lazy loading could save you memory (and time) wasting for useless objects/associations.

---

Final thoughts

It's better to "lose" some minutes for write DQL query (that seems to be even silly) that use "built-in" ones. Moreover you should use array (and not objects) for read-only operation (list elements that couldn't be modificated) changing getResult() method call as follows: getResult(Doctrine\ORM\Query::HYDRATE_ARRAY);. That will change "default" value (HYDRATE::OBJECT)

Answer 2

It seems like premature optimisation to be worrying about it before you're seeing a problem.

Write whatever's quickest, and in this case, that's often going to be $em->findOneById($id) style. Then use valgrind to look for bottlenecks.

Looking at the amount of time you spend writing all that custom DQL, the overall peformance could be improved by fixing a bigger problem somewhere else in your application.