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