Show a lot of data with ORM doctrine symfony

Question

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 App\Controller\Admin;

use App\Repository\ChargeRepository;
use Knp\Component\Pager\PaginatorInterface;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;

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
        ]);
    }
}

Answer 1

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