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?
--
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.
For more options, visit https://groups.google.com/d/optout.
