php Question posted in
Official manual can be found here.

Php – Show a lot of data with ORM doctrine symfony

I try make charge test with a symfony web app. I’ve an Entity with 13 fields including 1 primary key and 7 foreiner keys. I’ve injected 40K data.

My controller use a findAll() and KNP Paginator with a maximum of 10 lines by page.
The page display takes long time (between 15 / 30 seconds) here is the result of my profiler :

Performance metrics :

  • total execution times : 29680 ms
  • peak memory usage : 154mb

I’ve try without relation foreiner keys, it’s better but not yet acceptable.
Performance metrics :

  • total execution times : 5600ms
  • peak memory usage : 88mb

What do you recommend for displaying a lot of data with pagination? Thank for your help.

Have a nice day.

<?php

namespace AppControllerAdmin;

use AppRepositoryChargeRepository;
use KnpComponentPagerPaginatorInterface;
use SymfonyBundleFrameworkBundleControllerAbstractController;
use SymfonyComponentHttpFoundationRequest;
use SymfonyComponentHttpFoundationResponse;
use SymfonyComponentRoutingAttributeRoute;

class ChargeController extends AbstractController
{
    #[Route('/admin/charge', name: 'app_admin_charge')]
    public function index(ChargeRepository $repository, PaginatorInterface $paginator, Request $request): Response
    {
        $data = $paginator->paginate(
            $repository->findAll(),
            $request->query->getInt('page', 1),
            10
        );
       // dd($myTickets);
        return $this->render('admin/charge/index.html.twig', [
            'data' => $data
        ]);
    }
}
Tags:

2

Answers


  1. 0

    According to their documentation, you should paginate over the query builder itself, not over the result of findAll. When you use findAll, you load all entities from the database and hydrate them, this takes a lot of time for 40k rows.

    Something like this could already help:

    $data = $paginator->paginate(
  $repository->createQueryBuilder('charge'),
  $request->query->getInt('page', 1),
  10
);

    Using this code, the paginator adds the neccessary pagination limit to the database query itself, such that only those items are loaded that are also displayed on the current page

    Login or Signup to reply.
  2. 0

    Selecting all data with findAll() method is not good practice when you have a large number of data. Try with custom pagination with firstResult and maxResults. Here is an example:

    /**
 * @param int $page
 * @param int $maxResults
 * @return Charge[]
 */
public function getPaginated(int $page = 1, int $maxResults = 50): array
{
    return $this->createQueryBuilder('c') // add where clauses if you need
        ->setFirstResult(($page - 1) * $maxResults)
        ->setMaxResults($maxResults)
        ->getQuery()
        ->getResult();
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search