Task #50349

Reduce SQL queries of page tree in workspaces

Added by Oliver Hader about 6 years ago. Updated almost 2 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Performance
Target version:
Start date:
2013-07-24
Due date:
% Done:

100%

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

Description

Nodes of the page tree are highlighted, if that particular node has versioned elements in the current workspace. However, this is determined by iterating over all tables and all records for each page. If having installed many extensions with many tables any many records, this will produce a lot of SQL queries.

The process of "does have versions" can be stopped once the first version for a page/node has been found.


Related issues

Related to TYPO3 Core - Bug #61184: Backend - Page Tree in Workspace & Publish content - Performance Issue - BackendUtility::countVersionsOfRecordsOnPage Closed 2014-08-26
Related to TYPO3 Core - Bug #66231: Workspace very slow loading page tree Closed 2015-04-02
Related to TYPO3 Core - Task #69439: Enhance SQL query reduction in page tree in workspaces Closed 2015-08-31

Associated revisions

Revision 37cd2d50 (diff)
Added by Nicole Cordes over 4 years ago

[TASK] Reduce SQL queries of page tree in workspaces

Nodes of the page tree are highlighted, if that particular node
has versioned elements in the current workspace. However, this is
determined by iterating over all tables and all records for each
page. If having installed many extensions with many tables and
many records, this will produce a lot of SQL queries.

This patch introduces a new cache for record versions per page which is
fetched once from the database. This cache is located within the
\TYPO3\CMS\Workspaces\Service\WorkspaceService and can be shared between
several other classes as this is implements a SingletonInterface.

Resolves: #50349
Resolves: #66231
Related: #61184
Releases: master, 6.2
Change-Id: Ibaef07bdab87147c6826af8578870c52803dfe03
Reviewed-on: http://review.typo3.org/22528
Reviewed-by: Georg Ringer <>
Tested-by: Georg Ringer <>
Reviewed-by: Nicole Cordes <>
Tested-by: Nicole Cordes <>

Revision d2c4bbbb (diff)
Added by Nicole Cordes over 4 years ago

[TASK] Reduce SQL queries of page tree in workspaces

Nodes of the page tree are highlighted, if that particular node
has versioned elements in the current workspace. However, this is
determined by iterating over all tables and all records for each
page. If having installed many extensions with many tables and
many records, this will produce a lot of SQL queries.

This patch introduces a new cache for record versions per page which is
fetched once from the database. This cache is located within the
\TYPO3\CMS\Workspaces\Service\WorkspaceService and can be shared between
several other classes as this is implements a SingletonInterface.

Resolves: #50349
Resolves: #66231
Related: #61184
Releases: master, 6.2
Change-Id: Ibaef07bdab87147c6826af8578870c52803dfe03
Reviewed-on: http://review.typo3.org/41425
Reviewed-by: Nicole Cordes <>
Tested-by: Nicole Cordes <>

Revision c45de676 (diff)
Added by Nicole Cordes over 4 years ago

[FOLLOWUP][TASK] Reduce SQL queries of page tree in workspaces

The patch adds a check if workspaces is activated before using its
class and function.

Releases: 6.2
Resolves: #50349
Resolves: #66231
Related: #61184
Change-Id: I12bc2d496f2216725ac97a8640bc6c431630cc7a
Reviewed-on: http://review.typo3.org/41553
Reviewed-by: Daniel Goerz <>
Reviewed-by: Susanne Moog <>
Tested-by: Susanne Moog <>
Reviewed-by: Nicole Cordes <>
Tested-by: Nicole Cordes <>

Revision 319b0aa3 (diff)
Added by Oliver Hader over 3 years ago

[TASK] Enhance SQL query reduction in page tree in workspaces

This changeset is a follow-up to the changes for issue #50349 and
it partly reverts the changes that have been introduced back then.

The most important changes are:

  • Allow early return on first found record in hasPageVersions.
    The previous implementation performed SQL queries on all tables
    that are defined in the global TCA array. Now the first version
    occurrence is enough to infer that a page has any versions.
  • Integrate hooks to modify the determined results. This way, the
    meaning of having versions can be further modified by hooks.
    For instance, the default behavior of the TYPO3 core is to create
    a workspace version record on persisting the same record in the
    backend - without any actual changes to the data model.

Resolves: #69439
Releases: master
Change-Id: I119a79d8fad82b0dc5891861af45ecfdbc681820
Reviewed-on: https://review.typo3.org/42944
Reviewed-by: Andreas Wolf <>
Reviewed-by: Susanne Moog <>
Tested-by: Susanne Moog <>
Reviewed-by: Benni Mack <>
Tested-by: Benni Mack <>

History

#1 Updated by Oliver Hader about 6 years ago

  • Tracker changed from Bug to Task

#2 Updated by Gerrit Code Review about 6 years ago

  • Status changed from New to Under Review

Patch set 1 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/22528

#3 Updated by Mathias Schreiber over 4 years ago

  • Target version set to 7.4 (Backend)

Hey Olly, wanna meet up to dig into ths one?
The idea itself is the right way.

#4 Updated by Alexander Opitz over 4 years ago

In #66231 this patch seams to be used in a 1:1 manner which was tested in #61184 and reported as fine.
So we should go forward with this for TYPO3 CMS 7 LTS.

#5 Updated by Grigori Prokhorov over 4 years ago

Hi Alexander,

what speaks against including just https://review.typo3.org/#/c/38700/ in the next 6.2.x?

I'm asking since IF the situation occurs which this patch resolves, it actually makes the usage of the workspaces completely impossible.
The patch from #66231 as a "stand-alone" solution (as compared to https://review.typo3.org/#/c/22528/) only affects a very specific part of the core since the affected method is used in exactly those two classes which are included.

Best,
Grigori

#6 Updated by Christian Opitz over 4 years ago

This issue makes the workspace feature nearly unusable. +1 for getting this into 6.2

#7 Updated by Gerrit Code Review over 4 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/22528

#8 Updated by Gerrit Code Review over 4 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/22528

#9 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/22528

#10 Updated by Gerrit Code Review over 4 years ago

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

#11 Updated by Gerrit Code Review over 4 years ago

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

#12 Updated by Nicole Cordes over 4 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#13 Updated by Gerrit Code Review over 4 years ago

  • Status changed from Resolved to Under Review

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

#14 Updated by Gerrit Code Review over 4 years ago

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

#15 Updated by Nicole Cordes over 4 years ago

  • Status changed from Under Review to Resolved

#16 Updated by Gerrit Code Review over 4 years ago

  • Status changed from Resolved to Under Review

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

#17 Updated by Nicole Cordes over 4 years ago

  • Status changed from Under Review to Resolved

#18 Updated by Gerrit Code Review about 4 years ago

  • Status changed from Resolved to Under Review

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

#19 Updated by Oliver Hader about 4 years ago

  • Status changed from Under Review to Closed

Follow-up #69439

#20 Updated by Oliver Hader about 4 years ago

  • Status changed from Closed to Resolved

#21 Updated by Riccardo De Contardi almost 2 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF