TYPO3 Core

Are you maybe able to check the SQL query that would get executed? Could it be the joins are missing groupBys or so?

Maybe through database-side query logging or slow queries. Then we could check the SQL resultset for abmormal dataset sizes or recursion or contained IDs that could cause extbase infinite recursion through language overlays or so...?

(Given that the SQL query even gets built - or it times out beforehand even, do I understand right?)

Yeah, it times out before it is ready build. It seems like the parsing of constraints doesn't scale well anymore. The more constraints, the longer it takes.

E.g. in the test, reducing the available sys_category entries would lead to a time that you won't notice.

This is the executed SQL on v12:

SELECT count(*)
  FROM `tx_events_domain_model_date` `tx_events_domain_model_date`
  LEFT JOIN `tx_events_domain_model_event` `tx_events_domain_model_event`
    ON `tx_events_domain_model_date`.`event` = `tx_events_domain_model_event`.`uid`
 WHERE (((((((not(`tx_events_domain_model_event`.`uid` IS NULL)) AND (`tx_events_domain_model_date`.`uid` > 0))) AND (((((((((((((((((((((((((((((((((((((((((((((((`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '1') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '2') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '3') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '4') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '5') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '6') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '7') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '8') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '9') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '10') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '11') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '12') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '13') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '14') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '15') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '16') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '17') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '18') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '19') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '20') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '21') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '22') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '23') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '24') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))))) AND (((`tx_events_domain_model_date`.`start` >= '1660158000') OR (`tx_events_domain_model_date`.`end` >= '1660158000')))))
   AND (`tx_events_domain_model_date`.`sys_language_uid` IN (0, -1))
   AND (`tx_events_domain_model_date`.`pid` = 2)
   AND (`tx_events_domain_model_date`.`t3ver_oid` = 0)
   AND (((`tx_events_domain_model_date`.`t3ver_state` <= 0) AND (`tx_events_domain_model_date`.`t3ver_wsid` = 0) AND (((`tx_events_domain_model_date`.`t3ver_oid` = 0) OR (`tx_events_domain_model_date`.`t3ver_state` = 4))) AND (`tx_events_domain_model_date`.`hidden` = 0) AND (`tx_events_domain_model_date`.`starttime` <= 1738223280) AND (((`tx_events_domain_model_date`.`endtime` = 0) OR (`tx_events_domain_model_date`.`endtime` > 1738223280)))))
   AND (((((`tx_events_domain_model_event`.`deleted` = 0) AND (`tx_events_domain_model_event`.`t3ver_state` <= 0) AND (`tx_events_domain_model_event`.`t3ver_wsid` = 0) AND (((`tx_events_domain_model_event`.`t3ver_oid` = 0) OR (`tx_events_domain_model_event`.`t3ver_state` = 4))) AND (`tx_events_domain_model_event`.`hidden` = 0) AND (`tx_events_domain_model_event`.`starttime` <= 1738223280) AND (((`tx_events_domain_model_event`.`endtime` = 0) OR (`tx_events_domain_model_event`.`endtime` > 1738223280))))) OR (`tx_events_domain_model_event`.`uid` IS NULL)))
   AND (((`tx_events_domain_model_date`.`t3ver_wsid` = 0) AND (((`tx_events_domain_model_date`.`t3ver_oid` = 0) OR (`tx_events_domain_model_date`.`t3ver_state` = 4)))))

Ouch, this looks bad. Can you say where exactly this gets issued? I assume on an extbase domain repository relation property mapping? Your tests only executes a subrequest, I guess that one uses a specific extbase findBy/findAll() method?

This might help Stefan to easier setup a reproducible example if we can recreate the extbase circumstances.

Haven't look into it myself, feels a bit like this query should not have X subqueries but rather X left outer joins with a single query to return the UIDs for a "uid in (....)" condition...?

Guess https://github.com/werkraum-media/events/blob/main/Classes/Domain/Repository/EventRepository.php#L147-L151 is at work here?

     $categories = GeneralUtility::intExplode(',', $categories, true);
        foreach ($categories as $category) {
            $constraints[] = $query->contains('categories', $category);
        }

Wonder if you could use $query->in('categories.uid', $categoryUids instead to make it perform more efficiently?

Yes exactly. From my understanding this won't be possible, as one event can have multiple categories assigned, that's why one needs to use contains().

This is a query from v13, when I remove some sys_category form the database:

SELECT count(*)
  FROM `tx_events_domain_model_date`
  LEFT JOIN `tx_events_domain_model_event` `tx_events_domain_model_event`
    ON `tx_events_domain_model_date`.`event` = `tx_events_domain_model_event`.`uid`
 WHERE ((((((not(`tx_events_domain_model_event`.`uid` IS NULL)) AND (`tx_events_domain_model_date`.`uid` > 0))) AND (((((((`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '1') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '2') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '3') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))) OR (`tx_events_domain_model_event`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = '4') AND (((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event') AND (`sys_category_record_mm`.`fieldname` = 'categories'))))))))) AND (((`tx_events_domain_model_date`.`start` >= '1660158000') OR (`tx_events_domain_model_date`.`end` >= '1660158000'))) AND (`tx_events_domain_model_date`.`sys_language_uid` IN (0, -1)) AND (`tx_events_domain_model_date`.`pid` = 2) AND (`tx_events_domain_model_date`.`t3ver_oid` = 0) AND (((`tx_events_domain_model_date`.`t3ver_state` <= 0) AND (`tx_events_domain_model_date`.`t3ver_wsid` = 0)) AND ((`tx_events_domain_model_date`.`t3ver_oid` = 0) OR (`tx_events_domain_model_date`.`t3ver_state` = 4)) AND `tx_events_domain_model_date`.`hidden` = 0 AND `tx_events_domain_model_date`.`starttime` <= 1738226160 AND ((`tx_events_domain_model_date`.`endtime` = 0) OR (`tx_events_domain_model_date`.`endtime` > 1738226160))) AND (((`tx_events_domain_model_event`.`deleted` = 0 AND ((`tx_events_domain_model_event`.`t3ver_state` <= 0) AND (`tx_events_domain_model_event`.`t3ver_wsid` = 0)) AND ((`tx_events_domain_model_event`.`t3ver_oid` = 0) OR (`tx_events_domain_model_event`.`t3ver_state` = 4)) AND `tx_events_domain_model_event`.`hidden` = 0 AND `tx_events_domain_model_event`.`starttime` <= 1738226160 AND ((`tx_events_domain_model_event`.`endtime` = 0) OR (`tx_events_domain_model_event`.`endtime` > 1738226160))) OR (`tx_events_domain_model_event`.`uid` IS NULL))) AND (((`tx_events_domain_model_date`.`t3ver_wsid` = 0) AND (((`tx_events_domain_model_date`.`t3ver_oid` = 0) OR (`tx_events_domain_model_date`.`t3ver_state` = 4))))))

The v12 query works in under 1 second. So the query in general doesn't seem to be an issue in v12.
But I'm also happy if there is a better approach.

The ->in() condition when combined with a grouping of the model's uid should be able to "fold" multiple join results, but you're probably right this would be a workaround for the query creation process, and the difference from v12 to v13 in query generstion needs to be found.

I'll be silent now and let Stefan have a look first, but maybe this small sidequest rings a bell for him ;-)

(Stefan: I've added a POC https://review.typo3.org/c/Packages/TYPO3.CMS/+/87945 to show what I mean with a "better" query for finding categories which would not use recursion. However I think this is just a sidequest/optimization; the root cause for this where Doctrine DBAL probably creates large resource exhaustion due to resursive object generation is still left. But I think, a `contains` here is not a good query to execute)

Thanks, that might solve the issue when you only care about "or", e.g. if any of the given categories is assigned to events. But that won't work with an "and" if an event should be assigned to all of the categories, right?

We now have your workaround for OR in our project, this works fine, as the project itself only uses the OR feature of the extension. Thanks for helping out.