Task #55169

Epic #55070: Workpackages

Epic #55065: WP: Overall System Performance (Backend and Frontend)

Epic #55656: Optimize overall Extbase performance

Story #55168: Optimize Extbase generic persistence

Extbase: fetch child objects in one query

Added by Felix Oertel over 5 years ago. Updated almost 2 years ago.

Status:
Rejected
Priority:
Should have
Assignee:
Category:
Extbase
Target version:
Start date:
2014-07-22
Due date:
% Done:

100%

TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
hard
Sprint Focus:

Description

To get the child-objects of a collection, extbase runs one query per object. If you got an objectStorage with 10 posts each with an author, extbase will use ten queries to get them. This generates a lot of database hits on applications making heavy use of child-objects (like FAL).

We have to try to reduce the queries to one per type. It should (in theory) not be too hard, to fetch ten authors in one "uid IN (1,2,3)"-query, cache them 1st level and map them to the corresponding parent object on request.

At the moment it's only possible to set @lazy-annotation globally in the domain model, so we should give the user a way to disable pre-fetching on a per-query-base.


Subtasks

Feature #60460: Refactor lazyLoading handling in extbaseClosedFelix Oertel


Related issues

Related to TYPO3 Core - Task #59917: User Repositories in internal persistence logic Rejected 2014-06-27 2014-07-09

History

#1 Updated by Alexander Schnitzler over 5 years ago

Why not cache "SELECT * FROM" on every table? scnr

#2 Updated by Felix Oertel over 5 years ago

Alexander Schnitzler wrote:

Why not cache "SELECT * FROM" on every table? scnr

Because it would - most likely - take up too much ram.

#4 Updated by Ernesto Baschny over 5 years ago

  • Subject changed from fetch child objects in one query to Extbase: fetch child objects in one query
  • Category set to Extbase
  • TYPO3 Version set to 6.2

#5 Updated by Xavier Perseguers over 5 years ago

Beware with "uid IN (1,2,3)"-query, there is a hard-limit to 1000 items in the list when using MySQL. This is why I implemented those kinds of queries many times like that:

        $chunkSize = 1000;
        $constraints = array();
        while (count($uids) > 0) {
            $chunk = array_slice($uids, 0, $chunkSize);
            if ($plainArray) {
                $constraints[] = 'uid IN (' . implode(',', $chunk) . ')';
            } else {
                $constraints[] = $query->in('uid', $chunk);
            }
            $uids = array_slice($uids, count($chunk));
        }

(was used in the context of doing complex queries to retrieve uids of objects to be returned in some findBy method the "old" way, efficiently, without Extbase, and only then use Extbase to fetch domain objects.

Of course with 10 or 100 posts, this is not a problem but we should think of real life scenarios as well!

#6 Updated by Felix Oertel over 5 years ago

  • Due date set to 2014-07-09
  • Target version changed from 6.2.0 to 7.0
  • Start date changed from 2014-01-20 to 2014-06-27
  • Estimated time set to 48.00 h
  • translation missing: en.field_remaining_hours set to 48.0

#7 Updated by Gerrit Code Review about 5 years ago

  • Status changed from Accepted to Under Review
  • translation missing: en.field_remaining_hours deleted (48.0)

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

#8 Updated by Gerrit Code Review about 5 years ago

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

#9 Updated by Gerrit Code Review about 5 years ago

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

#10 Updated by Mathias Schreiber almost 5 years ago

  • Target version changed from 7.0 to 7.1 (Cleanup)

#11 Updated by Gerrit Code Review over 4 years ago

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

#12 Updated by Christian Kuhn over 4 years ago

the pending patch was abandoned.

#13 Updated by Benni Mack over 4 years ago

  • Target version changed from 7.1 (Cleanup) to 7.4 (Backend)

#14 Updated by Susanne Moog about 4 years ago

  • Target version changed from 7.4 (Backend) to 7.5

#15 Updated by Benni Mack about 4 years ago

  • Target version changed from 7.5 to 8 LTS

#16 Updated by Anja Leichsenring almost 3 years ago

  • Status changed from Under Review to Rejected

Also available in: Atom PDF