Bug #82100: Backend Form Dataprovider load every foreign Entity, not only the related one - TYPO3 Core - TYPO3 Forge

Actions

Copy link

Bug #82100

open

Backend Form Dataprovider load every foreign Entity, not only the related one

Added by Benjamin Rannow over 6 years ago. Updated 5 months ago.

Status:

Under Review

Priority:

Should have

Assignee:

Category:

Performance

Target version:

Start date:

2017-08-14

Due date:

% Done:

100%

Estimated time:

TYPO3 Version:

PHP Version:

7.1

Tags:

Complexity:

hard

Is Regression:

Sprint Focus:

Description

Hi,

I notice a massive performance drop if I add in TCA a label_userFunc.
I found the reason in the TYPO3\CMS\Backend\Form\FormDataProvider\AbstractItemProvider::processForeignTableClause

For my Setup

I have 2 Entities Post and Comment, these are Bidirectional Related with ManyToOne.
Post has a inline TCA which load all related Comments.
Comment has a label_userFunc in TCA which simply format a Datetime into the title.

After I Try to Debug the label_userFunc, i notice the method are called WAY TO OFTEN.
Then I look at the Queries. I saw that simply the sql Relation are missing in the Query.

That means every time I want to edit a Post, Typo3 load EVERY Comment from a certain PageId.
Example:
- Post_A has 3 Comments.
- All Posts together conatins over 9000 Comments.

If i want to edit Post_A, I expect that Typo3 Load only 3 Comments, but Actual load Every comment on the System and used on every Comment the label_userFunc.
In the End, i see only 3 comments (which is correct).

I worte myself a little workarund which redurce the system and Database load dramatically:
https://gist.github.com/anonymous/7e307e73e6842fd6d94f28ba62ab2a7d#file-abstractitemprovider-php-L135-L146

Post TCA:

    'comments' => [
            'exclude' => false,
            'l10n_mode' => 'exclude',
            'behaviour' => [
                'allowLanguageSynchronization' => true
            ],
            'label' => $ll . 'backend.tca.post.comments',
            'config' => [
                'type' => 'inline',
                'foreign_table' => 'tx_exampleBlog_domain_model_comments',
                'foreign_field' => 'post',
                'maxitems' => 9999,
                'appearance' => [
                    'collapseAll' => true,
                    'levelLinksPosition' => 'top',
                    'showSynchronizationLink' => false,
                    'showAllLocalizationLink' => false,
                    'showPossibleLocalizationRecords' => true,
                    'showRemovedLocalizationRecords' => true,
                    'enabledControls' => [
                        'info' => true,
                        'dragdrop' => true,
                        'localize' => true,
                    ],
                ],
            ],
        ],

Comment TCA:

    'post' => [
            'exclude' => true,
            'label' => $ll . 'backend.tca.post',
            'config' => [
                'type' => 'select',
                'renderType' => 'selectSingle',
                'foreign_table' => 'tx_exampleBlog_domain_model_post',
                'minitems' => 0,
                'maxitems' => 1,
            ],
        ],

Related issues 3 (2 open — 1 closed)

Actions

Copy link

Updated by Benjamin Rannow over 6 years ago

Description updated (diff)

Update Description

Actions

Copy link

Updated by Benjamin Rannow over 6 years ago

Description updated (diff)

Actions

Copy link

Updated by Benjamin Rannow over 6 years ago

Description updated (diff)

Add TCA Example

Actions

Copy link

Updated by Michael Stopp over 6 years ago

I've experienced this performance problem in a similar configuration. In my case, a parent record contained inline child records (configured with collapseAll = true). Each child record contained a field (type' => 'select', 'renderType' => 'selectSingle'), which had 2600 records from a third table with a label_userFunc. When trying to open a parent record with 20 child records, the BE would run into an AJAX timeout eventually, as loading of the 20 child records would take too long. Temporarily disabling the label_userFunc on the third table did bring a significant improvement in performance.
I eventually worked around this problem by changing the TCA for the problematic field in the child record from type 'select' to 'group'. This immediately solved all performance problems.

I'd like to add two observations that I found in my case:

While displaying 20 child records in inline context did take minutes and would eventually run into a timeout, displaying 100 of the same records in the list module was no problem (with the label_userFunc in place).
I initially thought that my label_userFunc might be the performance bottleneck; I added some debug output and noticed that the function was called twice for every record with no apparent reason; that obviously adds an extra burden on the performance.

Actions

Copy link

Updated by Benjamin Rannow over 6 years ago

Complexity changed from medium to hard

I have good and bad news.

The good one,I wrote a Extension that can reproduce and log the Bug.
https://github.com/brannow/frd_inline_relation_bug

Also the behavior that @Michael_Stopp describes, can be also reproduced with the test extension

The bad News:
There are 2 Bugs that go hand in hand.
its a little bit tricky to described this.

Lets say we have a Ext with 2 Entities "City" and "Store"
City and contains N Stores.

1. Bug: if u edit a City, the System load all Stores with the same pid, not only the related one (which is bad)
We have 20 Cities and every City has 20 Stores, so there are 400 Stores in Total.

That means if u edit a City, the System load all 400 Stores. (+ the 20 Stores in the City afterwords, again)
so we have 420 Iteration, not 20.

2. Bug: (which is really nesty) it loads every of the 400 stores multiple times (based on the number of stores in the City)
in Numbers: ([all 400 Stores] * [20 Stores in City]) + [20 Stores in City] = 8020 Iterations ...
with every new record it goes worse and worse.

Actions

Copy link

Updated by Tim Lochmüller over 6 years ago

Related to Story #54266: As an User I want FAL to be performant added

Actions

Copy link

Updated by Tim Lochmüller over 6 years ago

This problem is also part of the FAL abstraction!!

The sys_file_metadata use a select TCA configuration to sys_file. In my system are 500.000 files.
As the result the backend select all files, build Workspace Overlays (optional) and generate the record titles of 500.000 elements, that are not used in the backend view of the meta data.

I solve this problem by adding this line:

$GLOBALS['TCA']['sys_file_metadata']['columns']['file']['config']['foreign_table_where'] = ' AND sys_file.uid=###REC_FIELD_file###';

Regards,
Tim

Actions

Copy link

Updated by Benjamin Rannow over 6 years ago

I've created for my Projects a Patch.
On my Local machine from 4Seconds -> 900ms to open a Record with IRRE

https://gist.github.com/brannow/20420c9b7ad54faeec57b30aa4364b87

Actions

Copy link

Updated by Gerrit Code Review over 6 years ago

Status changed from New to Under Review

Patch set 16 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/54519

Actions

Copy link

#26

Updated by Tymoteusz Motylewski over 5 years ago

Status changed from Under Review to Resolved
% Done changed from 0 to 100

Applied in changeset be62d64d6b17b51b997cd88c5da7805587fc93db.

Actions

Copy link

#27

Updated by Anja Leichsenring over 5 years ago

Status changed from Resolved to New

Actions

Copy link

#28

Updated by Christian Eßl about 4 years ago

Related to Bug #82730: Opening record in backend painfully slow with foreign table and large main table added

Actions

Copy link

#29

Updated by Gerrit Code Review 5 months ago

Status changed from New to Under Review

Patch set 2 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/81871

Actions

Copy link

#30

Updated by Gerrit Code Review 5 months ago

Patch set 3 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/81871

Actions

Copy link

#31

Updated by Gerrit Code Review 5 months ago

Patch set 4 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/81871

Actions

Copy link

Also available in: Atom PDF

Project

General

Profile

TYPO3 Core

Custom queries

Watchers (5)

Bug #82100

Backend Form Dataprovider load every foreign Entity, not only the related one

Updated by Benjamin Rannow over 6 years ago

Updated by Benjamin Rannow over 6 years ago

Updated by Benjamin Rannow over 6 years ago

Updated by Michael Stopp over 6 years ago

Updated by Benjamin Rannow over 6 years ago

Updated by Tim Lochmüller over 6 years ago

Updated by Tim Lochmüller over 6 years ago

Updated by Benjamin Rannow over 6 years ago

Updated by Gerrit Code Review over 6 years ago

Updated by Gerrit Code Review over 6 years ago

Updated by Gerrit Code Review over 6 years ago

Updated by Gerrit Code Review over 6 years ago

Updated by Gerrit Code Review over 6 years ago

Updated by Gerrit Code Review over 6 years ago

Updated by Gerrit Code Review over 6 years ago

Updated by Gerrit Code Review over 6 years ago

Updated by Gerrit Code Review about 6 years ago

Updated by Tymoteusz Motylewski about 6 years ago

Updated by Gerrit Code Review about 6 years ago

Updated by Gerrit Code Review about 6 years ago

Updated by Gerrit Code Review about 6 years ago

Updated by Gerrit Code Review about 6 years ago

Updated by Gerrit Code Review almost 6 years ago

Updated by Gerrit Code Review over 5 years ago

Updated by Gerrit Code Review over 5 years ago

Updated by Tymoteusz Motylewski over 5 years ago

Updated by Anja Leichsenring over 5 years ago

Updated by Christian Eßl about 4 years ago

Updated by Gerrit Code Review 5 months ago

Updated by Gerrit Code Review 5 months ago

Updated by Gerrit Code Review 5 months ago