Review main Merged

81166[BUGFIX] Fix performance issue when resolving Extbase relations in workspaces

Rémy DANIEL CR +2 V +2 Easy +28 −10 updated 01/02/25, 20:43

Commit message

In the workspace preview, when Extbase resolves a MM relation
(e.g. categories from a news record), the database query introduced by
commit a1f22a427f3bc49ab3fc3424859a7e2d7beaa1c9 forgets to constrain the
MM on the foreign side of the relation.

This can result in a lot of rows being fetched from the database, which
can be a huge performance issue depending on the number of relations in
the MM table.

This change enforces the foreign clause on the foreign side of the
relation.

Review

  • Code review CR +2 Done
    • Benni Mack +2
    • Stefan Bürk +1
  • Verified V +2 Done
    • Benni Mack +2
    • Stefan Bürk +1
    • core-ci +1 CI

Issues 6

Files 1

Review log 3547

Show 1622 earlier messages
  1. Rémy DANIEL uploaded patch set 1 09/21/23, 20:49

  2. core-ci voted on patch set 1 V -1 09/21/23, 20:59 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47038

  3. Rémy DANIEL uploaded patch set 2 V -1 09/21/23, 21:19

  4. core-ci voted on patch set 2 V +1 09/21/23, 21:26 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47039

  5. Rémy DANIEL rebased onto patch set 3 V +1 06/17/24, 14:08

  6. core-ci voted on patch set 3 V +1 06/17/24, 14:16 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/59734

  7. Friedemann Altrock commented on patch set 3 CR +1 V +1 08/30/24, 14:30

    Just ran into this problem with hundreds of MM objects being hydrated and PHP running out of memory. Can confirm that the patch fixes it.
  8. Benni Mack updated the commit message in patch set 4 CR +1 V +1 10/18/24, 13:47

  9. Rémy DANIEL commented on patch set 4 10/18/24, 14:46

    Hi Benni Why not backport this patch also to 12.4, where the issue still exists? Our 11.5 ELTS instances would also love to have it.
  10. Friedemann Altrock voted on patch set 4 CR +1 V +1 11/10/24, 13:26

  11. Benni Mack commented on patch set 4 CR -1 11/15/24, 10:28

    I'm fine adding 12.4 again

    DataMapper.php:547

    needs a if ($columMap->getParentKeyFieldName() !== null) wrapper
  12. Rémy DANIEL uploaded patch set 5 CR +1 V +1 11/15/24, 15:12

  13. Rémy DANIEL commented on patch set 5 11/15/24, 15:13

    DataMapper.php:547 Reply

    Done
  14. core-ci voted on patch set 5 V +1 11/15/24, 15:19 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/69488

  15. Benni Mack commented on patch set 5 11/15/24, 16:25

    DataMapper.php:529

    this looks like it is moved out of resolveRelationValuesOfField() into this calling method. Is there any benefit on why to do this?
  16. Rémy DANIEL commented on patch set 5 11/15/24, 17:12

    DataMapper.php:529 Reply

    Yes. $parentId is now needed in 2 places: inside resolveRelationValuesOfField where it was calculated before, and in the new sql constraint. In order not to introduce duplicated code, I extracted $parentId calculation outside of resolveRelationValuesOfField.
  17. Benni Mack uploaded patch set 6 V +1 11/15/24, 22:19

  18. Benni Mack commented on patch set 6 CR +1 11/15/24, 22:21

    I hope my changes are OK, I find this change now much more readable. However, your remarks in this patch were really helpful! Can you also verify if this change is OK like it is, Rémy!
  19. Benni Mack commented on patch set 6 11/15/24, 22:21

    DataMapper.php:529 Reply

    Thanks for the feedback!
  20. core-ci voted on patch set 6 V -1 11/15/24, 22:30 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/69506

  21. Benni Mack commented on patch set 7 CR +1 V -1 11/15/24, 23:04

    Published edit on patch set 6.
  22. core-ci voted on patch set 7 V -1 11/15/24, 23:13 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/69510

  1. Rémy DANIEL commented on patch set 7 11/18/24, 08:41

    Reply

    Thanks, your change is much more readable.
  2. Benni Mack rebased onto patch set 8 V -1 11/22/24, 16:31

  3. core-ci voted on patch set 8 V -1 11/22/24, 16:41 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/70058

  4. Benni Mack rebased onto patch set 9 V -1 12/11/24, 16:33

  5. core-ci voted on patch set 9 V -1 12/11/24, 16:48 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/70955

  6. Benni Mack uploaded patch set 10 V -1 12/11/24, 17:01

  7. Benni Mack voted on patch set 10 CR +1 V +1 12/11/24, 17:01

  8. core-ci voted on patch set 10 V +1 12/11/24, 17:09 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/70960

  9. Benni Mack rebased onto patch set 11 CR +1 V +1 12/11/24, 18:41

  10. core-ci voted on patch set 11 V +1 12/11/24, 18:49 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/70975

  11. Benni Mack voted on patch set 11 CR +1 V +1 12/13/24, 14:10

  12. Rémy DANIEL voted on patch set 11 CR +1 V +1 12/23/24, 09:35

  13. Stefan Bürk voted on patch set 11 CR -1 12/23/24, 10:49

  14. Stefan Bürk commented on patch set 11 12/23/24, 11:10

    DataMapper.php:542

    We are talking about a join here and this statement only adds an equal check against a value for one value: ``` parentKeyFieldName = $parentId ``` which is not guaranteed to be unique within an join sql statement - and even more open for conflicts with multiple joins. I'd say that we need to use the tablename + fieldname combination to make this concreate and deterministic from a sql point of view. ``` $query->equals( $columnMap->getParentKeyTableName() . '.' . $columnMap->getParentKeyFieldName(), $parentId ) ``` to have the sql as ``` parentTableName.parentFieldname = $parentId ``` Not that deep into the code here, but the table may be aliased and not sure if that is reflected in the ColumnMap table. That should also be investigated and the alias mapping used instead of the real tablename. This is about extbase - I would feel more confident having some kind of functional test for this, but not that deep into extbase to have it directly in mind to provide it directly. This solution seems to be incomplete for me or did I missed something ?
  15. Rémy DANIEL commented on patch set 11 12/23/24, 11:45

    DataMapper.php:542 Reply

    Hi Stefan Here is an example of the generated sql query: ``` SELECT `sys_category_record_mm`.*, `sys_category`.* FROM `sys_category_record_mm` LEFT JOIN `sys_category` `sys_category` ON ((`sys_category_record_mm`.`uid_local` = `sys_category`.`uid`) AND (((`sys_category`.`deleted` = 0) AND (`sys_category`.`hidden` = 0) AND (`sys_category`.`starttime` <= 1734954060) AND (((`sys_category`.`endtime` = 0) OR (`sys_category`.`endtime` > 1734954060)))))) WHERE ((((((`sys_category_record_mm`.`uid_local` IN (?)) AND (`sys_category_record_mm`.`uid_foreign` = ?))) AND (`sys_category_record_mm`.`fieldname` = ?))) AND (`sys_category_record_mm`.`tablenames` = ?) AND (`sys_category`.`deleted` = 0 AND ((`sys_category`.`t3ver_wsid` = 0) OR (`sys_category`.`t3ver_wsid` = 1)))) ORDER BY `sys_category_record_mm`.`sorting_foreign` ASC ``` You can see the table in the LEFT JOIN is aliased and the alias is used in combination with fieldnames in the ON clause.
  16. Stefan Bürk commented on patch set 11 12/30/24, 10:25

    DataMapper.php:542 Reply

    Thanks for the answer. That was not clear fro. the clasa/method desceiption. Going to have a look directly after the year switch and try to comeing up with some tests for this.
  17. Garvin Hicking commented on patch set 11 12/30/24, 12:08

    Can you add a "Related: ..." to the issue of that referenced commit a1f22a427f3bc49ab3fc3424859a7e2d7beaa1c9 ?
  18. Stefan Bürk updated the commit message in patch set 12 CR +1 V +1 01/02/25, 14:11

  19. Stefan Bürk commented on patch set 12 CR +1 V +1 01/02/25, 14:15

    Reply

    Done

    DataMapper.php:542 Reply

    Not easily doable - needs some pre-work/structure. Added to todo list to add these kind of tests for extbase repository/ORM handling in general.
  20. core-ci voted on patch set 12 V +1 01/02/25, 14:17 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/71829

  21. Stefan Bürk updated the commit message in patch set 13 CR +1 V +1 01/02/25, 15:01

  22. Stefan Bürk voted on patch set 13 CR +1 V +1 01/02/25, 15:01

  23. core-ci voted on patch set 13 V +1 01/02/25, 15:06 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/71832

  24. Benni Mack voted on patch set 13 CR +2 V +2 01/02/25, 20:34

  25. Benni Mack merged patch set 14 01/02/25, 20:43