Thanks for replying, but your response doesn't really help. Perhaps my post
wasn't clear...
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()
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 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] <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/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].
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/cd7b05b0-97ee-49e6-8159-2bd6aff34763%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
