Reading time: 3 minutes
How our team improved Pimcore 5 performance by fixing object tree issues
20 / 03 / 2018
Pimcore is one of the leading PIM solutions (Product Information Management). Available as an open-source platform, it’s far more flexible and cost-effective than other tools of this class.
We’ve used it a lot in some of our e-commerce and omnichannel projects. Having worked so much with Pimcore, our developers started to contribute to its development as well.
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!
You can read more about it in this pull request on Github.
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 the method 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.
Check this pull request to learn more.