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. <http://pastebin.com/raw.php?i=ETNDAeAa> 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.
