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.
