OK, keep going with the profiling, whats the total number of rows you are
seeing with the joined load, 5000 total ? sometimes a join is actually
scrolling through many more rows than that, duplicate rows in the case of a
cartesian product. echo='debug' would show that. Or there could be very
large textual fields in rows that are being fetched repeatedly (in which case
subqueryload() might be better). There's a lot of reasons this could happen.
On Sep 12, 2014, at 3:16 AM, tatütata Okay <rogersher...@googlemail.com> wrote:
> Thats what I did before, activating echo=True shows me correct query too.
> This joinedload takes 1.3 seconds for this 5000 entries.
> And only 4 queries in general for complete side load. Adding one more
> joinedload ends up in 2 seconds, thats way too much for 1700 customers and 5
> k bought items
>
> On Thu, Sep 11, 2014 at 10:12 PM, Simon King <si...@simonking.org.uk> wrote:
> The query that you are doing:
>
> customerorders = sqlsession.query(Customerorder)\
> .join(Itemsbought.order)\
> .all()
>
> probably doesn't do what you intend. In particular, it doesn't populate the
> Customerorder.itemsbought collection. So when you iterate over customerorders
> and access the "itemsbought" property, it issues a new query for each order.
>
> Try starting with something like this:
>
> customerorders = (sqlsession.query(Customerorder)
> .options(saorm.joinedload("itemsbought"))
> .all())
>
> Hope that helps,
>
> Simon
>
> On 11 Sep 2014, at 20:54, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
> > 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.
