TYPO3 Core

Hey Andy,

where exactly are the multiple DB queries located?

AFAIK getPagesFromDatabaseForCandidates() does only one query per language. If workspaces are enabled, we have more, of course.

We have the performance problem currently in an installation with 1600 domains too. In our case we fixed it by extending the query in the getPagesFromDatabaseForCandidates, so that only the pages of the current rootline are returned. But I'm not sure if there could be problems with mountpoints?

Changes in PageRouter.php line 334 and below

  /**
     * Get all uids below given page uid
     *
     * @param $pageUID
     * @return string
     */

    private function getRecursivePageUIDs($pageUID)
    {
        $depth = 999999;
        $queryGenerator = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Database\\QueryGenerator');
        $rGetTreeList = $queryGenerator->getTreeList($pageUID, $depth, 0, 1);
        //Will be a string
        return explode(',', $rGetTreeList);

    }

    /**
     * Check for records in the database which matches one of the slug candidates.
     *
     * @param array $slugCandidates
     * @param int $languageId
     * @return array
     */
    protected function getPagesFromDatabaseForCandidates(array $slugCandidates, int $languageId): array
    {
        $context = GeneralUtility::makeInstance(Context::class);
        $searchLiveRecordsOnly = $context->getPropertyFromAspect('workspace', 'isLive');
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
            ->getQueryBuilderForTable('pages');
        $queryBuilder
            ->getRestrictions()
            ->removeAll()
            ->add(GeneralUtility::makeInstance(DeletedRestriction::class))
            ->add(GeneralUtility::makeInstance(FrontendWorkspaceRestriction::class, null, null, $searchLiveRecordsOnly));
        //restrict query to sites in rootline
        $uids = $this->getRecursivePageUIDs($this->site->getRootPageId());
        $statement = $queryBuilder
            ->select('uid', 'l10n_parent', 'pid', 'slug')
            ->from('pages')
            ->where(
                $queryBuilder->expr()->in('uid',$uids),
                $queryBuilder->expr()->eq(
                    'sys_language_uid',
                    $queryBuilder->createNamedParameter($languageId, \PDO::PARAM_INT)
                ),
                $queryBuilder->expr()->in(
                    'slug',
                    $queryBuilder->createNamedParameter(
                        $slugCandidates,
                        Connection::PARAM_STR_ARRAY
                    )
                )
            )
            // Exact match will be first, that's important
            ->orderBy('slug', 'desc')
            ->execute();

Please use this patch source: https://raw.githubusercontent.com/protos1575/PageRouter.patch/master/PageRouter.patch

Same problem here, we have a large website with 100+ domains and some mountpoints.
The method getPagesFromDatabaseForCandidates() iterate a lot of non necessary pages (/home/about-us/offices/,/home/about-us/,/home/,/ for each site) and call a lot of methods for each.
For examples, in my case, i will iterate overs 500+ pages to find the slug that appear in the first row of the query. This result in a big performance issue.

So, even if we find a page wich are on the same site, we iterate all the others pages.
If we have the good site ID and the exact same slug, maybe we could stop iteration here?

After adapting the original patch from @Carlos Meyer as provided in #89058-6 to work with current TYPO3 v10, it turns out this does not work at all to restrict the query on the subpages of a given site when you work with mount points.

Too bad indeed, I liked the idea even though the very first call was then much longer to complete since it had to compute the full page tree of my domain once.

So I'll go back to my caching solution for the time being.