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
| Status: | Under Review | Start date: | 2014-07-22 | |
|---|---|---|---|---|
| Priority: | Could have | Due date: | ||
| Assigned To: | Felix Oertel | % Done: | 0% |
|
| Category: | Extbase | Spent time: | - | |
| Target version: | 7.4 (Backend) | |||
| TYPO3 Version: | 6.2 | Complexity: | hard | |
| PHP Version: | 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
Related issues
History
#1 Updated by Alexander Schnitzler over 1 year ago
Why not cache "SELECT * FROM" on every table? scnr
#2 Updated by Felix Oertel over 1 year ago
Alexander Schnitzler wrote:
Why not cache "SELECT * FROM" on every table? scnr
Because it would - most likely - take up too much ram.
#3 Updated by Stephan Großberndt over 1 year ago
#4 Updated by Ernesto Baschny over 1 year 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 1 year 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:
1 $chunkSize = 1000;
2 $constraints = array();
3 while (count($uids) > 0) {
4 $chunk = array_slice($uids, 0, $chunkSize);
5 if ($plainArray) {
6 $constraints[] = 'uid IN (' . implode(',', $chunk) . ')';
7 } else {
8 $constraints[] = $query->in('uid', $chunk);
9 }
10 $uids = array_slice($uids, count($chunk));
11 }
(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 about 1 year 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
- Remaining (hours) set to 48.0
#7 Updated by Gerrit Code Review 12 months ago
- Status changed from Accepted to Under Review
- 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 12 months 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 12 months 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 7 months ago
- Target version changed from 7.0 to 7.1 (Cleanup)
#11 Updated by Gerrit Code Review 5 months 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 about 1 month ago
the pending patch was abandoned.
#13 Updated by Benjamin Mack about 1 month ago
- Target version changed from 7.1 (Cleanup) to 7.4 (Backend)