Bug #82100
open
Backend Form Dataprovider load every foreign Entity, not only the related one
100%
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,
],
],
Updated by Michael Stopp over 7 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.
- 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.
Updated by Benjamin Rannow over 7 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.
Updated by Tim Lochmüller about 7 years ago
- Related to Story #54266: As an User I want FAL to be performant added
Updated by Tim Lochmüller about 7 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
Updated by Benjamin Rannow about 7 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
Updated by Gerrit Code Review about 7 years 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
Updated by Gerrit Code Review about 7 years 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
Updated by Gerrit Code Review about 7 years 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
Updated by Gerrit Code Review about 7 years 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
Updated by Gerrit Code Review about 7 years 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
Updated by Gerrit Code Review about 7 years 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
Updated by Gerrit Code Review about 7 years 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
Updated by Gerrit Code Review about 7 years 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
Updated by Gerrit Code Review over 6 years 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
Updated by Tymoteusz Motylewski over 6 years ago
- Related to Feature #65505: Add paging for IRRE-records (working with many IRRE-entries) added
Updated by Gerrit Code Review over 6 years 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
Updated by Gerrit Code Review over 6 years 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
Updated by Gerrit Code Review over 6 years 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
Updated by Gerrit Code Review over 6 years 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
Updated by Gerrit Code Review over 6 years 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
Updated by Gerrit Code Review about 6 years 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
Updated by Gerrit Code Review about 6 years 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
Updated by Tymoteusz Motylewski about 6 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset be62d64d6b17b51b997cd88c5da7805587fc93db.
Updated by Anja Leichsenring almost 6 years ago
- Status changed from Resolved to New
Updated by Christian Eßl over 4 years ago
- Related to Bug #82730: Opening record in backend painfully slow with foreign table and large main table added
Updated by Gerrit Code Review 12 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
Updated by Gerrit Code Review 12 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
Updated by Gerrit Code Review 12 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
Updated by Gerrit Code Review 6 months ago
Patch set 5 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
Updated by Gerrit Code Review 6 months ago
Patch set 6 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
Updated by Gerrit Code Review 4 months ago
Patch set 7 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
Updated by Gerrit Code Review 4 months ago
Patch set 8 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