Shopware's performance can increase by up to 30%

[pavel-rossinsky]

Please describe the feature you would like to see implemented.

This request is neither a feature nor a bug. But it's beneficial for the SW team and community to be aware of that. The problem is relevant for both Vanilla Shopware and customized SW stores. Sorry if I have placed it in the wrong place.

Short story:

There is a way to improve the performance of all processes that run database queries by up to 30% (page generation, indexing, product updates, and so forth). For this, patching of Doctrine\DBAL\SQLParserUtils::collectPlaceholders() is required. For testing the potential fix, I've created a patch. How it was tested is described below.

The steps to reproduce the problem and measure the time taken by SQLParserUtils:

1. Find the class Doctrine\DBAL\SQLParserUtils, navigate to the method collectPlaceholders().

Place right before the foreach loop:

$time1 = -microtime(true);

Place after the loop and before the return:

// Profilers aren't used since, when installed and activated, they can increase the function execution time
$time1 += microtime(true);

$key = 'speed_test3';
$timeTotal = apcu_fetch($key, $success);

if ($success) {
    $timeTotal = floatval($timeTotal);
    $timeTotal += $time1;
} else {
    $timeTotal = $time1;
}

apcu_store($key, $timeTotal, 3600);

if (!isset($GLOBALS['total_time1'])) {
    $GLOBALS['total_time1'] = 0.00;
}
$GLOBALS['total_time1'] += $time1;

syslog(1, sprintf('__page_wall_time__ %f ms, __total_time__ %f ms', $GLOBALS['total_time1'] * 1000, $timeTotal * 1000));

Run

sudo tail -f /var/log/syslog

If Syslog is not installed

sudo apt-get update
sudo apt-get -y install rsyslog
sudo service rsyslog start

Clear the cache.
Now you can measure how long the execution of the collectPlaceholders() takes in total and per executed request.

2. Applying the patch, by which the issue is significantly reduced, to your dev or local system and measuring the performance before and after.

Reporting the issue to https://github.com/doctrine/dbal doesn't make much sense because doctrine/dbal 2.13 isn't being supported, and the file has been removed in doctrine 3.0.

What has led to finding the issue:

After installing a blog plugin, the performance of product and category pages dropped from 475 to 650 ms.

Such a performance drop was unexpected because the custom plugin didn't add any data to the database and wasn't doing much with product pages. It turned out that the custom plugin added several additional criteria to the query attempting to select data from its empty database tables.
Debugging has shown that the performance degradation was due to the length of the database query string.

It takes up to 100ms to find placeholders in one long query!
The more extensions and plugins you have installed, the worse everything gets.

A screenshot of the performance profile of one product page (cold cache). The site runs on AWS c5.xlarge EC2.

As you can see on the screenshot, the SQLParserUtils::getUnquotedStatementFragments() (marked with red) eats up a lot of time before running any query.

Conclusions drawn

In general, up to 30% of page generation time is taken by the execution of the regular expression:
vendor/doctrine/dbal/lib/Doctrine/DBAL/SQLParserUtils.php:275

    private static function getUnquotedStatementFragments($statement)
    {
        $literal    = self::ESCAPED_SINGLE_QUOTED_TEXT . '|' .
            self::ESCAPED_DOUBLE_QUOTED_TEXT . '|' .
            self::ESCAPED_BACKTICK_QUOTED_TEXT . '|' .
            self::ESCAPED_BRACKET_QUOTED_TEXT;
        $expression = sprintf('/((.+(?i:ARRAY)\\[.+\\])|([^\'"`\\[]+))(?:%s)?/s', $literal);

        preg_match_all($expression, $statement, $fragments, PREG_OFFSET_CAPTURE);

        return $fragments[1];
    }

The getUnquotedStatementFragments() method is invoked only by collectPlaceholders(), whereas collectPlaceholders collects positions of placeholders in SQL queries. The regular expression has become complicated and slow since it's written to support many SQL dialects for searching placeholders in exotic queries like this one.
'SELECT table.column1, ARRAY[\'3\']::integer[] FROM schema.table table'

The doctrine's unit test for the SQLParserUtils can be here. To find the right source reference where the SQLParserUtils and SQLParserUtilsTest classes still exist, check https://packagist.org/packages/doctrine/dbal#2.13.9.

The results of applying the fix to the SQLParserUtils

All unit tests of SQLParserUtilsTest have passed successfully.
All unit and integration tests of Shopware have passed successfully.

Tested against the latest version of https://github.com/shopware/platform with and without the change.
System:
Macbook M1 32GB RAM, Docker, PHP 8.0 (8.1 doesn't solve the problem either)

With extra data

bin/console framework:demodata --categories=8 --customers=100 --manufacturers=1500 --properties=15000 --products=20000 --reviews=10000 --media=1000 --attribute-sets=20 

With the cold cache:
Product page creation time - reduced by 28%.
Category page creation time - reduced by 20%.
Integration tests execution time - reduced by 25%.

Tested against a real Shopware 6.4.14 shop with 50k products * 3 sales channels
System: PHP 7.4 running on AWS c5.xlarge instances
Product page creation time - reduced by 30%. From 730 to 518 ms.
Category page creation time - reduced by 20%. From 870 to 753 ms.

[keulinho]

Wow 🤯

Thanks for diving into this and preparing a fix and such a detailed analysis.

However as you already stated, the root cause of the issue lies in the doctrine/dbal library, so we would need to patch that library.

And in fact we where also aware of this bottleneck and took a look into it, but we came to the conclusion that we won't patch the old dbal library as we will switch to doctrine/dbal v3 in the next major release (soon ™️) and there the whole SQL parser part is completely rewritten and according to our preliminary performance tests the issue is solved in that version as well.

However this might be a good solution for projects that are stuck on an older shopware version for now and are affected by this bottleneck.