Doctrine2 fetch Count more optimized and faster way Or Zf2 library

Question

I am using Doctrine2 and Zf2 , now when I need to fetch count of rows, I have got the following two ways to fetch it. But my worry is which will be more optimized and faster way, as in future the rows would be more than 50k. Any suggestions or any other ways to fetch the count ?? Is there any function to get count which can be used with findBy ???

Or should I use normal Zf2 Database library to fetch count. I just found that ORM is not preferred to fetch results when data is huge. Please any help would be appreciated

$members = $this->getEntityManager()->getRepository('User\Entity\Members')->findBy(array('id' => $id, 'status' => '1'));
$membersCnt = sizeof($members);

or

$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('count(p)')
   ->from('User\Entity\Members', 'p')
   ->where('p.id = '.$id)
   ->andWhere('p.status = 1');

$membersCnt = $qb->getQuery()->getSingleScalarResult();

Answer 1

Comparison

1) Your EntityRepository::findBy() approach will do this:

• Query the database for the rows matching your criteria. The database will return the complete rows.
• The database result is then transformed (hydrated) into full PHP objects (entities).

2) Your EntityManager::createQueryBuilder() approach will do this:

• Query the database for the number of rows matching your criteria. The database will return a simple number (actually a string representing a number).
• The database result is then transformed from a string to a PHP integer.

You can safely conclude that option 2 is far more efficient than option 1:

• The database can optimize the query for counting, which might make the query faster (take less time).
• Far less data is returned from the database.
• No entities are hydrated (only a simple string to integer cast).

All in all less processing power and less memory will be used.

Security comment

Never concatenate values into a query! This can make you vulnerable to SQL injection attacks when those values are (derived from) user-input.

Also, Doctrine2 can't make use of prepared statements / parameter binding, which can lead to some performance-loss when the same query is used often (with or without different parameters).

In other words, replace this:

->where('p.id = '.$id)
->andWhere('p.status = 1')

with this:

->where('p.id = :id')
->andWhere('p.status = :status')
->setParameters(array('id' => $id, 'status' => 1))

or:

->where($qb->expr()->andX(
    $qb->expr()->eq('p.id', ':id'),
    $qb->expr()->eq('p.status', ':status')
)
->setParameters(array('id' => $id, 'status' => 1))

Additionally

For this particular query, there's no need to use the QueryBuilder, you can use straight DQL in stead:

$dql = 'SELECT COUNT(p) FROM User\Entity\Members p WHERE p.id = :id AND p.status = :status';
$q = $this->getEntityManager()->createQuery($dql);
$q->setParameters(array('id' => $id, 'status' => 1));

$membersCnt = $q->getSingleScalarResult();