I have a project to track status of servers in my environment where my models are set up generally like the below, where each step down is a one to many relationship except server <-> version which is many to many.
environment (model)
farm (model)
server_version (association as db.Table)
server (model)
version (model)
Currently I am naively getting the data to populate a dashboard using db.session.query(Environment).all()
but using SQLALCHEMY_ECHO=True
I have found that the query this call generates is ridiculous, it overran my terminal back-scroll of 10,000 lines. It appears to me that .all()
is essentially recursively walking through the entire database to resolve all of the back_populates
for each individual record. Right now I only have like 800 total rows in the database for testing, but it seems like this would be horribly unperformant if the DB had thousands or millions of rows.
Should I avoid using .all()
as I have above and create explicit joins as needed or is the above all expected, fine, and just how the ORM works under the hood?