Count Performance of DoctrineORMAdapter COUNT query and large record sets

[calumbrodie]

This issue is pertinent to Pagerfanta\Adapter\DoctrineORMAdapter.

I've run into performance problems with the way that the 'count' part of the pagination query is being constructed as referenced in the following issues (across multiple projects) (using MySQL):

KnpLabs/knp-components#77
doctrine/orm#298
KnpLabs/knp-components#65
KnpLabs/KnpPaginatorBundle#202
beberlei/DoctrineExtensions#59

This issue will be relevant to anyone using the paginator with MySQL and anything approaching a large number of rows in a database (for me 80k) - when it can take > 100 seconds to execute the count part of the query on a modest amount of data.

My goal here is to simplify the poorly performing query being generated, given that I'm not performing any join or group conditions (and I appreciate why the below structure may be required if this were the case), I have no need for either the 'distinct' subquery, nor the inner subquery it selects from. I'm trying to get from this:

SELECT 
  COUNT(*) AS dctrn_count 
FROM 
  (
    SELECT 
      DISTINCT id0 
    FROM 
      (
        SELECT 
          o0_.id AS id0, 
          o0_.ordr_id AS ordr_id1, 
          ...etc
        FROM 
          ordr_status o0_
        WHERE
          some_property = 'something'
      ) dctrn_result
  ) dctrn_table

to this:

SELECT COUNT(*) AS dctrn_count
    FROM ordr_status o0_
    WHERE some_property = 'something'

or this (I don't care about the DISTINCT as it doesn't seem to hurt performance):

SELECT COUNT(DISTINCT id0) AS dctrn_count
    FROM ordr_status o0_
    WHERE some_property = 'something'

It seems the way to do this is to avoid using the 'Doctrine\ORM\Tools\Pagination\CountOutputWalker' within the Doctrine paginator.

I've tried a variety of changing query walkers, query hints, and changing options in the paginator, and I've found I can achieve what I want by doing this in the DoctrineORMAdapter constructor:

$this->paginator = new DoctrinePaginator($query, false);
$this->paginator->setUseOutputWalkers(false);

So:
Could/Should the option to disable use of output walker be available in the adapter constructor:

public function __construct($query, $fetchJoinCollection = true, $useOutputWalkers = true)
{
    if (class_exists('Doctrine\ORM\Tools\Pagination\Paginator')) {
        $this->paginator = new DoctrinePaginator($query, $fetchJoinCollection);
        if (!$useOutputWalkers) {
          $this->paginator->setUseOutputWalkers(false);
        }
    } else {
        $this->paginator = new LegacyPaginator($query, $fetchJoinCollection);
    }
}

OR is there an alternative way to get this behaviour (such as setting a hint on the query before passing it to the adapter) that I've missed.

[jessevanmuijden]

+1

[afterdesign]

Well it looks like there is a quick fix.
Just set distinct option to false and it's going to use select count(*) from like:

$paginator->paginate(
    $query,
    $page,
    10,
    array('distinct' => false)
);

Still I think this is error to use complex query by default.

[rlavolee]

I think this commit solved this issue.

• SHA d111784

[calumbrodie]

I'd agree this looks to be resolved

[pablodip]

Closing then.

[ioleo]

@afterdesign where did you get the paginate method from? I'm looking at Pagerfanta classes and I can't find it. Isn't that code for KnpPaginatorBundle?

[nmeirik]

To clearify: If your code looks something like this:

$pagerfanta = new Pagerfanta(new DoctrineORMAdapter($query));

You'll want to change it to something like this (to make use of the commit mentioned above):

$pagerfanta = new Pagerfanta(new DoctrineORMAdapter($query, true, false));

[ioleo]

@nmeirik I've added the true,false to the constructor.. however I'm still getting errors (could not count query, due to leftJoin.. )

[nmeirik]

@loostro To me it sounds like your query might be too complex to benefit from the simplified count. But this is slightly over my head, so hopefully someone else can shed a better light on this.

[noetix]

I'm also having mixed results with the simplified count option (number of bound variables does not match number of tokens).

Is there a way we can set a hint, like KnpLabs/KnpPaginatorBundle#27 (comment)

[stof]

@loostro can you show the DQL query you are trying to paginate ?

[ioleo]

@stof I don't remember the exact query now (I had to workaround this), but I can describe what caused my problems.

My entities

/*
 * @ORM\Table(name="contract")
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(name="discr", type="string")
 */
abstract class Contract {}

/* Not mapped */
abstract class DonationContract extends Contract {}

/*
 * @ORM\Entity()
 * @ORM\Table(name="contract_donation_in_money")
 */
class DonationInMoneyContract extends DonationContract {}

/**
 * @ORM\Entity()
 * @ORM\Table(name="contract_donation_in_kind")
 */
class DonationInKindContract extends DonationContract {}

/* Not mapped */
abstract class SpecifiedTaskContract extends Contract {}

/**
 * @ORM\Entity()
 * @ORM\Table(name="contract_specified_task_with_natural_person")
 */
class SpecifiedTaskWithNaturalPersonContract extends SpecifiedTaskContract {}

/**
 * @ORM\Entity()
 * @ORM\Table(name="contract_specified_task_with_company")
 */
class SpecifiedTaskWithCompanyContract extends SpecifiedTaskContract {}

The table I was querying was contract. The functionality was a list of all contracts (no matter what sub-type). The problem emerged, when I wanted to allow the user to "order by costsSum", where "costsSum" is not a field on any of these entities. I wanted to create a calculated column (with a subselect), to use it for sorting/filtering, but only hydrate (fetch) the "contracts" (after the results are sorted the "virtual column" is not needed). However, Pagerfanta seems to have a problem with "extra" columns, it seems it cannot "count" my "Contracts" becouse of the extra column.