Will I get better performance if I use COUNT query instead of looping through entities in Symfony 4?

Question

For example I need to get review count, one way of doing it is like this:

public function getActiveReviews()
{
    return $this->getReviews()->filter(function(Review $review) {
        return $review->isActive() && !$review->isDeleted();
    })->count();
}

Another way is to use Query Builder like this:

$qb = $this->createQueryBuilder('r')
      ->where('r.active = true')
      ->andWhere('r.deleted = false')
      ->select('count(r)')

Which way will give me better performance and why?

And here is an oldie but goodie answer which slight refines your count(r) query to return a single scalar count improving performance just a tiny bit. https://stackoverflow.com/questions/9214471/count-rows-in-doctrine-querybuilder/9215880#9215880 And yes, using the database will be many times faster unless you already need the reviews for something else. — Cerad, Feb 21 '19 at 12:16

score 4 · Accepted Answer · answered Feb 21 '19 at 09:59

Of course count query will be faster because it will result into single SQL query that will return single value.

Iteration over entities will require:

Run of SQL query for fetching data rows
Actual fetching of data
Entity objects instantiation and persisting fetched data into them

Depending on amount of affected data difference may be very big.

The only case when running count over entities may be fast enough is a case when you already have all entities fetched and just need to count them.

score 1 · Answer 2 · edited Jul 22 '21 at 13:27

It depends on Symfony count() implementation, but you probably will. Usually RDBMS counts its rows quicker internally, and it requires much less resources.

In first case you request a whole rowset, which can be huge, then you iterate through it, you apply your filter function to every row, and then you just look at your filtered rowset size and drop everything. (But, of course, this might be optimized by your framework somehow).

In second case you just ask the database how much rows it has satisfying the criteria. And DB returns you a number, and that's all.

As other people said, the only case when first choice might be quicker is when you have already cached rowset (no need to connect to DB) — and when your DB connection is very slow at the same time.

I saw databases which were slow on some COUNT requests (Oracle) on big tables, but they were still faster than PHP code on same rowset. DBs are optimized for data filtering and counting. And usually COUNT request are very fast.

Will I get better performance if I use COUNT query instead of looping through entities in Symfony 4?

2 Answers2