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 <[email protected]> wrote:
> then it sounds like you have lazy loading happening, check echo=True
>
>
> On Sep 11, 2014, at 3:12 PM, tatütata Okay <[email protected]>
> 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 <[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.
