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.
