StaticDatabaseMapper slows down typolinks a lot

[DerFrenk]

Bug Report

Prerequisites

• Can you reproduce the problem on TYPO3 v9.5 LTS (not tested)
• Can you reproduce the problem on TYPO3 v10.4 LTS
• Can you reproduce the problem on TYPO3 v11.x (not tested)
• Can you reproduce the problem on TYPO3 v12.4 LTS
• Did you perform a cursory search to see if your bug or feature is already reported?

Description

While debugging performance issues in a client's website, I found a query that was executed 78 times with exactly the same parameter:

SELECT `crdate_year` FROM `pages` WHERE (`doktype` = :dcValue1) AND ((`pages`.`deleted` = 0) AND (`pages`.`hidden` = 0) AND (`pages`.`starttime` <= 1694694720) AND ((`pages`.`endtime` = 0) OR (`pages`.`endtime` > 1694694720))) GROUP BY `crdate_year`

:dcValue1 is 137 for every call.

This query is executed in StaticDatabaseMapper::buildValues(). Every single time T3's pagerouter generates a link (e.g. via <f:link.typolink), a new StaticDatabaseMapper is constructed and buildValues() gets called, which results in a query being executed.
In the website I'm debugging, those buildValues() calls take more than 600 ms together.

Steps to Reproduce

1. put some <f:link.typolinks in a (random) template, make sure the template is loaded in your page
2. place a debug line like \TYPO3\CMS\Core\Utility\DebugUtility::debug('hello', __CLASS__ . ':' . __LINE__); in \T3G\AgencyPack\Blog\Routing\Aspect\StaticDatabaseMapper::buildValues

Expected behavior:
I don't exactly know how the routing and aspects works in T3. Maybe some configuration must be changed so the StaticDatabaseMapper is reused for all instances with the same $settings?
If that's not possible and T3 creates a new instance for each typolink, maybe the result should be cached in a transient memory cache or redis (since the output probably will change only once a year!)

Actual behavior:
We see tons of hello debugs, instead of 0 or 1.

Versions

I've found this issue in CMS10 (blog: v10.0.1), a colleague reproduced it in CMS12 (blog: v12.0.2).

[amenk]

I just debugged a performance problem in our client's website with 81 locales where this causes around 6 seconds delay after installing the blog extension...

It currently just does not seem to work as intended. In our case, the resulting query seems to be even wrong (`doktype AND 137 instead of doktype = 137 ?!)

SELECT `crdate_year` FROM `pages` WHERE (doktype) AND (:dcValue1)
AND ((`pages`.`deleted` = 0) AND (`pages`.`hidden` = 0) AND
(`pages`.`starttime` <= 1730811540) AND ((`pages`.`endtime` = 0) OR (`pages`.`endtime` > 1730811540))) GROUP BY `crdate_year`

EDIT: This wrong query was actually fixed in 4ba5e6f

In our case it always returns 0, so as a workaround we are considering to just hard-code a return of [0 => '0'] at least to speed up things a lot.

Still we try to understand what this query is for.

[amenk]

Actually, if I copy the fix 4ba5e6f to our blog 11.0.2 @ TYPO3 10 installation, I don't have such severe performance problems.

@DerFrenk are you still able to reproduce this for example in TYPO3 10 with the above fix applied? Or did you find another workaround?

EDIT: During debugging I had some indexes applied:

CREATE INDEX idx_pages_filter ON pages (deleted, hidden, starttime, endtime);
CREATE INDEX idx_pages_crdate_year ON pages (crdate_year);
CREATE INDEX idx_pages_doktype ON pages (doktype);
CREATE INDEX idx_pages_crdate_year_filter ON pages (crdate_year, deleted, hidden, starttime, endtime);

Those seem to help to mitigate the problem a bit and in our cases, where we don't have any blog entries in the database, yet.
Without those indexes, it is still slow.