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