How can I improve KnpPaginatorBundle and Doctrine speed?

Question

I have a huge product table (100k+ rows) and in my controller I have the following function:

public function indexAction(Request $request)
    {
        $findProducts = $this->getDoctrine()
           ->getRepository("StockBundle:Product")->findAll();

        $paginator  = $this->get('knp_paginator');
        $producten = $paginator->paginate(
            $findProducts,
            $request->query->getInt('page', 1)/*page number*/,
            20/*limit per page*/
        );

        return $this->render('StockBundle:Default:index.html.twig', 
             array('producten' => $producten));
    }

The problem is the page takes about 11-12 seconds to load and consumes 233MB of RAM.

What can I do to improve the speed and reduce memory?

This is my entity:

/**
 * Product
 *
 * @ORM\Table()
 * @ORM\Entity(repositoryClass="Namespace\StockBundle\Entity\ProductRepository")
 */
class Product
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="naam_nl", type="string", length=255)
     */
    private $naamNl;

    /**
     * @var string
     *
     * @ORM\Column(name="naam_fr", type="string", length=255)
     */
    private $naamFr;

    /**
     * @var string
     *
     * @ORM\Column(name="naam_en", type="string", length=255)
     */
    private $naamEn;

    /**
     * @var string
     *
     * @ORM\Column(name="productnummer", type="string", length=255)
     */
    private $productnummer;

    /**
     * @var float
     *
     * @ORM\Column(name="prijs", type="float")
     */
    private $prijs;



    /**
     * @var string
     *
     * @ORM\Column(name="merk", type="string", length=255)
     */
    private $merk;

    /**
     * @ORM\OneToOne(targetEntity="Namespace\StockBundle\Entity\ProductInventory", cascade={"persist"})
     * @ORM\JoinColumn(name="productinventory_id", referencedColumnName="id")
     * 
     */
    private $productinventory;

The table structure is created by doctrine and looks like this:

CREATE TABLE `product` (
  `id` int(11) NOT NULL,
  `naam_nl` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `productnummer` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `prijs` double NOT NULL,
  `merk` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `productinventory_id` int(11) DEFAULT NULL,
  `naam_fr` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `naam_en` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

Right now, you are calling findAll() which will retrieve all records from the database and then synthesize them into objects. This takes a lot of time that is wasted because most of these objects are never used again as you only output one page at a time.

What you should do instead is pass a query builder to the paginator which should then be able to create a query that only gets the objects you actually need for the current page.

public function indexAction(Request $request)
    {
        $findProducts = $this->getDoctrine()
           ->getRepository("StockBundle:Product")->createQueryBuilder("p");

        $paginator  = $this->get('knp_paginator');
        $producten = $paginator->paginate(
            $findProducts,
            $request->query->getInt('page', 1)/*page number*/,
            20/*limit per page*/
        );

        return $this->render('StockBundle:Default:index.html.twig', 
             array('producten' => $producten));
    }