Re: [sqlalchemy] order_by(None) for joinedload subclause queries?

[Adam Feuer]

On Tue, Dec 30, 2014 at 8:24 PM, Michael Bayer 
wrote:

>  The first two parts, writing the joins and orderings and potentially
> aliasing them (or not), is up to you in that case, so you retain full
> control over how the query is rendered.
>
> Michael,

Cool, thanks for the explanation. That sounds like what we want. I'll try
it tomorrow.

cheers
adam
-- 
Adam Feuer
a...@cookbrite.com

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] order_by(None) for joinedload subclause queries?

[Michael Bayer]

Adam Feuer  wrote:

> On Tue, Dec 30, 2014 at 4:09 PM, Michael Bayer  > wrote:
> you either need to take order_by off the relationship(), probably the best 
> idea here if ordering isn’t important, or otherwise skip joinedload(), write 
> out the joins yourself and use contains_eager() 
> (http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_relationships.html?highlight=contains_eager#contains-eager
>  
> ).
> 
> Michael,
> 
> Thanks for the ideas, we'll try them. Not sure I can remove the order_by 
> relationship because we need the order other places... if I remove that, I 
> will have to add sorting in each of the other places. But it might be worth 
> it.
> 
> I think the manual joins and contains_eager may be better for us. Will that 
> work because the manual joins and contains_eager doesn't automatically 
> trigger adding the ORDER BY clauses?

joinedload() is sort of a macro that creates the joins and other modifications 
to the query (such as ORDER BY the relationship), applies aliases to each of 
those parts so that there’s no chance of them conflicting with anything on the 
query, and then routes the columns from those extra FROM clauses into 
collections and related objects.contains_eager() does just the very last 
part of that.The first two parts, writing the joins and orderings and 
potentially aliasing them (or not), is up to you in that case, so you retain 
full control over how the query is rendered.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] order_by(None) for joinedload subclause queries?

[Adam Feuer]

On Tue, Dec 30, 2014 at 4:09 PM, Michael Bayer 
wrote:

> you either need to take order_by off the relationship(), probably the best
> idea here if ordering isn’t important, or otherwise skip joinedload(),
> write out the joins yourself and use contains_eager() (
> http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_relationships.html?highlight=contains_eager#contains-eager
> ).
>

Michael,

Thanks for the ideas, we'll try them. Not sure I can remove the order_by
relationship because we need the order other places... if I remove that, I
will have to add sorting in each of the other places. But it might be worth
it.

I think the manual joins and contains_eager may be better for us. Will that
work because the manual joins and contains_eager doesn't automatically
trigger adding the ORDER BY clauses?

cheers
adam
-- 
Adam Feuer
a...@cookbrite.com

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] order_by(None) for joinedload subclause queries?

[Michael Bayer]

Adam Feuer  wrote:

> Hi,
> 
> We are using SQLAlchemy 0.9.8 on Python 2.7.7 and Postgres 9.3.
> 
> We have a query that uses joinedloads to fully populate some Recipe objects 
> using a single query. The query creates a large SQL statement that takes 20 
> seconds to execute - too long. Here's the rendered SQL statement on Pastebin. 
>  
> 
> The rendered SQL has an ORDER BY clause that Postgres explain says is the 
> source of 99% of the time spent on this query. This appears to come from the 
> relationship in the ORM model, which has an order_by clause.
> 
> However, we don't care about the order the results are returned for this 
> query - we only care about the order when looking at a single object. If I 
> remove the ORDER BY clause at the end of the rendered SQL statement, the 
> query executes in less than a second - perfect.
> 
> We tried using .order_by(None) on the query, but that seems to have no 
> effect. The ORDER BY seems to be related to the joinedloads, because if 
> change the joinedloads to lazyloads, they go away. But we need the 
> joinedloads for speed.
> 
> How can I get SQLAlchemy to omit the ORDER BY clauses? 

