Review main Merged

77392[TASK] Reduce SQL queries in PageRepository::getPageOverlaysForLanguage()

Christoph Lehmann CR +2 V +2 Normal +33 −26 updated 01/17/23, 13:08

Commit message

This introduces less but bigger queries. Queries are also chunked
to avoid hitting platform/driver placeholder limit, if `doctrine/dbal` transform eventually unsupported named query params to positional ones.

Review

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

Issues 1

Files 2

Review log 2127

Show 12 earlier messages
  1. Christoph Lehmann uploaded patch set 1 01/16/23, 14:55

  2. core-ci voted on patch set 1 V +1 01/16/23, 15:03 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/35856

  1. Oliver Klee commented on patch set 1 CR -1 01/16/23, 16:04

    The issue ID is still missing.
  2. Christoph Lehmann uploaded patch set 2 CR -1 V +1 01/16/23, 16:05

  3. Oliver Klee wrote 01/16/23, 16:10

    Removed reviewer Oliver Klee.
  4. Oliver Klee commented on patch set 2 01/16/23, 16:10

    Reply

    Done
  5. Oliver Klee wrote 01/16/23, 16:10

    Removed cc Oliver Klee.
  6. core-ci voted on patch set 2 V +1 01/16/23, 16:13 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/35861

  7. Christoph Lehmann commented on patch set 2 01/16/23, 16:17

    I have the languages EN (default), DE_default, and DE (fallback to DE_default, then EN). My menu has 50 subpages. When i request the page in DE then i have 394 queries WITH and 293 queries WITHOUT the commit.
  8. Oliver Klee updated the commit message in patch set 3 V +1 01/16/23, 16:42

  9. core-ci voted on patch set 3 V +1 01/16/23, 16:54 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/35863

  10. Benni Mack uploaded patch set 4 V +1 01/16/23, 18:24

  11. core-ci voted on patch set 4 V +1 01/16/23, 18:34 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/35871

  12. Benni Mack voted on patch set 4 V +1 CR +1 01/16/23, 19:20

  13. Stefan Bürk commented on patch set 4 01/16/23, 20:25

    PageRepository.php:568

    are we sure not to hit placeholder limit here eventually ? Maybe pageUids should be at least "guarded" with array_chunk technique, as a comprise between the two edges. (but the safe one)
  14. Christoph Lehmann uploaded patch set 5 CR +1 V +1 01/17/23, 11:10

  15. Christoph Lehmann commented on patch set 5 01/17/23, 11:14

    PageRepository.php:568 Reply

    Good catch. It now uses array_chunk like in https://github.com/TYPO3/typo3/blob/b744fd3f07efd316631d95e627cedc82f7b65712/typo3/sysext/linkvalidator/Classes/LinkAnalyzer.php#L147-L173 I kept createNamedParameter() for proper query grouping in EXT:adminpanel.
  16. Christoph Lehmann commented on patch set 5 01/17/23, 11:25

    PageRepository.php:568 Reply

    Done
  17. core-ci voted on patch set 5 V +1 01/17/23, 11:25 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/35936

  18. Benni Mack voted on patch set 5 V +1 CR +1 01/17/23, 12:44

  19. Stefan Bürk commented on patch set 5 V +1 CR +1 01/17/23, 12:56

    PageRepository.php:568 Reply

    Not sure if the query grouping works in all cases. Internally, the named parameter may be eventually replaced with single positional placeholder by doctrine/dbal if not supported by driver (if I remember right for example `mysqli`) ... which means that it will not use a `:dcValue1` or something like that, but creating a query with placeholder for each array element. That means, if the array has 5 elements there will be something like that: ``` IN (?, ?, ?, ?, ?) ``` and if the next call would be with an arry of only 3 elements it would be: ``` IN (?, ?, ?) ``` not checked, by I would guess that grouping would not work really here anyway. But maybe the grouping is done by the sql BEFORE doctrine replaces the named parameter with the positional placeholder for `PARAM_INT_ARRAY or PARAM_STR_ARRAY`. But I'm fine with keeping the placeholder.
  20. Stefan Bürk voted on patch set 5 V 0 CR 0 01/17/23, 12:56

  21. Stefan Bürk updated the commit message in patch set 6 CR +1 V +1 01/17/23, 12:59

  22. Stefan Bürk voted on patch set 6 V +1 CR +1 01/17/23, 12:59

  23. Benni Mack voted on patch set 6 V +2 CR +2 01/17/23, 13:02

  24. Benni Mack merged patch set 7 01/17/23, 13:02

  25. core-ci voted on patch set 6 V +1 01/17/23, 13:08 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/35959