Issue Bug Under Review Orphaned

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

Benjamin Rannow 08/14/17, 16:13 updated 07/12/24, 17:43

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

Reviews 3

History 1235

Show 810 earlier messages
  1. Benjamin Rannow commented and changed description <span data-processor="relativetime" data-value="2017-08-14T16:18:45+00:00" > 08/14/17, 16:18 </span>

    Update Description

  2. Michael Stopp commented <span data-processor="relativetime" data-value="2017-08-15T12:47:28+00:00" > 08/15/17, 12:47 </span>

    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.
  3. Benjamin Rannow commented and changed Complexity medium hard <span data-processor="relativetime" data-value="2017-08-16T19:32:10+00:00" > 08/16/17, 19:32 </span>

    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.

  4. Tim Lochmüller commented <span data-processor="relativetime" data-value="2017-09-13T13:18:27+00:00" > 09/13/17, 13:18 </span>

    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

  5. Benjamin Rannow commented <span data-processor="relativetime" data-value="2017-11-01T12:00:40+00:00" > 11/01/17, 12:00 </span>

    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

  6. Gerrit Code Review pushed patch set 1 for master to change 54519 status New Under Review <span data-processor="relativetime" data-value="2017-11-01T13:56:12+00:00" > 11/01/17, 13:56 </span>

  1. Tymoteusz Motylewski commented and changed status Under Review Resolved done 0 % 100 % <span data-processor="relativetime" data-value="2018-11-08T21:30:04+00:00" > 11/08/18, 21:30 </span>

    Applied in changeset commit:be62d64d6b17b51b997cd88c5da7805587fc93db.