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 <[email protected]> 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 <[email protected]> > 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 <[email protected]> > 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 <[email protected]> >> 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 [email protected]. >> To post to this group, send email to [email protected]. >> 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 [email protected]. >> To post to this group, send email to [email protected]. >> 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