you either need to take order_by off the relationship(), probably the best idea 
here if ordering isn’t important, or otherwise skip joinedload(), write out the 
joins yourself and use contains_eager() 
(http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_relationships.html?highlight=contains_eager#contains-eager).




> 
> 
> 
> 
> FYI, here's the query:
> 
> missing_recipes = 
> cls.query(session).filter(Recipe.id.in_(missing_recipe_ids)) if 
> missing_recipe_ids else []
> 
> Here's an excerpt from the ORM class:
> 
> class Recipe(Base, TransactionalIdMixin, TableCacheMixin, 
> TableCreatedModifiedMixin):
> __tablename__ = 'recipes'
>   authors = relationship('RecipeAuthor', 
> cascade=OrmCommonClass.OwnedChildCascadeOptions,
>single_parent=True,
>lazy='joined', order_by='RecipeAuthor.order', 
> backref='recipe')
> scanned_photos = relationship(ScannedPhoto, backref='recipe', 
> order_by="ScannedPhoto.position")
> utensils = relationship(CookingUtensil, secondary=lambda: 
> recipe_cooking_utensils_table)
> utensil_labels = association_proxy('utensils', 'name')
> 
> Our query() method looks something like this (some more joinedloads omitted):
> 
> @classmethod
> def query(cls, session):
> query = query.options(
> 
> joinedload(cls.ingredients).joinedload(RecipeIngredient.ingredient),
> joinedload(cls.instructions),
> joinedload(cls.scanned_photos),
> joinedload(cls.tags),
> joinedload(cls.authors),
> )
> 
> 
> cheers
> adam
> -- 
> Adam Feuer 
> a...@cookbrite.com 
> 
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com 
> .
> To post to this group, send email to sqlalchemy@googlegroups.com 
> .
> Visit this group at http://groups.google.com/group/sqlalchemy 
> .
> For more options, visit https://groups.google.com/d/optout 
> .

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] order_by(None) for joinedload subclause queries?

[Adam Feuer]

Hi,

We are using SQLAlchemy 0.9.8 on Python 2.7.7 and Postgres 9.3.

We have a query that uses joinedloads to fully populate some Recipe objects 
using a single query. The query creates a large SQL statement that takes 20 
seconds to execute - too long. Here's the rendered SQL statement on 
Pastebin.  

The rendered SQL has an ORDER BY clause that Postgres explain says is the 
source of 99% of the time spent on this query. This appears to come from 
the relationship in the ORM model, which has an order_by clause.

However, we don't care about the order the results are returned for this 
query - we only care about the order when looking at a single object. If I 
remove the ORDER BY clause at the end of the rendered SQL statement, the 
query executes in less than a second - perfect.

We tried using .order_by(None) on the query, but that seems to have no 
effect. The ORDER BY seems to be related to the joinedloads, because if 
change the joinedloads to lazyloads, they go away. But we need the 
joinedloads for speed.

How can I get SQLAlchemy to omit the ORDER BY clauses? 




FYI, here's the query:

missing_recipes = 
cls.query(session).filter(Recipe.id.in_(missing_recipe_ids)) if 
missing_recipe_ids else []

Here's an excerpt from the ORM class:

class Recipe(Base, TransactionalIdMixin, TableCacheMixin, 
TableCreatedModifiedMixin):
__tablename__ = 'recipes'
  authors = relationship('RecipeAuthor', 
cascade=OrmCommonClass.OwnedChildCascadeOptions,
   single_parent=True,
   lazy='joined', order_by='RecipeAuthor.order', 
backref='recipe')
scanned_photos = relationship(ScannedPhoto, backref='recipe', 
order_by="ScannedPhoto.position")
utensils = relationship(CookingUtensil, secondary=lambda: 
recipe_cooking_utensils_table)
utensil_labels = association_proxy('utensils', 'name')

Our query() method looks something like this (some more joinedloads 
omitted):

@classmethod
def query(cls, session):
query = query.options(

joinedload(cls.ingredients).joinedload(RecipeIngredient.ingredient),
joinedload(cls.instructions),
joinedload(cls.scanned_photos),
joinedload(cls.tags),
joinedload(cls.authors),
)


cheers
adam
-- 
Adam Feuer 
a...@cookbrite.com

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.