Issue Bug Under Review Orphaned

#82730Opening record in backend painfully slow with foreign table and large main table

Stefan Franke 10/11/17, 11:30 updated 07/12/24, 17:43

Description

Opening a record in the backend can become painfully slow under the following circumstances:

The main table (10.000 records) has got a 1:n relation to a foreign table (3 records). When trying to open the record of the main table in the TYPO3 backend, the following SQL statement is executed that often that it takes an exceptionally long amount of time to actually open the record.

SELECT * FROM `tx_myext_domain_model_foreigntable` WHERE (`uid` = ?) AND (`tx_myext_domain_model_foreigntable`.`deleted` = 0)

TCA of main table:

'attribute' => [
    'exclude' => true,
    'label' => 'attribute',
    'config' => [
        'type' => 'select',
        'renderType' => 'selectSingle',
        'foreign_table' => 'tx_myext_domain_model_foreigntable',
    ],
],

Reviews 1

History 1926

Show 1 earlier messages
  1. SiteGefühl no-lastname-given commented <span data-processor="relativetime" data-value="2017-11-08T17:13:23+00:00" > 11/08/17, 17:13 </span>

    I can confirm the Problem in TYPO3 7 and PHP 7.1
    We have something about 80.000 SQL queries after we clicked the edit button of the main record. It takes 3 minutes to open the record.
    One identical query is executed something about 5000 times.

    // Edit: Maybe this is going to fix the Problem? https://github.com/TYPO3/TYPO3.CMS/commit/f3e7a034c129aafc8b081e0125c50330207fae82#diff-ddfa1112aff05d23dcd52f1e19e04ba9
    // If yes, I would be happy about a fix for Version 7

  2. Stefan Franke commented <span data-processor="relativetime" data-value="2017-11-14T11:00:26+00:00" > 11/14/17, 11:00 </span>

    Unfortunately, commit f3e7a034c129aafc8b081e0125c50330207fae82 doesn’t fix the issue for me.

    It seems that it has got something to do with the localization. In my case I don’t need the localization feature, so I got rid of it and that “solved” the problem.

  3. Andre Koller commented <span data-processor="relativetime" data-value="2017-11-15T11:38:11+00:00" > 11/15/17, 11:38 </span>

    Typo3 v8.7.8 PHP 7.1

    When “label_alt” and “label_alt_force” ist used in the ctrl-Section in TCA of the foreign model, 10000 sql queries executed.
    Without label and label_alt_force 500 sql queries executed.

    Can anyone confirm this?

  4. Jakob Berlin commented <span data-processor="relativetime" data-value="2017-12-21T01:03:31+00:00" > 12/21/17, 01:03 </span>

    Not really confirming it but after disabling label_alt my edit view get back to work in a few seconds wich just CPU usage and not minutes with have DB usage. So it might be the cause of the problem.

    Btw: It even happened when label is set to a foreign attribute of the foreign attribute itself. So, to have now troubles label should be a static value from the foreign table by now.

  5. S P commented <span data-processor="relativetime" data-value="2018-01-23T11:46:16+00:00" > 01/23/18, 11:46 </span>

    We are having this issue as well (with both, TYPO3 7 and 8). Our data structure is like this: products are the main table, products contain attributes via 1:n (irre, inline, so the product uid with index is inside the attributes table), And the attributes contain n:1 an attributelabel (attribute contains a field attributelabel holding a uid):

    product ---1:n--- (field w/ index product) attribute (field label) --- n:1 --- attributelabel

    The ctrl => label in attributes is set to the field attributelabel – and this constellation triggers this behaviour. So, loading the inline label (or alt label) from a third table is responsible for this.

  6. S P commented <span data-processor="relativetime" data-value="2018-03-23T07:55:17+00:00" > 03/23/18, 07:55 </span>

    What is the state of this bug? We have two customers where this is a major problem, because they are not able to effectivley edit their datasets due to this behaviour.

  7. Michael Ecker commented <span data-processor="relativetime" data-value="2018-03-28T23:45:33+00:00" > 03/28/18, 23:45 </span>

    Andre Koller wrote:
    > Typo3 v8.7.8 PHP 7.1
    >
    > When “label_alt” and “label_alt_force” ist used in the ctrl-Section in TCA of the foreign model, 10000 sql queries executed.
    > Without label and label_alt_force 500 sql queries executed.
    >
    > Can anyone confirm this?

    I can definitely confirm that disabling ‘label_alt_force’ reduces the execution time by magnitudes, especially with an object containing more than about 10 inline children. No other configuration variation had a similar effect.

    This bug does gravely hamper the use of inline elements and should be resolved immediately. In my experience it was a smaller issue even with versions before 7, but with the migration to Doctrine DBAL in 8 the execution time multiplied again. I got regularly ‘max execution time exceeded’ errors even with significantly raised max_execution_time values.

  8. Oliver Beck commented <span data-processor="relativetime" data-value="2018-08-09T09:14:52+00:00" > 08/09/18, 09:14 </span>

    I have to push this issue. We have several typo3 instances, which have inline fields (irre) in tca. As soon as the fields contains more than 10 or 20 child-records, it takes about 30 seconds until the whole record can be edited.

  9. Bernhard Berger commented <span data-processor="relativetime" data-value="2018-12-07T16:38:37+00:00" > 12/07/18, 16:38 </span>

    I can also co firm this issue. In our case we actually need localization as we have child records attached to parent records and unfortubately we really need that relation.

    We can’t even change the design to use the list module as it would require re-schooling about 300 editors..

  10. Stephan Bauer commented <span data-processor="relativetime" data-value="2018-12-19T10:25:29+00:00" > 12/19/18, 10:25 </span>

    Same problem here with 8.7.

    'label' => 'title',
    'label_alt' => 'labels',
    'label_alt_force' => 1,

    ‘labels’ is a selectMultipleSideBySide

    As described in the related bug #84247 changing

    'foreign_table_where' => 'AND xxx.pid=###CURRENT_PID### AND ...

    to

    'foreign_table_where' => 'AND xxx.pid=-1 AND 

    it is fast again.

  11. Jan Kornblum commented <span data-processor="relativetime" data-value="2019-01-14T00:36:24+00:00" > 01/14/19, 00:36 </span>

    Same problem still in 9.5.

    Removing ‘label_alt’ (pointing to child or parent records) and ‘label_alt_force’ brought great performance back! In addition, i tried to remove the ‘label_alt’ for the IRRE appearance only using overrideChildTca() (https://forge.typo3.org/issues/87426) to perserve them in normal list-view, but that is not possible (missing feature).

    The other way to get even better performance: Removed localization feature at all from my extension, which made the usage of ‘label_alt’ and ‘label_alt_force’ possible again without performance loss.

  12. M. Ecker commented <span data-processor="relativetime" data-value="2019-10-01T13:45:42+00:00" > 10/01/19, 13:45 </span>

    Hint:
    To build an options list based on a foreign_table TYPO3 will first get all records according to foreign_table_where from this table and then in a second step build the labels for the options. If label_alt points e.g. to a parent table or localizations are needed there will at least one additional db request for every option. That’s why removing label_alt can boost performance.
    If you use IRRE elements where you defined a parent selector within TCA you should use overrideChildTca within the parent element to set this field to passthrough when displayed as child element.

    These performance problems could only be avoided using a (complex) JOIN statement to get the already localized labels within the first statement – but that wouldn’t fit all cases, I think.

  13. Markus Klein commented and changed status New Closed priority Must have Won't have this time <span data-processor="relativetime" data-value="2020-03-12T15:24:39+00:00" > 03/12/20, 15:24 </span>

    M. Ecker already outlined the behaviour and his conclusion.

    This is not fixable on a general scale as far as we can tell.

    So performance optimization is possible only by: * implementing a custom label function * changing the relation type/rendering type (e.g.g group instead of select) * adjusting the TCA in an inline relation (overrideChildTCA)

  14. Gerrit Code Review pushed patch set 2 for main to change 81871 status Closed Under Review <span data-processor="relativetime" data-value="2023-11-22T19:30:09+00:00" > 11/22/23, 19:30 </span>