Ok, so yes I see those queries for the two examples you posted.
For my original model (first post), with dynamic it takes about 6sec to
load ~70,000 amount of data, going by the first post then this means all
residents and pets from an address. Which I would like to speed up.
So I tried the eager load selectin (because I thought dynamic affects all()
- which you mentioned it does not), and I get the query from example 2. But
this one is slower because there are more queries - I used a profiler
(sqltap) and it shows the query count as being 3 times as much.
So ideally, I'd like to somehow query/join to get those ~70,000 records
within a faster amount of time, rather than 6 secs. Is there a proper way
to do this with eager loading or perhaps another way?
On Wednesday, May 22, 2019 at 4:49:58 PM UTC-7, Mike Bayer wrote:
>
>
>
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]
> <javascript:>.
> 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 [email protected].
To post to this group, send email to [email protected].
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/9824f5b0-b4f2-463a-b6ff-256aef5f6b49%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
