The query that you are doing: customerorders = sqlsession.query(Customerorder)\ .join(Itemsbought.order)\ .all()
probably doesn't do what you intend. In particular, it doesn't populate the Customerorder.itemsbought collection. So when you iterate over customerorders and access the "itemsbought" property, it issues a new query for each order. Try starting with something like this: customerorders = (sqlsession.query(Customerorder) .options(saorm.joinedload("itemsbought")) .all()) Hope that helps, Simon On 11 Sep 2014, at 20:54, Michael Bayer <mike...@zzzcomputing.com> wrote: > then it sounds like you have lazy loading happening, check echo=True > > > On Sep 11, 2014, at 3:12 PM, tatütata Okay <rogersher...@googlemail.com> > wrote: > >> Okay will try it. Maybe you got it wrong. I have only 5 k rows >> >> On Thu, Sep 11, 2014 at 8:55 PM, Michael Bayer <mike...@zzzcomputing.com> >> wrote: >> three joins over 100K rows is going to be very slow, two seconds seems in >> the ballpark. EXPLAIN will show if there are any table scans taking place. >> >> as far as why the relationship is faster, it might be doing something >> different, check the echo=True output. >> >> On Sep 11, 2014, at 2:35 PM, tatütata Okay <rogersher...@googlemail.com> >> wrote: >> >>> Yeah that would have been my next steps. I hoped more for some hints if the >>> setup is correct more if there are issues with the relationship >>> configuration? I think speed related things come up when there are over >>> 100000 entries and more? We speak here about 5000, thats why I think I did >>> a mistake on the configuration of the relation patterns? >>> >>> >>> On Thu, Sep 11, 2014 at 8:12 PM, Michael Bayer <mike...@zzzcomputing.com> >>> wrote: >>> for some tips on isolating where the speed issue is, follow the steps at: >>> http://docs.sqlalchemy.org/en/latest/faq.html#how-can-i-profile-a-sqlalchemy-powered-application >>> >>> for too many joins, you also want to look into EXPLAIN PLAN. >>> >>> >>> >>> On Sep 11, 2014, at 12:57 PM, tatütata Okay <rogersher...@googlemail.com> >>> wrote: >>> >>>> Hey, >>>> I got some shop tables like: >>>> customerorders, itemsbought, products whole query with three joins takes >>>> nothing on command line sql. When I trying to achieve same thing with ORM >>>> and my relationship it takes 1 second, joining all three 3 tables 2 >>>> seconds? >>>> >>>> May I come first up with my table and mapper definitions: >>>> >>>> class Products(Base): >>>> __tablename__ = 'products' >>>> id = Column(Integer, primary_key=True) >>>> productname = Column(Unicode(255)) >>>> productname_en =Column(Unicode(255), default = u'nice product') >>>> path = Column(Unicode(300)) >>>> pic = Column(Unicode(300), unique=True) >>>> bigpic = Column(Unicode(300), unique=True) >>>> sold = Column(Boolean) >>>> price = Column(Integer) >>>> locked = Column(Boolean) >>>> sort = Column(Integer) >>>> catid = Column(Integer) >>>> sizeid = Column(Integer, ForeignKey('productsizes.sizeid')) >>>> productsizes = relationship("ProductSizes", backref="products", >>>> lazy='joined') >>>> itemsbought = relationship("Itemsbought", backref="products") >>>> >>>> def __init__(self, productname, path, pic, bigpic, sold, price, >>>> sizeid, locked, sort, catid): >>>> self.productname = productname >>>> self.path = path >>>> self.pic = pic >>>> self.bigpic = bigpic >>>> self.sold = sold >>>> self.price = price >>>> self.sizeid = sizeid >>>> self.locked = locked >>>> self.sort = sort >>>> self.catid = catid >>>> >>>> class Customerorder(Base): >>>> __tablename__ = 'customerorders' >>>> id = Column(Integer, primary_key=True) >>>> ip = Column(Unicode(40)) >>>> comment = Column(Unicode(1000)) >>>> plz = Column(Unicode(30)) >>>> name = Column(Unicode(100)) >>>> street = Column(Unicode(200)) >>>> ort = Column(Unicode(100)) >>>> date = Column(DateTime, default = func.now()) >>>> money_received = Column(Boolean) >>>> shipped = Column(Boolean) >>>> >>>> def __init__(self, comment, ip, street, plz, ort, name, >>>> money_received): >>>> self.ip = ip >>>> self.customerid = customerid >>>> self.comment = comment >>>> self.street = street >>>> self.plz = plz >>>> self.ort = ort >>>> self.name = name >>>> self.money_received = money_received >>>> self.shipped = shipped >>>> >>>> class Itemsbought(Base): >>>> __tablename__ = 'itemsbought' >>>> id = Column(Integer, primary_key=True) >>>> orderid = Column(Integer, ForeignKey('customerorders.id')) >>>> order = relationship('Customerorder', backref=backref("itemsbought")) >>>> productid = Column(Integer, default = 0) >>>> >>>> def __init__(self, productid, orderid): >>>> self.productid = productid >>>> self.orderid = orderid >>>> >>>> >>>> I try to query only Customerorder and Itemsbought without having those >>>> relations to products: >>>> customerorders = sqlsession.query(Customerorder)\ >>>> .join(Itemsbought.order)\ >>>> .all() >>>> or with joinedload, outerjoins all the same thing. log shows me always >>>> sqlalchemy makes left outer joins. >>>> Trying to use this data in loop ends up in 1700 queries: >>>> >>>> for order in customerorders: >>>> print order.name, >>>> for item in order.itemsbought: >>>> print item.productid >>>> >>>> I managed to get all this correct data with sizes and products in 2,5 s >>>> with joinedload but this is a way too long for 5 k entries in itemsbought >>>> and 1680 orders. >>>> >>>> Can someone point to what I am doin wrong? And whats the best way to get >>>> all orders in an clearly arranged way with products, price, sizes etc. >>>> >>>> Cheers >>>> -- 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.