Task #55167

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: avoid parseQuery() / buildQuery() to be called multiple times

Added by Felix Oertel almost 8 years ago. Updated about 4 years ago.

Status:
Closed
Priority:
Should have
Assignee:
Category:
Extbase
Target version:
Start date:
2014-03-20
Due date:
2014-03-20
% Done:

100%

Estimated time:
(Total: 0.00 h)
TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
hard
Sprint Focus:

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)).


Subtasks

Task #55661: Add functional tests for extbase persistenceClosedFelix Oertel2014-03-202014-03-20

Actions
Task #57173: Use of cf_extbase_typo3dbbackend_queries results in fatalClosedXavier Perseguers2014-03-22

Actions
Task #57130: Extbase Queries with NULL comparison don't work anymoreClosed2014-03-21

Actions
Task #57104: Regression: Query building fails for LazyObjectStorageClosed2014-03-20

Actions
Task #57263: Typo3DbQueryParser does not handle LowerCaseInterface correctlyClosedFelix Oertel2014-03-25

Actions
Task #57338: Make Typo3DbQueryParser SingletonClosed2014-03-26

Actions

Related issues

Related to TYPO3 Core - Task #53514: Extbase: DataMapper causes redundant queriesClosedNico de Haen2013-11-11

Actions
Related to TYPO3 Core - Bug #57053: Regression: Extbase query builder uses invalid parameter in parseComparison()Closed2014-03-18

Actions
Related to TYPO3 Core - Task #57280: Introduce setting to be able to disable Extbase query cache for all queries.ClosedMarc Bastian Heinrichs2014-03-25

Actions
Related to TYPO3 Core - Task #57275: Add first level cache for the query cacheClosed2014-03-25

Actions
Follows TYPO3 Core - Task #55360: Extbase: use preparedStatement in generic persistenceClosedFelix Oertel2014-03-19

Actions
#1

Updated by Felix Oertel almost 8 years ago

  • Parent task set to #55168
#2

Updated by Felix Oertel almost 8 years ago

  • Subject changed from avoid buildQuery() to be called multiple times to avoid parseQuery() / buildQuery() to be called multiple times
#3

Updated by Alexander Schnitzler almost 8 years ago

Did you already have a look at ticket #53514? Saves a lot of queries, too.

Edit: wrong ticket number.

#4

Updated by Felix Oertel almost 8 years ago

yep, got this on my list, thanks.

#5

Updated by Gerrit Code Review almost 8 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

#6

Updated by Ernesto Baschny almost 8 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
#7

Updated by Gerrit Code Review almost 8 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

#8

Updated by Gerrit Code Review almost 8 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

#9

Updated by Gerrit Code Review almost 8 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

#10

Updated by Gerrit Code Review almost 8 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

#11

Updated by Gerrit Code Review almost 8 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

#12

Updated by Gerrit Code Review almost 8 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

#13

Updated by Gerrit Code Review almost 8 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

#14

Updated by Gerrit Code Review almost 8 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

#15

Updated by Gerrit Code Review almost 8 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

#16

Updated by Gerrit Code Review almost 8 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

#17

Updated by Anonymous almost 8 years ago

  • Status changed from Under Review to Resolved
#18

Updated by Alexander Stehlik almost 8 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.

#19

Updated by Alexander Stehlik almost 8 years ago

Think I found it. Regression in https://forge.typo3.org/issues/57053

#20

Updated by Alexander Stehlik almost 8 years ago

  • Estimated time set to 0.00 h

Found another regression in #57104 :(

#21

Updated by Peter Beernink almost 8 years ago

Also #57130 seems to be another regression..

#22

Updated by Alexander Stehlik almost 8 years ago

  • Estimated time deleted (0.00 h)
#23

Updated by Riccardo De Contardi about 4 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF