CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormpagination
nehalist
nehalist

Reputation: 1496

Slow Doctrine pagination when joining

My table structure contains a lot of tables which are in relation with the user table:

user
user_mails
user_addresses
user_phonenumbers
[...]

So a user can have as many mails, addresses, phone numbers as he wants to. There's an extensive search form to search members, including joined values from the other tables; for example it's possible to search for a users address or his phone number.

To make it possible to search for values in other tables I'm joining all related tables when searching a user.

Since some users have a decent amount of data this means the amount of rows from the query is around ~30k. Actually that's no problem, the query is executed in < 1 second, which is totally fine.

It starts getting weird as soon as the Paginator component comes into play. My repository method looks similar to this:

// User Repository
public function findUserByCriteria($criteria, $offset, $limit) {
    $qb = $this->createQueryBuilder('u');

    $qb->leftJoin('u.emails', 'e')->addSelect('u');
    $qb->leftJoin('u.addresses', 'adr')->addSelect('adr');
    $qb->leftJoin('u.phonenumbers', 'phn')->addSelect('phn');
    // 4 more joins

    // ...
    // the actual search implementation goes here...
    // ....

    $qb->setFirstResult($offset);
    $qb->setMaxResults($limit);

    return new Paginator($qb, true);
}

The problem seems to come from the second argument of the Paginator constructor, fetchJoinCollection. As long as it's on true it takes around ~10s to find some users (those with a lot of data), while it just takes around ~1s when it's on false. The problem when it's set to false is that the entire pagination is messed up.

I've found out that getting rid of the joins at the top of the method also decreases execution time to around ~1s, but then it's no longer possible to search within these tables.

What is happening here?

Upvotes: 1

Views: 1213

Answers (2)

Hokusai
Hokusai

Reputation: 2349

You can try to avoid use Paginator, it is a pain with large amount of data.

In that cases I do the next: Firstly I calculate the total of data (COUNT) and use that value for my pagination and secondly I get the data with the query.

This example is using DQL instead of Query builder but you can get the idea:

$countDql = "SELECT COUNT (e.id)
             FROM YourBundle:Entity e
             WHERE e.condition = :yourCondition";

$dataDql = "SELECT e
             FROM YourBundle:Entity e
             WHERE e.condition = :yourCondition";

$total = $em->createQuery($countDql)->getSingleScalarResult(); //Add also  setParameter() if needed
$data = $em->createQuery($dataDql)->getResult();

Hope it helps.

Upvotes: 1

Renan Taranto
Renan Taranto

Reputation: 572

Joins are heavy, and Left Joins are even heavier.

You could change your findUserByCriteria method to add the joins as they are needed according to the $criteria argument. If there's no criteria for address for example, you don't need to join with the Address entity.

And if your queries are still way too slow, you can directly use PDO to create them, which is faster than using the querybuilder or DQL.

Upvotes: 0

Related Questions