Bug #82730

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

Added by Stefan Franke 9 months ago. Updated 4 months ago.

Status:
New
Priority:
Must have
Assignee:
-
Category:
FormEngine aka TCEforms
Target version:
-
Start date:
2017-10-11
Due date:
% Done:

0%

TYPO3 Version:
8
PHP Version:
7.0
Tags:
Complexity:
Is Regression:
Sprint Focus:

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',
    ],
],


Related issues

Related to TYPO3 Core - Bug #81036: TCA l18n_parent is processed for sys_language_uid 0 New 2017-04-27
Related to TYPO3 Core - Bug #84247: TCA label_userfunc called for every record in current page/folder New 2018-03-13

History

#1 Updated by Susanne Moog 9 months ago

  • Category set to FormEngine aka TCEforms

#2 Updated by SiteGef├╝hl no-lastname-given 9 months ago

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

#3 Updated by Stefan Franke 8 months ago

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.

#4 Updated by Andre Koller 8 months ago

  • Related to Bug #81036: TCA l18n_parent is processed for sys_language_uid 0 added

#5 Updated by Andre Koller 8 months ago

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?

#6 Updated by Jakob Berlin 7 months ago

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.

#7 Updated by Stefan P 6 months ago

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.

#8 Updated by Webadmin no-lastname-given 4 months ago

  • Related to Bug #84247: TCA label_userfunc called for every record in current page/folder added

#9 Updated by Stefan P 4 months ago

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.

#10 Updated by Michael Ecker 4 months ago

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.

Also available in: Atom PDF