CODEWITHSUNDEEP
performancesymfonydoctrine-orm
beterthanlife
beterthanlife

Reputation: 1658

Symfony2 and doctrine2 performance of simple DQL query

Im developing a front end for a site where performance is very important. Rapid development was also key, so I made the decision to go with symfony2 and to use the Doctrine2 ORM.

When I request a simple page from symfony (without DB request) it responds in ~200ms. As soon as I request anything from the DB through Doctrine it jumps up to ~1300ms.

I appreciate there is time to hydrade the records, but this is with me hydrating to array.

Heres the controller code. The template just outputs the array count.

$repository = $this->getDoctrine()->getManager()->getRepository('AcmeProductBundle:Product');
    $qb = $repository->createQueryBuilder('g');
    $qb->addOrderBy('g.id', 'DESC');

    $ret = null;
    $query = $qb->getQuery();
    //$query->useResultCache(true, 3600);
    $ret = $query->getArrayResult();
    return $this->render('AcmeCatalogBundle::test.html.twig', array('id' => count($ret)));

Looking at the Timline in symfony profiler toolbar, the contoller is taking ~1000ms and doctrine is taking ~1ms. But if i comment out the getResults() line the controller jumps down to ~45ms.

Is this normal? What can I do about this?

UPDATE I ran the following test and it shows that the first query is where all the time is lost:

$ret = array();
    $start = microtime(true);
    for ($i = 1; $i <= 10; $i++) {
        $time_start = microtime(true);

        $query = $em->createQuery('SELECT p FROM AcmeProductBundle:Product p WHERE p.id = 1');
        $products = $query->getResult();

        $time_end = microtime(true);
        $ret[$i] = $time_end - $time_start;
    }
    $end = microtime(true);
    $ret['Total'] = $end - $start;

    return $this->render('AcmeCatalogBundle::test.html.twig', array('ret' => $ret));

And the results:

  1. 1.0216779708862
  2. 0.00091791152954102
  3. 0.00082588195800781
  4. 0.00081419944763184
  5. 0.00081706047058105
  6. 0.00081610679626465
  7. 0.00081491470336914
  8. 0.00081706047058105
  9. 0.00043296813964844
  10. 0.0004270076751709

Total - 1.0283808708191

Could it be that the db connection or entity manager isnt being pooled perhaps?

Upvotes: 0

Views: 3590

Answers (2)

beterthanlife
beterthanlife

Reputation: 1658

So as you guys suggested, it turned out to be my environment. Namely, that I was using 'localhost' as the database_host in symfony. Changing it to '127.0.0.1' made all the difference. Im now running queries suitably fast.

Thanks for your comments people.

http://www.bluetopazgames.com/uncategorized/php-mysql_connect-is-slow-1-second-for-localhost-windows-7/

Upvotes: 1

Mun Mun Das
Mun Mun Das

Reputation: 15002

You guessed right, entity manager comes to action here. When you issue $query->getResult() default hydration mode is Query::HYDRATE_OBJECT. For this mode Doctrine2 will map each row data to entity object (which may be very expensive for complex entity relation) and load them to entity manager. But if you issue same query again it will match the row identifiers with the identifiers of the entity object loaded in the entity manager. If matched Doctrine2 will not do mapping, will do mapping and load if not matched.

It is useful if same query is issued multiple time. For example various service can load user data and show user info in various parts of the page. If you are sure that entity object will be loaded and used exactly once in one request cycle then you can use $query->getArrayResult() to avoid unnecessary resultset mapping. That said, ORM can be efficient if used properly :). For more info see doc.

Upvotes: 0

Related Questions