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;

It does not only depends doctrine, could you post your SQL schema table ? — Sylvain Martin, Aug 08 '15 at 16:56

score 6 · Accepted Answer · answered Aug 08 '15 at 17:29

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

That's it, now my page takes about 300ms (in app_dev.php) and only uses about 19MB of memory. — yeouuu, Aug 08 '15 at 17:36

score 0 · Answer 2 · answered Aug 08 '15 at 17:27

You'd better to use the Symfony2 profiler to to debug your query, you shoud first add index on your fields and a foreign key on the field productinventory_id.

Index could be use like this :

/**
 * @Entity
 * @Table(name="user",indexes={
 *     @Index(name="email_idx", columns={"email"})
 * })
 */
class User
{
    ...
}

To optimize this specific query in your indexAction, you could do a select with only need column you want to show in your list and also write your query instead of a DQL one. for example :

// Get connection
$conn = $entityManager->getConnection();

// Get table name
$meta = $entityManager->getClassMetadata(User::class);
$tableName = $meta->getTableName();

// Get random ids
$sql = "SELECT id AS id FROM $tableName WHERE active = true ORDER BY RAND()";
$statement = $conn->executeQuery($sql);
$ids = array_map(function ($element) {
    return $element['id'];
}, $statement->fetchAll());

return $ids;

Finaly, you should have to enable cache tools like apc or memcache to free your sql server.

While all of these are valid DB access optimization strategies none of them really apply here. The main issue is that the asker is fetching way too many entries. Adding an index will not help here at all and suggesting to switch to raw SQL queries when all you need is 20 objects with 8 properties each is simply unnecessary. — Chris, Aug 08 '15 at 17:32

How can I improve KnpPaginatorBundle and Doctrine speed?

2 Answers2