Task #55167
closedEpic #55070: Workpackages
Epic #55065: WP: Overall System Performance (Backend and Frontend)
Epic #55656: Optimize overall Extbase performance
Story #55168: Optimize Extbase generic persistence
Extbase: avoid parseQuery() / buildQuery() to be called multiple times
100%
Description
At the moment the extbase persistence runs buildQuery() and parseQuery() for every single getObjectDataByQuery() / getObjectCountByQuery(). When fetching child objects, this is called for every single object. This results in a lot of re-run code. In a simple blog_example page, this is called up to 200 times.
We will try to cache the built query as in an live environment always the same queries should be run, just with different parameters. Special care is to be taken for which parts of the query are "static" and about to be built into the cache identifier.
We will have to give the developer the chance to disable query caching though, because in some cases it might result in real big data piles. (e. g. matrix-like queries, allowing the front-end to influence the number of queried properties (search)).
Updated by Felix Oertel over 10 years ago
- Subject changed from avoid buildQuery() to be called multiple times to avoid parseQuery() / buildQuery() to be called multiple times
Updated by Alexander Schnitzler over 10 years ago
Did you already have a look at ticket #53514? Saves a lot of queries, too.
Edit: wrong ticket number.
Updated by Gerrit Code Review over 10 years ago
- Status changed from Accepted to Under Review
- Estimated time set to 0.00 h
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/27555
Updated by Ernesto Baschny over 10 years ago
- Subject changed from avoid parseQuery() / buildQuery() to be called multiple times to Extbase: avoid parseQuery() / buildQuery() to be called multiple times
- Category set to Extbase
- Estimated time set to 0.00 h
- TYPO3 Version set to 6.2
Updated by Gerrit Code Review over 10 years ago
- Estimated time set to 0.00 h
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/27555
Updated by Gerrit Code Review over 10 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/27555
Updated by Gerrit Code Review over 10 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/27555
Updated by Gerrit Code Review over 10 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/27555
Updated by Gerrit Code Review over 10 years ago
- Estimated time set to 0.00 h
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/27555
Updated by Gerrit Code Review over 10 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/27555
Updated by Gerrit Code Review over 10 years ago
- Estimated time set to 0.00 h
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/27555
Updated by Gerrit Code Review over 10 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/27555
Updated by Gerrit Code Review over 10 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/27555
Updated by Gerrit Code Review over 10 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/27555
Updated by Anonymous over 10 years ago
- Status changed from Under Review to Resolved
Applied in changeset 2583fd2a12dd3e740a9739f73aa602c2793999ec.
Updated by Alexander Stehlik over 10 years ago
After the patch was applied I get this SQL error with the news extension:
SELECT COUNT(*) FROM tx_news_domain_model_news WHERE ( tx_news_domain_model_news.uid IN ( SELECT uid_local FROM tx_news_domain_model_news_category_mm WHERE uid_foreign=? ) AND tx_news_domain_model_news.pid IN ('99999999') ) AND tx_news_domain_model_news.deleted=0 AND tx_news_domain_model_news.t3ver_state<=0 AND tx_news_domain_model_news.pid<>-1 AND tx_news_domain_model_news.hidden=0 AND tx_news_domain_model_news.starttime<=1395170580 AND (tx_news_domain_model_news.endtime=0 OR tx_news_domain_model_news.endtime>1395170580) AND (tx_news_domain_model_news.fe_group='' OR tx_news_domain_model_news.fe_group IS NULL OR tx_news_domain_model_news.fe_group='0' OR FIND_IN_SET('0',tx_news_domain_model_news.fe_group) OR FIND_IN_SET('-1',tx_news_domain_model_news.fe_group) ) AND (tx_news_domain_model_news.sys_language_uid IN (0,-1))
This is a var_dump of the sql query parts:
array (size=9) 'keywords' => array (size=0) empty 'tables' => array (size=1) 'tx_news_domain_model_news' => string 'tx_news_domain_model_news' (length=25) 'unions' => array (size=0) empty 'fields' => array (size=1) 'tx_news_domain_model_news' => string 'tx_news_domain_model_news.*' (length=27) 'where' => array (size=5) 0 => string '(' (length=1) 1 => string 'tx_news_domain_model_news.uid IN (SELECT uid_local FROM tx_news_domain_model_news_category_mm WHERE uid_foreign=?)' (length=114) 2 => string ' AND ' (length=5) 3 => string 'tx_news_domain_model_news.pid IN (:AND1pid)' (length=43) 4 => string ')' (length=1) 'additionalWhereClause' => array (size=0) empty 'orderings' => array (size=1) 0 => string 'tx_news_domain_model_news.datetime DESC' (length=39) 'limit' => null 'offset' => null
This is the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?) AND tx_news_domain_model_news.pid IN ('99999999')) AND tx_news_domain_model_n' at line 1
I think the problem is the unnamed parameter (?
) in the uid_foreign
query.
I also pointed out some problems in the code in Gerrit.
Updated by Alexander Stehlik over 10 years ago
Think I found it. Regression in https://forge.typo3.org/issues/57053
Updated by Alexander Stehlik over 10 years ago
- Estimated time set to 0.00 h
Found another regression in #57104 :(
Updated by Peter Beernink over 10 years ago
Also #57130 seems to be another regression..
Updated by Riccardo De Contardi almost 7 years ago
- Status changed from Resolved to Closed