Doctrine 2: Querying Potentially Large Numbers of Records

Question

I am building a dashboard for my website where users can see the number of records in a database table by All Time, Last 12 Months, Last 6 Months and Last 3 Months. It occurs to me that this could bog down the server given the fact there could be thousands of records and multiple users requesting these figures at the same time.

Is there an optimal way to do this using Doctrine 2? Right now, the function I have to achieve this looks like this:

/**
 * Search group instruction sessions based on the number of months selected from the dashboard gauges.
 *
 * @param int $number_months (how many months back to search)
 * @param User $user_obj (optional- the user being searched)
 * @return Response
 */
private function __recordsByNumberOfMonths($number_months, $user_obj = NULL){       
    $from = date('Y-m-d', strtotime('-'.$number_months.' months'));

    //get surveys by a specific user
    if($user_obj != NULL){
        $query = $this->entityManager->createQuery('SELECT i FROM GroupInstruction i WHERE i.librarian = :librarian AND i.instruction_date > :from ORDER BY i.instruction_date DESC');
        $query->setParameter('from', $from);
        $query->setParameter('librarian', $user_obj);
    } 
    //get all surveys by all users
    else {
        $query = $this->entityManager->createQuery('SELECT i FROM GroupInstruction i WHERE i.instruction_date > :from ORDER BY i.instruction_date DESC');
        $query->setParameter('from', $from);
    }
    $results = $query->getResult();

    $total_results = count($results);

    return $total_results;
}

Any tips on optimization (or if I'm doing this completely backwards) would be appreciated!

Run 'explain' on the query and see what you can do to optimize it. — Len_D, Dec 01 '14 at 15:11
See here on how to get a count without returning all the records: http://stackoverflow.com/questions/9214471/count-rows-in-doctrine-querybuilder/9215880#9215880. — Cerad, Dec 01 '14 at 16:48

score 2 · Answer 1 · answered Dec 01 '14 at 15:16

2

I think an array hydration would be way faster than object hydration:

$query->getResult( Doctrine\ORM\Query::HYDRATE_ARRAY );

This way you'll get your data as an array of primitive types instead of array of objects.

answered Dec 01 '14 at 15:16

András

693
5
17

This is actually quite helpful when I have 15+ mil records. – Mecanik Oct 09 '18 at 09:56

score 0 · Answer 2 · answered Dec 02 '14 at 12:43

And cache it using doctrine :) http://doctrine-orm.readthedocs.org/en/latest/reference/caching.html And secondly, if you don't want fetch data, prepare queries which count's objects - or use Doctrine Paginator:

$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
$totalRows = $paginator->count();

Doctrine 2: Querying Potentially Large Numbers of Records

2 Answers2