Default Pagination

[richardjennings]

Is there a way that the default paginator setup, i.e pagination wrapper helper => resource model helper, can be made or configured to work efficiently? After noticing a rather severe performance penalty with large tables, I wrote my own. As standard it seems that the Selectable paginator will pull all rows (matching criteria) from the database and use the count function to count them. I cannot see how that mechanism can be expected to work in anything but a trivial scenario. Any clues? Cheers

[bakura10]

Hi,

This is a known issue that needed to be solved in doctrine. I've made a lot of fixes lately to doctrine and the situation in doctrine 2.5 is much better.

Please update your dependencies to be on the development branch of Doctrine.

You may need to alter a bit your mapping to on associations (see the performance part in the cookbook).

Envoyé de mon iPhone

Le 28 mai 2014 à 09:24, Richard Jennings notifications@github.com a écrit :

Is there a way that the default paginator setup, i.e pagination wrapper helper => resource model helper, can be made or configured to work efficiently? After noticing a rather severe performance penalty with large tables, I wrote my own. As standard it seems that the Selectable paginator will pull all rows (matching criteria) from the database and use the count function to count them. I cannot see how that mechanism can be expected to work in anything but a trivial scenario. Any clues? Cheers

— Reply to this email directly or view it on GitHub.

[richardjennings]

Hi Thanks. What improvements are available in Doctrine 2.5 that improve the pagination performance issue in particular? Regards

[bakura10]

Hi,

I've made several improvements so that for ToMany associations, it no longer loads everything into memory when you want to do operation like "COUNT", but rather do an optimized database COUNT. It also triggers an optimized database call for the "exists" method on a collection. Expects more improvements in the future.

For now, Doctrine 2.5 include improvements:

• Matching an ObjectRepository will always return a Lazy Collection, which means that any count call will lead to an optimized count.
• Matching an associations (for instance /users/2/tweets), you will receive a "Selectable" instance. If the "Tweet" association is marked as "EXTRA_LAZY", it will return a Lazy Collection, so that the paginator will trigger an optimized COUNT call.

The situation that is not taken into account is ManyToMany. For now it will still load the whole collection. I'm going to try to fix that before 2.5 releases, but I'm not sure, I'm kinda lacking time.

Doctrine 2.5 also brings a whole new feature called Secondary Level Cache. This will allow to have caching built-in into ZfrRest. For instance, if you want to cache a User entity, you will only need to add an @ORM\Cache annotation to your entity, and boom, ZfrRest will cache into it!

In overall, ZfrRest is not really usable before Doctrine 2.5 (except for very trivial cases) and I'll likely bump the minimum dependency to 2.5 once it is available.

Let me know if you need any more information.

[bakura10]

Does it work for you ? :)

[bakura10]

I'm closing. If you still have issue please tell me :).