Project

General

Profile

Actions

Bug #61184

closed

Backend - Page Tree in Workspace & Publish content - Performance Issue - BackendUtility::countVersionsOfRecordsOnPage

Added by Andreas Lingott over 9 years ago. Updated almost 9 years ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2014-08-26
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

When working in a workspace (Typo3 Backend), loading of the page tree is very slow and sometimes resolving in a connection timeout.
Also, when applying / publish changes of a workspace to the live workspace, changes are getting lost caused by the connection timeout.

That's because, there are a lot of SQL queries which took more than 8 seconds to be processed.

The bottle neck is the following method:
\TYPO3\CMS\Backend\Utility\BackendUtility::countVersionsOfRecordsOnPage

In our case, this method generates queries like this:

mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND (A.t3ver_oid=B.uid AND A.t3ver_state<>4 OR A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4) AND A.deleted=0 AND B.deleted=0;
+----+-------------+-------+------+------------------+--------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys    | key    | key_len | ref   | rows  | Extra       |
+----+-------------+-------+------+------------------+--------+---------+-------+-------+-------------+
|  1 | SIMPLE      | B     | ref  | PRIMARY,parent   | parent | 4       | const |   120 | Using where |
|  1 | SIMPLE      | A     | ref  | t3ver_oid,parent | parent | 4       | const | 12507 | Using where |
+----+-------------+-------+------+------------------+--------+---------+-------+-------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND (A.t3ver_oid=B.uid AND A.t3ver_state<>4 OR A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4) AND A.deleted=0 AND B.deleted=0;
Empty set (8.21 sec)

The problem here is the "OR" part of the query.

A.t3ver_oid=B.uid AND A.t3ver_state<>4 OR A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4

That's only one of round about 2.000 queries to be need to generate the page tree, and at least 15 of them takes more than 8 seconds.

The solution is, to split this kind of queries into two and merge the results together.

mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.uid AND A.t3ver_state<>4 AND A.deleted=0 AND B.deleted=0;
+----+-------------+-------+------+------------------+-----------+---------+------------------------------+------+-------------+
| id | select_type | table | type | possible_keys    | key       | key_len | ref                          | rows | Extra       |
+----+-------------+-------+------+------------------+-----------+---------+------------------------------+------+-------------+
|  1 | SIMPLE      | B     | ref  | PRIMARY,parent   | parent    | 4       | const                        |  120 | Using where |
|  1 | SIMPLE      | A     | ref  | t3ver_oid,parent | t3ver_oid | 8       | typo3_cms_switch.B.uid,const |    4 | Using where |
+----+-------------+-------+------+------------------+-----------+---------+------------------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4 AND A.deleted=0 AND B.deleted=0;
+----+-------------+-------+------+------------------+-----------+---------+----------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys    | key       | key_len | ref                                    | rows | Extra       |
+----+-------------+-------+------+------------------+-----------+---------+----------------------------------------+------+-------------+
|  1 | SIMPLE      | B     | ref  | parent           | parent    | 4       | const                                  |  120 | Using where |
|  1 | SIMPLE      | A     | ref  | t3ver_oid,parent | t3ver_oid | 8       | typo3_cms_switch.B.t3ver_move_id,const |    4 | Using where |
+----+-------------+-------+------+------------------+-----------+---------+----------------------------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.uid AND A.t3ver_state<>4 AND A.deleted=0 AND B.deleted=0;
Empty set (0.00 sec)

mysql> SELECT B.uid as live_uid, A.uid as offline_uid FROM tt_content A,tt_content B WHERE A.pid=-1 AND B.pid=42 AND A.t3ver_wsid=12 AND A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=4 AND A.deleted=0 AND B.deleted=0;
Empty set (0.70 sec)

So this construct is 7.3 seconds faster for only one query.

Please take a look on the attached patch, which solves the issue on my end.


Files

BackendUtilitycountVersionsOfRecordsOnPage.patch (2.45 KB) BackendUtilitycountVersionsOfRecordsOnPage.patch Patch File Andreas Lingott, 2014-08-26 10:25
BackendUtility.php (178 KB) BackendUtility.php Changed file with fix Andreas Lingott, 2014-08-26 10:25
BackendUtility.php (181 KB) BackendUtility.php Original unchanged file without fix Andreas Lingott, 2014-08-26 10:25

Related issues 3 (0 open3 closed)

Related to TYPO3 Core - Task #50349: Reduce SQL queries of page tree in workspacesClosed2013-07-24

Actions
Related to TYPO3 Core - Bug #66231: Workspace very slow loading page treeClosed2015-04-02

Actions
Related to TYPO3 Core - Task #69439: Enhance SQL query reduction in page tree in workspacesClosedOliver Hader2015-08-31

Actions
Actions

Also available in: Atom PDF