Bug #82100

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

Added by Benjamin Rannow over 1 year ago. Updated 5 months ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
Performance
Target version:
-
Start date:
2017-08-14
Due date:
% Done:

100%

TYPO3 Version:
8
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

Related to TYPO3 Core - Story #54266: As an User I want FAL to be performant Closed 2013-11-16 2013-12-31
Related to TYPO3 Core - Feature #65505: Add paging for IRRE-records (working with many IRRE-entries) New 2015-03-04

Associated revisions

Revision be62d64d (diff)
Added by Tymoteusz Motylewski 5 months ago

[BUGFIX] Improve Performance for Inline Elements

Invisible and not translated records will not be retrieved anymore.

Currently every inline element will be translated even if the default
language is requested. This causes a massive performance leak because not only
the current record is loaded, but every other record with the same pid.

Not expanded inline children will be also be ignored.

Resolves: #82100
Releases: master
Change-Id: I3cbe0039f5d15c921d81fc3e634ef38aa06cd18a
Reviewed-on: https://review.typo3.org/54519
Reviewed-by: Georg Ringer <>
Tested-by: Georg Ringer <>
Reviewed-by: Anja Leichsenring <>
Tested-by: Anja Leichsenring <>

Revision 5f700cfe (diff)
Added by Oliver Hader 5 months ago

Revert "[BUGFIX] Improve Performance for Inline Elements"

This reverts commit be62d64d6b17b51b997cd88c5da7805587fc93db.

The fix introduced breaking behavior on for additional processings like
`label_userFunc` based on the expanded/collapsed state (which is stored
in the users `uc` blob).

Change-Id: I12a31391fd3f6e50946d0f98d21939a501a95eee
Releases: master
Related: #82100
Reviewed-on: https://review.typo3.org/58936
Tested-by: TYPO3com <>
Reviewed-by: Anja Leichsenring <>
Tested-by: Anja Leichsenring <>
Reviewed-by: Benni Mack <>
Tested-by: Benni Mack <>

History

#1 Updated by Benjamin Rannow over 1 year ago

  • Description updated (diff)

Update Description

#2 Updated by Benjamin Rannow over 1 year ago

  • Description updated (diff)

#3 Updated by Benjamin Rannow over 1 year ago

  • Description updated (diff)

Add TCA Example

#4 Updated by Michael Stopp over 1 year 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.

#5 Updated by Benjamin Rannow over 1 year 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.

#6 Updated by Tim Lochm├╝ller over 1 year ago

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

#7 Updated by Tim Lochm├╝ller over 1 year 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

#8 Updated by Benjamin Rannow over 1 year 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

#9 Updated by Gerrit Code Review over 1 year ago

  • Status changed from New to Under Review

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

#10 Updated by Gerrit Code Review over 1 year ago

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

#11 Updated by Gerrit Code Review over 1 year ago

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

#12 Updated by Gerrit Code Review over 1 year ago

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

#13 Updated by Gerrit Code Review over 1 year ago

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

#14 Updated by Gerrit Code Review over 1 year ago

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

#15 Updated by Gerrit Code Review over 1 year ago

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

#16 Updated by Gerrit Code Review over 1 year ago

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

#17 Updated by Gerrit Code Review about 1 year ago

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

#18 Updated by Tymoteusz Motylewski about 1 year ago

  • Related to Feature #65505: Add paging for IRRE-records (working with many IRRE-entries) added

#19 Updated by Gerrit Code Review about 1 year ago

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

#20 Updated by Gerrit Code Review about 1 year ago

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

#21 Updated by Gerrit Code Review about 1 year ago

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

#22 Updated by Gerrit Code Review about 1 year ago

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

#23 Updated by Gerrit Code Review 11 months ago

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

#24 Updated by Gerrit Code Review 6 months ago

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

#25 Updated by Gerrit Code Review 5 months ago

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

#26 Updated by Tymoteusz Motylewski 5 months ago

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

#27 Updated by Anja Leichsenring 5 months ago

  • Status changed from Resolved to New

Also available in: Atom PDF