Database model (simplified):
class Document(Base):
__tablename__ = "document"
id = Column(Integer, primary_key=True)
...
@classmethod
def link(cls):
col = Column(Integer, ForeignKey(cls.id))
rel = relationship(cls, uselist=False, lazy='selectin', foreign_keys=[col])
return col, rel
class File(Base):
__tablename__ = "document_file"
id = Column(Integer, primary_key=True)
document_id = Column(Integer, ForeignKey(Document.id))
document = relationship( Document, back_populates="files" )
...
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
...
class B(Base):
__tablename__ = "b"
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey(A.id))
a = relationship(A, back_populates="a")
...
doc1_id, doc1 = Document.link()
doc2_id, doc2 = Document.link()
....
class C(Base):
__tablename__ = "c"
id = Column(Integer, primary_key=True)
b_id = Column(Integer, ForeignKey(B.id))
b = relationship(B, back_populates="a")
...
doc_id, doc = Document.link()
...
There is entity Document used in domain-specific entities. It has complex hierarchical structure with several one-to-many layers. It's fetched as a single root object (class A in the example), then lazy-loading of SQLAlchemy does its magic.
In a single one-to-many case SQLAlchemy detects it and loads whole list of objects in a single request to database. But in given case this optimization is not working: Document instances are fetched one-by-one. With 10k+ objects it becomes very slow.
Two ways to solve this:
Use
selectinloadpolicy for whole hierarchy. Drawback is I need a whole hierarchy after a check of several objects. Moreover, sometimes I can't use an additional query withpopulate_existingcommand because the objects are already modified.Use SQLAlchemy mapper data to scan for references to
Documentand fetch it to the dictionary with a single request. But I can't just assign fetched objects to properties managed by SQLAlchemy, so this technique is not uniform with ordinary access to objects.
Two questions:
Is there some another optimization technique?
Is there a more efficient solution in terms of database model architecture?