Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1602

Executors for Class Table Inheritance (JOINED) are extremely slow on MySQL

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.2-BETA2
    • Fix Version/s: None
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      Debian, MySQL 5.5.17

      Description

      Update and delete executors for Class Table Inheritance (JOINED) are extremely slow on MySQL platform. It is most probably due to use of subselect on the temporary table.
      The slowdown is really significant as the table size increases. As an example, lets have a root entity with one subclass:

      /**
       * @Entity
       * @InheritanceType("JOINED")
       * @DiscriminatorColumn(name="discr", type="string")
       * @DiscriminatorMap({"root" = "Root", "a" = "SubA"})
       */
      class Root
      {
      	/**
      	 * @Column(type="integer")
      	 * @Id
      	 * @GeneratedValue
      	 */
      	private $id;
      
      	/**
      	 * @Column(type="integer")
      	 */
      	private $xyz;
      }
      
      /**
       * @Entity
       */
      class SubA extends Root
      {
      	/**
      	 * @Column(type="integer")
      	 */
      	private $foo;
      }
      

      Now lets perform a simple DQL UPDATE:

      UPDATE Entities\Root r SET r.xyz = 123 WHERE r.id > ?
      

      (note: always the upper half of entries)
      Which creates following SQLs:

      CREATE TEMPORARY TABLE Root_id_tmp (id INT NOT NULL)
      INSERT INTO Root_id_tmp (id) SELECT t0.id FROM Root t0 LEFT JOIN SubA s0_ ON t0.id = s0_.id WHERE t0.id > 25000
      UPDATE Root SET xyz = 123 WHERE (id) IN (SELECT id FROM Root_id_tmp)
      DROP TEMPORARY TABLE Root_id_tmp
      

      The time spent on this on MySQL 5.5.17 and PostgreSQL 9.1 is:

      no. of entries 500 1000 2500 5000 10000 20000 50000
      MySQL 0.26s 0.35s 1.1s 3.68s 14.13s 54.44s 338s
      PostgreSQL 0.10s 0.10s 0.13s 0.15s 0.22s 0.35s 1.01s

      As you can see, MySQL is drastically slower on even relatively small tables. This currently makes Doctrine unusable for this type of inheritance on MySQL. The solution probably would be to avoid subselect in WHERE clause in Doctrine\ORM\Query\Exec\MultiTableUpdateExecutor and Doctrine\ORM\Query\Exec\MultiTableDeleteExecutor.

      Feel free to try/modify the test script yourself, it's here.

        Activity

        Michael Moravec created issue -
        Michael Moravec made changes -
        Field Original Value New Value
        Description Update and delete executors for Class Table Inheritance (JOINED) are extremely slow on MySQL platform. It is most probably due to use of subselect on the temporary table.
        The slowdown is really significant as the table size increases. As an example, lets have a root entity with one subclass:

        {code}
        /**
         * @Entity
         * @InheritanceType("JOINED")
         * @DiscriminatorColumn(name="discr", type="string")
         * @DiscriminatorMap({"root" = "Root", "a" = "SubA"})
         */
        class Root
        {
        /**
        * @Column(type="integer")
        * @Id
        * @GeneratedValue
        */
        private $id;

        /**
        * @Column(type="integer")
        */
        private $xyz;
        }
        {code}

        {code}
        /**
         * @Entity
         */
        class SubA extends Root
        {
        /**
        * @Column(type="integer")
        */
        private $foo;
        }
        {code}

        Now lets perform a simple DQL UPDATE:
        {code}
        UPDATE Entities\Root r SET r.xyz = 123 WHERE r.id > ?
        {code}
        (note: always the upper half of entries)
        Which creates following SQLs:
        {code}
        CREATE TEMPORARY TABLE Root_id_tmp (id INT NOT NULL)
        INSERT INTO Root_id_tmp (id) SELECT t0.id FROM Root t0 LEFT JOIN SubA s0_ ON t0.id = s0_.id WHERE t0.id > 25000
        UPDATE Root SET xyz = 123 WHERE (id) IN (SELECT id FROM Root_id_tmp)
        DROP TEMPORARY TABLE Root_id_tmp
        {code}

        The time spent on this on MySQL 5.5.17 and PostgreSQL 9.1 is:

        || no. of entries || 500 || 1000 || 2500 || 5000 || 10000 || 20000 || 50000 ||
        | *MySQL* | 0.26s | 0.35s | 1.1s | 3.68s | 14.13s | 54.44s | 338s |
        | *PostgreSQL* | 0.10s | 0.10s | 0.13s | 0.15s | 0.22s | 0.35s | 1.01s |

        As you can see, MySQL is drastically slower on even relatively small tables. This currently makes Doctrine unusable for this type of inheritance on MySQL. The solution probably would be to avoid subselect in WHERE clause.

        Feel free to try/modify the test script yourself, it's [here|https://github.com/Majkl578/doctrine2-slow-executors].
        Update and delete executors for Class Table Inheritance (JOINED) are extremely slow on MySQL platform. It is most probably due to use of subselect on the temporary table.
        The slowdown is really significant as the table size increases. As an example, lets have a root entity with one subclass:

        {code}
        /**
         * @Entity
         * @InheritanceType("JOINED")
         * @DiscriminatorColumn(name="discr", type="string")
         * @DiscriminatorMap({"root" = "Root", "a" = "SubA"})
         */
        class Root
        {
        /**
        * @Column(type="integer")
        * @Id
        * @GeneratedValue
        */
        private $id;

        /**
        * @Column(type="integer")
        */
        private $xyz;
        }
        {code}

        {code}
        /**
         * @Entity
         */
        class SubA extends Root
        {
        /**
        * @Column(type="integer")
        */
        private $foo;
        }
        {code}

        Now lets perform a simple DQL UPDATE:
        {code}
        UPDATE Entities\Root r SET r.xyz = 123 WHERE r.id > ?
        {code}
        (note: always the upper half of entries)
        Which creates following SQLs:
        {code}
        CREATE TEMPORARY TABLE Root_id_tmp (id INT NOT NULL)
        INSERT INTO Root_id_tmp (id) SELECT t0.id FROM Root t0 LEFT JOIN SubA s0_ ON t0.id = s0_.id WHERE t0.id > 25000
        UPDATE Root SET xyz = 123 WHERE (id) IN (SELECT id FROM Root_id_tmp)
        DROP TEMPORARY TABLE Root_id_tmp
        {code}

        The time spent on this on MySQL 5.5.17 and PostgreSQL 9.1 is:

        || no. of entries || 500 || 1000 || 2500 || 5000 || 10000 || 20000 || 50000 ||
        | *MySQL* | 0.26s | 0.35s | 1.1s | 3.68s | 14.13s | 54.44s | 338s |
        | *PostgreSQL* | 0.10s | 0.10s | 0.13s | 0.15s | 0.22s | 0.35s | 1.01s |

        As you can see, MySQL is drastically slower on even relatively small tables. This currently makes Doctrine unusable for this type of inheritance on MySQL. The solution probably would be to avoid subselect in WHERE clause in Doctrine\ORM\Query\Exec\MultiTableUpdateExecutor and Doctrine\ORM\Query\Exec\MultiTableDeleteExecutor.

        Feel free to try/modify the test script yourself, it's [here|https://github.com/Majkl578/doctrine2-slow-executors].
        Michael Moravec made changes -
        Summary Executor for Class Table Inheritance (JOINED) is extremely slow on MySQL Executors for Class Table Inheritance (JOINED) are extremely slow on MySQL
        Michael Moravec made changes -
        Affects Version/s 2.2-BETA2 [ 10188 ]
        Affects Version/s Git Master [ 10100 ]
        Benjamin Eberlei made changes -
        Issue Type Bug [ 1 ] Improvement [ 4 ]
        Priority Critical [ 2 ] Major [ 3 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 13362 ] jira-feedback [ 14011 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 14011 ] jira-feedback2 [ 15875 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 15875 ] jira-feedback3 [ 18131 ]

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Michael Moravec
          • Votes:
            3 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: