Review main Merged

81421[TASK] Speed up bin/typo3 referencindex:update

Christian Kuhn CR +2 V +2 Normal +65 −11 updated 03/20/24, 06:13

Commit message

By deleting sys_refindex rows pointing to deleted=1
records early, and ignoring soft-deleted ones in
the main loop, we can speed up the call significantly
for instances with many soft-deleted records.

Example data point with my test DB: Before ~24
seconds, with patch ~6 seconds. The more soft-deleted
records exist in a table, the higher the improvement.

Review

  • Code review CR +2 Done
    • Christian Kuhn +2
    • Stefan Bürk +1
  • Verified V +2 Done
    • Christian Kuhn +2
    • Jasmina Ließmann +1
    • Oliver Klee +1
    • Stefan Bürk +1
    • core-ci +1 CI

Issues 1

Files 2

Review log 2126

Show 1 earlier messages
  1. Christian Kuhn uploaded patch set 1 10/12/23, 14:27

  1. core-ci voted on patch set 1 V -1 10/12/23, 14:38 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47723

  2. Christian Kuhn uploaded patch set 2 V -1 10/14/23, 12:26

  3. core-ci voted on patch set 2 V -1 10/14/23, 12:35 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47756

  4. Christian Kuhn uploaded patch set 3 V -1 10/14/23, 12:35

  5. Christian Kuhn commented on patch set 3 10/14/23, 12:37

    @stefan, could you have a closer look at the query to verify i did not mess this up? the outer one is either a count() or a delete() with an 'recuid IN( $subquery ), where the subquery selects uids that are a) deleted in target table and b) actually have rows in sys_refindex. b) reduces the data set heavily so we don't IN() all deleted=1 records blindly.
  6. Christian Kuhn updated the commit message in patch set 4 10/14/23, 12:41

  7. core-ci voted on patch set 3 V +1 10/14/23, 12:42 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47758

  8. core-ci voted on patch set 4 V +1 10/14/23, 12:48 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47759

  9. Benni Mack voted on patch set 4 CR +1 10/14/23, 18:33

  10. Stefan Bürk commented on patch set 4 CR -1 10/14/23, 18:51

    Basically - okay. Will ping lolly to discuss some thoughts / edgce cases. Therefore, temp -1.
  11. Christian Kuhn uploaded patch set 5 CR +1 V +1 10/15/23, 11:15

  12. Stefan Bürk commented on patch set 5 V +1 CR +1 10/15/23, 11:17

    Reply

    solved.
  13. core-ci voted on patch set 5 V +1 10/15/23, 11:22 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47769

  14. Oliver Klee voted on patch set 5 V +1 10/15/23, 11:40

  15. Jasmina Ließmann voted on patch set 5 V +1 10/15/23, 14:03

  16. Christian Kuhn voted on patch set 5 V +2 CR +2 10/15/23, 14:03

  17. Christian Kuhn merged patch set 6 10/15/23, 14:03

  18. Markus Klein commented on patch set 6 10/15/23, 17:38

    ReferenceIndex.php:997

    I think (by reading only) this should use the $subQueryBuilder
  19. Stefan Bürk commented on patch set 6 10/15/23, 17:42

    ReferenceIndex.php:997 Reply

    Nope - $queryBuilder is correct - if it would be the correct instance. When dealing with subqueries and creating named parameters, the most outer (main) queryBuilder instance needs to be used. The $queryBuilder needs to be the instance from L1013 - so should be moved to the else branch after the L1013 $queryBuilder instance creation.
  20. Garvin Hicking commented on patch set 6 10/15/23, 18:32

    ReferenceIndex.php:997 Reply

    The reason this luckily works in practice, is because in both queries `:dcvalue1` equals the value of `$tableName`. I agree though, this is a possible setup for future errors, so either somewhere before $subQueryBuilder->getSql there should be a mention ("Danger, Will Robinson: Be aware of re-use of placeholder parameters, these are shared across both queryBuilder instances"). Or the other way (which I would prefer) would be to already utilise a named placeholder, as Robert indicated: ``` $queryBuilder->expr()->eq('sub_refindex.tablename', $queryBuilder->createNamedParameter($tableName, Connection::Param_STR, ':subQueryTableName')) ```
  21. Stefan Bürk commented on patch set 6 10/15/23, 18:44

    ReferenceIndex.php:997 Reply

    To define placholder, naming automaticly or manually, the corret main(outer) instance needs to be used. Let the querybuilder name it or name it manually does not matter at all. Just the correct main instance needs to be used. Even naming it manually like in your example Garvin. Your explanation is correct, why "the" correct value is used and it "should" not matter and why it slipped through the verification. We just need to move the subQuery stuff after the correct outer QueryBuilder instance is created and can be used. Than the placeholder for the subquery would be `:dcValue3` and not `:dcValue1` and therefore unique again + the value is added in the correct instance. Manual naming for the placeholder is not needed here. I would not favour manual placeholder namings at all, let the automatic naming do their job.
  22. Garvin Hicking commented on patch set 6 10/15/23, 19:15

    ReferenceIndex.php:997 Reply

    Nice one, thank you Stefan. That helped me understand how this was meant to work. I prepared a patch which I hope is what you're suggesting: https://gist.github.com/garvinhicking/4892f6a3f50101b77a66174757d41ab5 That works at least for me, and now uses :dcValue1 and :dcValue2 (with the same contents though). Also I attached a note so that future people reading this don't believe this is a wrong re-use of $queryBuilder within the $subQueryBuilder. Maybe it helps, I suppose a new gerrit patch/review would follow?! Happy to help doing some legwork, let me know.
  23. Christian Kuhn commented on patch set 6 10/15/23, 21:55

    ReferenceIndex.php:997 Reply

    Nice! Thank you guys for stepping in, I didn't realize I was just 'lucky' the setup worked. We're not doing *that* many sub queries in the core (yet), it's good to learn now and eventually document some pitfalls as well. Note there is a second subquery setup in the method we may want to check as well while we are at it. Garvin, would you like to push your change to get this right?
  24. Stefan Bürk commented on patch set 6 10/16/23, 05:14

    ReferenceIndex.php:997 Reply

    on it, we need to adress more.
  25. Stefan Bürk commented on patch set 6 10/16/23, 06:01

    ReferenceIndex.php:997 Reply