Doctrine 2 - ORM

Unecessary queries with LEFT JOIN

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Critical Critical
  • Resolution: Fixed
  • Affects Version/s: 2.1.4
  • Fix Version/s: 2.1.6, 2.2
  • Component/s: None
  • Security Level: All
  • Labels:
    None
  • Environment:
    PHP 5.3.6

Description

After upgrading to 2.1.4 (from 2.1.2), the following dql started creating way more queries than necessary :
SELECT m, p, s, c, o
FROM FERMainBundle:Menu m
LEFT JOIN m.page p
LEFT JOIN m.section s
LEFT JOIN m.children c
LEFT JOIN s.position o
ORDER BY m.lft

Details to the code here:
http://pastie.textmate.org/private/z9gtgqe1odwenxcmudywqa
The model looks like that:
http://pastie.textmate.org/private/przxzfimsfyua02cxqcv9a

http://pastie.textmate.org/private/ob1jqiekv89e4xj9bq06q
First query is executed, it should in fact retrieve everything there is about the menu, but then it runs the second query for every menu element I have, generating a lot of queries that didn't occur before.

  1. Text File
    trace.txt
    20/Dec/11 11:30 AM
    5.94 MB
    Pascal Burkhard
  2. Text File
    trace.txt
    19/Dec/11 6:22 PM
    40 kB
    Pascal Burkhard

Issue Links

is duplicated by

Bug - A problem which impairs or prevents the functions of the product. DDC-1573 Simple relation hydratation not working properly

  • Major - Major loss of function.
  • Resolved - A resolution has been taken, and it is awaiting verification by reporter. From here issues are either reopened, or are closed.

Activity

Descending order - Click to sort in ascending order
Hide
Permalink
Benjamin Eberlei added a comment -

Fixed

Show
Benjamin Eberlei added a comment - Fixed
Hide
Permalink
Benjamin Eberlei added a comment -

I found the issue.

Show
Benjamin Eberlei added a comment - I found the issue.
Hide
Permalink
Pascal Burkhard added a comment -

Alright. I deactivated the Tree extension, but there are no changes in the number of queries.

Show
Pascal Burkhard added a comment - Alright. I deactivated the Tree extension, but there are no changes in the number of queries.
Hide
Permalink
Benjamin Eberlei added a comment -

Can you disable the nested set extension? the other ticket uses it too and i want to rule out that its the extensions fault.

Show
Benjamin Eberlei added a comment - Can you disable the nested set extension? the other ticket uses it too and i want to rule out that its the extensions fault.
Hide
Permalink
Pascal Burkhard added a comment -

The "complete" trace, i.e. the one that is 5.94 mb big is from after the upgrade to 2.1.5.

Show
Pascal Burkhard added a comment - The "complete" trace, i.e. the one that is 5.94 mb big is from after the upgrade to 2.1.5.
Hide
Permalink
Benjamin Eberlei added a comment -

Is the trace from before upgrade to 2.1.5 or after?

Show
Benjamin Eberlei added a comment - Is the trace from before upgrade to 2.1.5 or after?
Hide
Permalink
Pascal Burkhard added a comment -

Here the complete trace, started just before the first query. I'm sorry but I can't make heads or tails with that... I hope it can help you pinpoint the problem.

Please also note that I have update Doctrine ORM to 2.1.5 and there was a change in the number of "superfluous" queries done. I am now only left with additional queries to get the relations to "parent", cf the model ( http://pastie.textmate.org/private/przxzfimsfyua02cxqcv9a ).

Show
Pascal Burkhard added a comment - Here the complete trace, started just before the first query. I'm sorry but I can't make heads or tails with that... I hope it can help you pinpoint the problem. Please also note that I have update Doctrine ORM to 2.1.5 and there was a change in the number of "superfluous" queries done. I am now only left with additional queries to get the relations to "parent", cf the model ( http://pastie.textmate.org/private/przxzfimsfyua02cxqcv9a ).
Hide
Permalink
Benjamin Eberlei added a comment -

Hi Pascal, sorry but this is not enough. I need this query until all the other entities (or at least one) are n+1 joined.

Show
Benjamin Eberlei added a comment - Hi Pascal, sorry but this is not enough. I need this query until all the other entities (or at least one) are n+1 joined.
Hide
Permalink
Pascal Burkhard added a comment -

xdebug trace start just before I query the database

Show
Pascal Burkhard added a comment - xdebug trace start just before I query the database
Hide
Permalink
Benjamin Eberlei added a comment -

Can you profile where exactly the extra queries are executed using xdebug_start_trace? Directly during hydration or later in your code?

Show
Benjamin Eberlei added a comment - Can you profile where exactly the extra queries are executed using xdebug_start_trace? Directly during hydration or later in your code?

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Pascal Burkhard
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: