Performance issue with multiple table references to one

Viewed 44

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:

  1. Use selectinload policy 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 with populate_existing command because the objects are already modified.

  2. Use SQLAlchemy mapper data to scan for references to Document and 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:

  1. Is there some another optimization technique?

  2. Is there a more efficient solution in terms of database model architecture?

0 Answers
Related