getArrayResult() exceeding maximum execution time on symfony

Question

I am currently trying to get data from a repository to show it in a Bootgrid, but I always exceed the maximum execution time (120 seconds).

I use the following code:

In my Javascript:

$params.table.bootgrid({
    ajax: true,
    url: $params.authoritiesDataPath //the url is correct here, I verified it
})

In my controller:

/**
 * Authorities Data
 *
 * @param Request $request
 * @return JsonResponse
 */
public function dataAction(Request $request)
{
    $this->denyAccessUnlessGranted(RoleVoterHelper::SECTION_COMPANY_VIEW);
    try {
        $data = $this->getDoctrine()->getRepository('BugTrackerModelBundle:Authority')->findByParameters(
            $request->request->all()
        );
    } catch (\Exception $e) {
        $data = [
            'status'   => 'error',
            'error'    => $e->getMessage(),
            'rows'     => [],
            'current'  => 1,
            'rowCount' => 0,
            'total'    => 0,
        ];
    }

    return new JsonResponse($data);
}

in my repository:

public function findByParameters(array $parameters)
{
    $queryBuilder = $this->createQueryBuilder('a')
        ->select('COUNT(a.id)');

    if (!empty($parameters['searchPhrase'])) {
        $queryBuilder->where('a.name LIKE :search')
            ->setParameter('search', '%'.$parameters['searchPhrase'].'%');
    }

    $parameters['rows'] = array();
    $parameters['current'] = isset($parameters['current']) ? (int)$parameters['current'] : 0;
    if ($parameters['total'] = (int)$queryBuilder->getQuery()->getSingleScalarResult()) {
        $queryBuilder->select('a.id', 'a.name', 'COUNT(DISTINCT c.id) as companies',
            'COUNT(DISTINCT u.id) as users', 'COUNT(DISTINCT dl.id) as deviceLists',
            'COUNT(DISTINCT d.id) as devices', 'a.name as authority', 'a.enabled')
            ->leftJoin('BugTrackerModelBundle:Company', 'c', Join::WITH, 'c.authority = a.id')
            ->leftJoin('BugTrackerModelBundle:Device', 'd', Join::WITH, 'd.authority = a.id')
            ->leftJoin('BugTrackerModelBundle:Device\DeviceList', 'dl', Join::WITH, 'dl.authority = a.id')
            ->leftJoin('BugTrackerModelBundle:User', 'u', Join::WITH, 'u.company = c.id')
            ->groupBy('a.id');

        if (!empty($parameters['sort'])) {
            $order = reset($parameters['sort']) ?: 'ASC';
            switch (key($parameters['sort'])) {
                case 'name':
                    $queryBuilder->orderBy('c.name', $order);
                    break;
                case 'users':
                    $queryBuilder->orderBy('users', $order);
                    break;
                case 'company':
                    $queryBuilder->orderBy('companies', $order);
                    break;
                case 'enabled':
                    $queryBuilder->orderBy('a.enabled', $order);
                    break;
                default:
                    $queryBuilder->orderBy('c.id', $order);
            }
        }

        if (isset($parameters['rowCount']) && $parameters['rowCount'] > 0) {
            $queryBuilder->setFirstResult(($parameters['current'] - 1) * $parameters['rowCount'])
                ->setMaxResults($parameters['rowCount']);
        }

        $parameters['rows'] = $queryBuilder->getQuery()->getArrayResult();
    }

    return $parameters;
}

I tried returning arrays pretty much everywhere to find where the loop was (I'm guessing that it's a loop since my code is usually very fast), and it seems to come from the following line

$parameters['rows'] = $queryBuilder->getQuery()->getArrayResult();

I tried returning $queryBuilder->getQuery() and it took a few seconds, so the issue is with getArrayResult

The query it returned when I printed the sql:

SELECT a0_.id AS id_0, a0_.name AS name_1, COUNT(DISTINCT c1_.id) AS sclr_2, COUNT(DISTINCT u2_.id) AS sclr_3, COUNT(DISTINCT d3_.id) AS sclr_4, COUNT(DISTINCT d4_.id) AS sclr_5, a0_.name AS name_6, a0_.enabled AS enabled_7 FROM authority a0_ LEFT JOIN client_company c1_ ON (c1_.authority_id = a0_.id) LEFT JOIN device d4_ ON (d4_.authority_id = a0_.id) LEFT JOIN device_list d3_ ON (d3_.authority_id = a0_.id) LEFT JOIN user u2_ ON (u2_.company_id = c1_.id) GROUP BY a0_.id LIMIT 5 OFFSET 0

Here is the "explain" when I ran the query in PhpMyAdmin: Explain

I really don't understand why it takes so much time, I never had issues with getting data from my database and there is no loop in all that code that could cause it. Is there any way for me to test other things to understand why it takes so much time and change it?

Answer 1

have you indexed the correct columns? this should reduce the querying time, additionally, it could be that the results set is so complex it's taking a while to hydrate the array which is causing the time duration you're seeing.

Answer 2

Looking at the query produced, you have 4 joins in it and it hydrates 5 entities BugTrackerModelBundle:Authority, BugTrackerModelBundle:Company, BugTrackerModelBundle:Device, BugTrackerModelBundle:Device\DeviceList and BugTrackerModelBundle:User.

That `s a lot of joins,as "the process of hydration becomes extremely expensive when more than 2 LEFT JOIN operations clauses". See Reference. In short, the result set is large and doctrine takes too long to map it to Entities.

My assumption is that the ORM is taking too long to normalize the result set returned by the query.

My advice is to split the query to 2 with maximum 2 joins each:

$queryBuilder = $this->createQueryBuilder('a')
            ->select('a.id', 'a.name', 
            'COUNT(DISTINCT c.id) as companies',
            'COUNT(DISTINCT u.id) as users',
            'a.name as authority', 'a.enabled')
            ->leftJoin('BugTrackerModelBundle:Company', 'c', Join::WITH, 'c.authority = a.id')
            ->leftJoin('BugTrackerModelBundle:User', 'u', Join::WITH, 'u.company = c.id')
            ->groupBy('a.id');

$queryBuilder = $this->createQueryBuilder('a')
            ->select('a.id', 'a.name',
            'COUNT(DISTINCT dl.id) as deviceLists',
            'COUNT(DISTINCT d.id) as devices', 'a.name as authority', 'a.enabled')
            ->leftJoin('BugTrackerModelBundle:Device', 'd', Join::WITH, 'd.authority = a.id')
            ->leftJoin('BugTrackerModelBundle:Device\DeviceList', 'dl', Join::WITH, 'dl.authority = a.id')
            ->groupBy('a.id');

Hope this helps.