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

Ascending order - Click to sort in descending order
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?
Benjamin Eberlei made changes -
Field Original Value New Value
Priority Major [ 3 ] Critical [ 2 ]
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
Pascal Burkhard made changes -
Attachment trace.txt [ 11133 ]
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 -

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 ).
Pascal Burkhard made changes -
Attachment trace.txt [ 11134 ]
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 -

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.
Benjamin Eberlei made changes -
Link This issue is duplicated by DDC-1573 [ DDC-1573 ]
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 -

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 -

I found the issue.

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

Fixed

Show
Benjamin Eberlei added a comment - Fixed
Benjamin Eberlei made changes -
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 2.1.6 [ 10182 ]
Fix Version/s 2.2 [ 10157 ]
Resolution Fixed [ 1 ]
Benjamin Eberlei made changes -
Workflow jira [ 13250 ] jira-feedback [ 15139 ]
Benjamin Eberlei made changes -
Workflow jira-feedback [ 15139 ] jira-feedback2 [ 17003 ]
Benjamin Eberlei made changes -
Workflow jira-feedback2 [ 17003 ] jira-feedback3 [ 19256 ]
Miha Vrhovnik made changes -
Comment [ Ca I get a face palm :P ]
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
  • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-1526, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

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

Dates

  • Created:
    Updated:
    Resolved: