That’s why today we want to tell you about our recent bug fixes related to the object tree performance in large databases.
Why Pimcore is worth your attention
In a nutshell, PIM solutions based on Pimcore are a great way to manage and optimise your entire web presence and eCommerce storefronts. They are particularly useful in case of complex product catalogues with large number of attributes, multimedia or relations with other products. But they also lend themselves well to a whole variety of business needs.
Until version 4 Pimcore was based on Zend, one of the most popular PHP frameworks. Pimcore 5 is based on the latest full stack of Symfony and uses various components from this PHP framework.
What’s important is that Pimcore comes with an open GPL licence. Not restricted by the proprietary software licensing, it can be easily customised, integrated and expanded to satisfy the enterprise-grade requirements.
How we spotted Pimcore performance issues
During our Pimcore 5 tests with a database of approx. 1 400 000 objects, some problems with object tree performance became evident.
Firstly, we noticed that it was taking about 3 seconds to expand a tree with folders containing large quantities of objects – we’re talking about approx. 100,000 elements each.
Then we observed that this time was increasing to 9 seconds in case of folders with 400,000 objects.
We carried out profiling which confirmed these performance issues. It also revealed two main problems that needed to be tackled.
Issue #1: Searching all children slows down the subtree opening
When expanding a tree, Pimcore was retrieving lists of sub-elements and then checking whether each of them had children. This step was necessary to decide whether to display the plus icon next to their names.
To do so, an SQL query had to be run for all the sub-elements. Yet, only the first result would be checked.
Our developer added LIMIT 1 to the query. As a result, when checking if a given element has sub-objects, Pimcore now only looks for one child. This has speeded up the subtree opening from 3 seconds to 200 milliseconds. That’s 15 times faster!
Issue #2: Children count slows down the tree opening
A tree pagination display was only possible if the selected folder comprised a given number of elements.
The SQL COUNT used to ascertain that figure was deploying the WHERE IN clause which only counted objects of the selected type. It proved particularly slow in case of large amounts of data – the query itself could take as much as 3 seconds!
Since it may be sometimes required, all types of objects had to be counted when fetching a number of the tree sub-elements.
To enable it,our developer modified themethod Pimcore\Model\DataObject\AbstractObject\Dao::getChildAmount() and allowed the first parameter to be null.
As a result, the WHERE IN doesn’t get added to the query, reducing its time to less than 200 milliseconds.
This in turn means that expanding a tree element now takes 6 seconds instead of 9. Obviously, there’s still room for improvement, but that’s the way to go.
Marketing Project Manager at Unity Group. Ewa graduated with an MA in Digital Media from Goldsmiths, University of London and since then has coordinated numerous international communications projects, predominantly for tech companies & startups. All-year-round cyclist, iberophile, avid fan of all things vegan.