Doctrine 1
  1. Doctrine 1
  2. DC-672

Adding DISTINCT when not needed hurts performance badly

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Can't Fix
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      LAMP - other details seem irrelevant

      Description

      Doctrine seems to be adding DISTINCT to SQL queries without any good reason (to the table's primary key!) and thus SERIOUSLY HURTS performance. It even seems like Doctrine adds a whole new DISTINCT query without a good reason. I believe the extra query is the result of the different implementation of LIMIT in Doctrine vs. SQL. However, when a unique index is involved, this extra query seems redundant.

      In the following example, execution time went from ~5sec down to ~1sec when I simply removed the DISTINCT added by Doctrine.

      Why is the extra query created anyway?
      Is there any way to prevent Doctrine to add the DISTINCT part when there's a unique index on the column?

      Here's the DQL:

      SELECT i.id, ip.item_id, pic.id, pic.width, pic.height, i.producer_id, i.series_id, i.issue_date, t_i.name, FROM ZpcPhonecard i INNER JOIN i.Picture pic WITH pic.width > 125 AND pic.width >= pic.height LEFT JOIN i.Translation t_i WITH t_i.lang = 0 INDEXBY t_i.lang WHERE i.front_picture_id > 0 AND i.state = ? AND i.zpc_system_id = 4 ORDER BY i.id DESC LIMIT 20

      And the resulting SQLs:

      SELECT DISTINCT z3.id FROM zpc_phonecard z3 INNER JOIN picture p2 ON z3.front_picture_id = p2.id AND ((p2.width > 125 AND p2.width >= p2.height)) LEFT JOIN zpc_phonecard_translation z4 ON z3.id = z4.id AND (z4.lang = 0) WHERE (z3.front_picture_id > 0 AND z3.state = 'Active') AND z3.zpc_system_id = 4 ORDER BY z3.id DESC LIMIT 20;

      SELECT z.id AS z_id, z.producer_id AS zproducer_id, z.series_id AS zseries_id, z.issue_date AS zissue_date, p.id AS pid, p.width AS pwidth, p.height AS pheight, z2.id AS z2id, z2.lang AS z2lang, z2.name AS z2_name FROM zpc_phonecard z INNER JOIN picture p ON z.front_picture_id = p.id AND ((p.width > 125 AND p.width >= p.height)) LEFT JOIN zpc_phonecard_translation z2 ON z.id = z2.id AND (z2.lang = 0) WHERE z.id IN ('231871', '231870', '231869', '231868', '231865', '231864', '231863', '231862', '231861', '231860', '231859', '231858', '231857', '231856', '231855', '231853', '231852', '231851', '231850', '231849') AND ((z.front_picture_id > 0 AND z.state = 'Active') AND z.zpc_system_id = 4) ORDER BY z.id DESC

      Here's my own translation from DQL to A MUCH FASTER SINGLE SQL statement:
      SELECT i.id, pic.id, pic.width, pic.height, i.producer_id, i.series_id, i.issue_date, t_i.name FROM Zpc_Phonecard i INNER JOIN Picture pic ON i.front_picture_id=pic.id AND pic.width > 125 AND pic.width >= pic.height LEFT JOIN Zpc_Phonecard_translation t_i ON i.id = t_i.id AND t_i.lang = 0 WHERE i.front_picture_id > 0 AND i.state = 'Active' AND i.zpc_system_id = 4 ORDER BY i.id DESC LIMIT 20

        Activity

        Hide
        Amir W added a comment - - edited

        Here's a workaround to avoid Doctrine's handling of LIMIT queries. Is this there recommended way or is there another?

        Before the $q->execute() part simply add these lines:

        $q->buildSqlQuery(false);
        $map = $q->getRootDeclaration();
        $table = $map['table'];
        $table->setAttribute(Doctrine_Core::ATTR_QUERY_LIMIT, Doctrine_Core::LIMIT_ROWS);

        Now the DQL is automatically translated to the following SQL:

        SELECT z.id AS z_id, z.producer_id AS zproducer_id, z.series_id AS zseries_id, z.issue_date AS zissue_date, p.id AS pid, p.width AS pwidth, p.height AS pheight, z2.id AS z2id, z2.lang AS z2lang, z2.name AS z2_name FROM zpc_phonecard z INNER JOIN picture p ON z.front_picture_id = p.id AND ((p.width > 125 AND p.width >= p.height)) LEFT JOIN zpc_phonecard_translation z2 ON z.id = z2.id AND (z2.lang = 0) WHERE ((z.front_picture_id > 0 AND z.state = 'Active') AND z.zpc_system_id = 4) ORDER BY z.id DESC LIMIT 20

        Show
        Amir W added a comment - - edited Here's a workaround to avoid Doctrine's handling of LIMIT queries. Is this there recommended way or is there another? Before the $q->execute() part simply add these lines: $q->buildSqlQuery(false); $map = $q->getRootDeclaration(); $table = $map ['table'] ; $table->setAttribute(Doctrine_Core::ATTR_QUERY_LIMIT, Doctrine_Core::LIMIT_ROWS); Now the DQL is automatically translated to the following SQL: SELECT z.id AS z_ id, z.producer_id AS z producer_id, z.series_id AS z series_id, z.issue_date AS z issue_date, p.id AS p id, p.width AS p width, p.height AS p height, z2.id AS z2 id, z2.lang AS z2 lang, z2.name AS z2 _name FROM zpc_phonecard z INNER JOIN picture p ON z.front_picture_id = p.id AND ((p.width > 125 AND p.width >= p.height)) LEFT JOIN zpc_phonecard_translation z2 ON z.id = z2.id AND (z2.lang = 0) WHERE ((z.front_picture_id > 0 AND z.state = 'Active') AND z.zpc_system_id = 4) ORDER BY z.id DESC LIMIT 20
        Hide
        Jonathan H. Wage added a comment -

        This is the dreaded limit subquery algorithm at work. I don't think we can do anything to patch/fix this in Doctrine 1.

        Show
        Jonathan H. Wage added a comment - This is the dreaded limit subquery algorithm at work. I don't think we can do anything to patch/fix this in Doctrine 1.
        Hide
        Amir W added a comment -

        As there is a workaround (as I've suggested above) you can obviously add a flag (via a function or any other way) allowing the user NOT to use the "dreaded limit subquery"...

        Show
        Amir W added a comment - As there is a workaround (as I've suggested above) you can obviously add a flag (via a function or any other way) allowing the user NOT to use the "dreaded limit subquery"...

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Amir W
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: