DDC-1526: Unecessary queries with LEFT JOIN #2158

Closed
doctrinebot opened this Issue Dec 9, 2011 · 13 comments

2 participants

@doctrinebot

Jira issue originally created by user nenuial:

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.

@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Comment created by nenuial:

xdebug trace start just before I query the database

@doctrinebot

Comment created by @beberlei:

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.

@doctrinebot

Comment created by nenuial:

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 ).

@doctrinebot

Comment created by @beberlei:

Is the trace from before upgrade to 2.1.5 or after?

@doctrinebot

Comment created by nenuial:

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

@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Comment created by nenuial:

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

@doctrinebot

Comment created by @beberlei:

I found the issue.

@doctrinebot

Comment created by @beberlei:

Fixed

@doctrinebot

Issue was closed with resolution "Fixed"

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.1.6 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment