On Wed, May 22, 2019, at 6:44 PM, bvbav wrote:
> Thanks for replying, but your response doesn't really help. Perhaps my post
> wasn't clear...
not really sure, I answered what I thought I was seeing, and having gone
through your new response, I am still seeing the same thing being asked, so
what we do is we start using code to answer the question rather than words.
that way there's no ambiguity.
>
> I'm aware the "dynamic" relationship returns a query, and not a collection.
> It's mainly used for large collections and when someone wants to perform
> filters, etc instead of getting the collection as a list.
>
> Because of the nature of dynamic, when I do that join (re-posted here), it
> emits a SELECT statement for every attribute in the relationship. Correct me
> if I'm wrong on this. So the join query is slow for extremely large amount.
>
> db.session.query(Address, Resident, Pet).filter(Address.id ==
> 1).join(Address.residents).join(Resident.pets).all()
So above, is "residents" the relationship with lazy="dynamic" configured?
Whether or not it is, "Address.residents" is at the class level. This returns a
SQL expression object. It does not invoke any kind of dynamic query behavior at
query time. That is, when the "all()" above has completed, there will be
exactly one "SELECT" being emitted. If that is not what you are seeing, then
there is something much more elaborate going on in your case like an event
listener. See Example 1 at the end of the email which illustrates this.
Now when we go beyond the call to all(), and we have a whole list of objects
that you now iterate through, and Address.residents is in fact the "dynamic"
relationship, and you access it at the instance level, it will run a new Query
all over again. This is the expected behavior if Address.residents is
lazy="dynamic" and there is no way to change that except to not use
lazy="dynamic".
So are we on the same page? Otherwise if you see something different we have to
clear that part up first.
> Here I'm basically getting all residents and pets that live at an address.
>
> Because it's slow, I thought switching to eager loading would work. I made
> two relationships to keep the dynamic an option - shouldn't have included as
> that caused confusion. So ignore "residents_dynamic" etc.
>
> But with the "selectin" loading, it resulted in 3x the number of queries
> while using that same join above.
So when you say 3x the number of queries, I don't know what we are multiplying
by three since the previous thing you describe is not what happens based on
your description, "SELECT IN" does not emit a query per object withn the scope
of the parent Query. You might be seeing 1. per object lazy (or dynamic?) loads
that aren't actually "selectin" loads, or 2. "selectin"loads that are a
secondary effect of each of the per-object loads in #1. In both cases the
problem is the same, that it seems you are seeing per-object loads going on
that aren't "selectin" loads.
Example 2 below illustrates what I think is the usage you are describing. At
the end, even though there are 30 objects there are only four queries. The main
one, then two SELECT IN queries for A->B->C, then another SELECT IN query that
I think is due to B->C, because it queries for A and B at the top level, which
is slightly more queries than we'd prefer but still nothing dramatic.
the way to get this worked out is to please provide a self-contained, short
proof of concept that creates the most minimal mapping possible, installs the
most minimal amount of test data possible into a sqlite:// memory database,
then illustrates the query you are running (this is also known as an MCVE).
Because I don't really know what you are seeing. Please confirm you are also on
the SQLAlchemy 1.3 series.
Example 1 - dynamic relationship has no effect on all():
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
bs = relationship("B", lazy="dynamic")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey("a.id"))
data = Column(String)
cs = relationship("C", lazy="dynamic")
class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
b_id = Column(ForeignKey("b.id"))
data = Column(String)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
A(bs=[B(cs=[C(), C()]), B(cs=[C(), C()]), B(cs=[C(), C()])]),
A(bs=[B(cs=[C(), C()]), B(cs=[C(), C()]), B(cs=[C(), C()])]),
A(bs=[B(cs=[C(), C()]), B(cs=[C(), C()]), B(cs=[C(), C()])]),
])
s.commit()
s.close()
s.query(A, B, C).filter(A.id == 1).join(A.bs).join(B.cs).all()
single query at the end after the INSERTs:
SELECT a.id AS a_id, a.data AS a_data, b.id AS b_id, b.a_id AS b_a_id, b.data
AS b_data, c.id AS c_id, c.b_id AS c_b_id, c.data AS c_data
FROM a JOIN b ON a.id = b.a_id JOIN c ON b.id = c.b_id
WHERE a.id = ?
Example 2: selectin is one query per class-level relationship, not per instance:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
bs_dyn = relationship("B", lazy="dynamic")
bs = relationship("B", lazy="selectin")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey("a.id"))
data = Column(String)
cs_dyn = relationship("C", lazy="dynamic")
cs = relationship("C", lazy="selectin")
class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
b_id = Column(ForeignKey("b.id"))
data = Column(String)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
A(bs=[B(cs=[C(), C()]), B(cs=[C(), C()]), B(cs=[C(), C()])]),
A(bs=[B(cs=[C(), C()]), B(cs=[C(), C()]), B(cs=[C(), C()])]),
A(bs=[B(cs=[C(), C()]), B(cs=[C(), C()]), B(cs=[C(), C()])]),
])
s.commit()
s.close()
s.query(A, B, C).filter(A.id == 1).join(A.bs).join(B.cs).all()
queries at the end which are, the s.query(), the two SELECT IN for A.bs ->
B.cs, then a SELECT IN for B->cs since it is specified at the top level as well:
SELECT a.id AS a_id, a.data AS a_data, b.id AS b_id, b.a_id AS b_a_id, b.data
AS b_data, c.id AS c_id, c.b_id AS c_b_id, c.data AS c_data
FROM a JOIN b ON a.id = b.a_id JOIN c ON b.id = c.b_id
WHERE a.id = ?
2019-05-22 19:39:25,156 INFO sqlalchemy.engine.base.Engine (1,)
2019-05-22 19:39:25,160 INFO sqlalchemy.engine.base.Engine SELECT b.a_id AS
b_a_id, b.id AS b_id, b.data AS b_data
FROM b
WHERE b.a_id IN (?) ORDER BY b.a_id
2019-05-22 19:39:25,160 INFO sqlalchemy.engine.base.Engine (1,)
2019-05-22 19:39:25,161 INFO sqlalchemy.engine.base.Engine SELECT c.b_id AS
c_b_id, c.id AS c_id, c.data AS c_data
FROM c
WHERE c.b_id IN (?, ?, ?) ORDER BY c.b_id
2019-05-22 19:39:25,162 INFO sqlalchemy.engine.base.Engine (1, 2, 3)
2019-05-22 19:39:25,163 INFO sqlalchemy.engine.base.Engine SELECT c.b_id AS
c_b_id, c.id AS c_id, c.data AS c_data
FROM c
WHERE c.b_id IN (?, ?, ?) ORDER BY c.b_id
2019-05-22 19:39:25,164 INFO sqlalchemy.engine.base.Engine (1, 2, 3)
>
>
>
>
> So I was wondering if anyone knows of a way to do that query so it is fast.
> Perhaps the 3x number of queries is because it eager loads Address.residents
> and Resident.pets, then since I'm also joining, it adds onto that? Or perhaps
> I need to change my query to work with "selectin"?
>
>
> On Wednesday, May 22, 2019 at 1:33:11 PM UTC-7, Mike Bayer wrote:
>>
>>
>> On Wed, May 22, 2019, at 3:56 PM, bvbav wrote:
>>> Hi
>>>
>>> I was wondering if I can get some advice on this...
>>>
>>> Let's say I have these models:
>>>
>>> class Pet(db.Model):
>>> id = db.Column(..., primary_key=True)
>>> name = db.Column(...)
>>>
>>>
>>> resident_pet = db.Table('resident_pet', db.Column('resident_id',
>>> db.Integer, db.ForeignKey('resident.id'), primary_key=True), db.Column
>>> `('pet_id', db.Integer, db.ForeignKey('pet.id'), primary_key=True)
>>>
>>> class Resident(db.Model):`
>>> `` id = db.Column(..., primary_key=True)
>>> name = db.Column(...)``
>>> `````` pets``` = db.relationship('Resident', secondary=resident_pet,
>>> lazy='dynamic')`
``
>>> ``````resident_address``` = db.Table('`````````resident_address```',
>>> db.Column('resident_id', db.Integer, db.ForeignKey('resident.id'),
>>> primary_key=True), db.Column
>>> `('address_id', db.Integer, db.ForeignKey('address.id'), primary_key=True)
>>>
>>>
>>> class Address(db.Model):
>>> ...
>>> residents = ````````````````db.relationship('Resident',
>>> secondary=resident_address, lazy='dynamic')``````````
>>>
>>> Now I want to query tables Address, Resident, Pet like this:
>>>
>>> db.session.query(Address, Resident, Pet).filter(Address.id ==
>>> 1).join(Address.residents).join(Resident.pets).all()
>>>
>>> Now, let's say hypothetically an address (represents a home) can have a
>>> large number of residents (e.g 10,000+), and each resident can have the
>>> same number of pets (e.g 10,000+). This is the reason why I used
>>> lazy='dynamic', so I can do quick filters etc. But this causes a problem
>>> when I do joins like the one above. Because with the dynamic loading,
>>> individual SELECT queries will be emitted for every pet for every resident
>>> which can result in a low performance? Is my assumption correct?
>>>
>>> I thought about switching to 'selectin' while keeping 'dynamic', so there
>>> would be two relationships
>>>
>>> `````class Address(db.Model):
>>> ...`````
>>> ``````````` residents = ````````````````db.relationship('Resident',
>>> secondary=resident_address, lazy='selectin')
```````````` residents_dynamic = ````````````````db.relationship('Resident',
secondary=resident_address, lazy='dynamic')```````````
>>>
>>> However, doing this (while keeping the same JOIN query above, results in a
>>> lot more queries and take a longer amount of time to finish. I also figured
>>> since I'm querying everything, there's no point in eager loading
>>> Resident.pets, because it's not like I'm getting one instance of Resident
>>> and expect to have the pets collection already there.
>>>
>>> How can I get better performance for this kind of join?
>>
>> the mechanism of "dynamic" loading is that your ORM instance contains a link
>> to a special Query object, and it does not store any Python list of items.
>> This is entirely different from what relationship does in all other cases,
>> which is that your ORM instance has a Python list, set, or other kind of
>> collection that simply stores the related objects in memory once loaded. So
>> it is not possible for a single relationship to perfom in both a "dynamic"
>> as well as a fixed "eager load" style of use.
>>
>> Therefore, for normal use of your objects when you don't need to add
>> additional filter criteria, you should use the "residents" relatoinship that
>> does not use "dynamic" loading. Only when you need to make a special SQL
>> query that is essentially Address.residents + extra SQL filtering, would the
>> "residents_dynamic" relationship be used, as it by definition has to query
>> the database on each use (else it wouldn't be "dynamic"). hope this helps.
>>
>>
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>> description.
>>> ---
>>> 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 sqlal...@googlegroups.com.
>>> To post to this group, send email to sqlal...@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/ab7a84b6-225a-40ec-8615-1ecfe7cf4ce9%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/ab7a84b6-225a-40ec-8615-1ecfe7cf4ce9%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/cd7b05b0-97ee-49e6-8159-2bd6aff34763%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/cd7b05b0-97ee-49e6-8159-2bd6aff34763%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/0661881b-a96a-4c83-ad63-4ff10556a60e%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
