Slow performance compared to sqlalchemy using sqlmodel for performing group_by operation on a large table

[YusufTyer]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

---------------------------------------------------------
sqlmodel example
---------------------------------------------------------
from sqlmodel import SQLModel, Field
from typing import Optional

class Base(SQLModel):
    # dimensions
    Year: int = Field(default=None, primary_key=True)
    Month: str = Field(default=None)
    FinancialPeriod: str = Field(default=None)
    FinancialYear: int
    FinancialMonth: int = Field(default=None)
    SubRegionName: str = Field(default=None)
    FacilityName: str = Field(default=None)

    # metrics
    SelfUtilisation: Optional[float] = Field(default=None)
    SelfOpportunity: float = Field(default=None)
    GroupUtilisation: float = Field(default=None)
    GroupOpportunity: float = Field(default=None)
    FormularySavings: float = Field(default=None)
    FormularyQty: float = Field(default=None)
    NonFormularyQty: float = Field(default=None)
    BestCostFormularyQty: float = Field(default=None)
    BestCostNonFormularyQty: float = Field(default=None)
    FormularyOpportunity: float = Field(default=None)
    BestCostFormularyOpportunity: float = Field(default=None)
    SingleChoiceFormularyQty: float = Field(default=None)
    SingleChoiceNonFormularyQty: float = Field(default=None)

class SpecialityDRG(Base, table=True):
    __tablename__ = 'Agg_RegSpecDRG'
    __table_args__ = {'schema': 'model'}
    
    Speciality: str = Field(default=None)
    ItemGroup: str = Field(default=None)
    ItemComposition: str = Field(default=None)
    ItemDescription: str = Field(default=None)
    DRGDescription: str = Field(default=None)

    depth: int = Field(default=4)


from sqlmodel import Session, func

# Insert your engine here
session = Session(ENGINE)

query = (
    session.query(
        SpecialityDRG.DRGDescription,
        SpecialityDRG.ItemGroup,
        SpecialityDRG.ItemComposition,
        func.sum(SpecialityDRG.SelfUtilisation).label('SelfUtilisation'),
        func.sum(SpecialityDRG.SelfOpportunity).label('SelfOpportunity')
    )
    .filter(
        SpecialityDRG.FinancialYear == 2023,
        SpecialityDRG.SubRegionName == 'Inland South',
        SpecialityDRG.FacilityName == 'Life Flora Hospital',
        SpecialityDRG.Speciality == 'Orthopaedic Surgeon',
    )
    .group_by(SpecialityDRG.DRGDescription, SpecialityDRG.ItemGroup, SpecialityDRG.ItemComposition)
)

query.all()


---------------------------------------------------------
sqlalchemy example
---------------------------------------------------------
from sqlalchemy import Column, Integer, String, Numeric
from sqlalchemy.orm import DeclarativeBase
from typing import Union


# %% Create a session
class Base(DeclarativeBase):
    # dimensions
    Year = Column("Year", Integer, nullable=False, primary_key=True)
    Month = Column("Month", String(3))
    FinancialPeriod = Column("FinancialPeriod", String(7), nullable=False)
    FinancialYear = Column("FinancialYear", Integer)
    FinancialMonth = Column("FinancialMonth", Integer)
    SubRegionName = Column("SubRegionName", String(200), nullable=False)
    FacilityName = Column("FacilityName", String(100), nullable=False)

    # metrics
    SelfUtilisation = Column("SelfUtilisation", Numeric(38, 6))
    SelfOpportunity = Column("SelfOpportunity", Numeric(38, 6))
    GroupUtilisation = Column("GroupUtilisation", Numeric(38, 6))
    GroupOpportunity = Column("GroupOpportunity", Numeric(38, 6))
    FormularySavings = Column("FormularySavings", Numeric(38, 6))
    FormularyQty = Column("FormularyQty", Numeric(38, 6))
    NonFormularyQty = Column("NonFormularyQty", Numeric(38, 6))
    BestCostFormularyQty = Column("BestCostFormularyQty", Numeric(38, 6))
    BestCostNonFormularyQty = Column("BestCostNonFormularyQty", Numeric(38, 6))
    FormularyOpportunity = Column("FormularyOpportunity", Numeric(38, 6))
    BestCostFormularyOpportunity = Column("BestCostFormularyOpportunity", Numeric(38, 6))
    SingleChoiceFormularyQty = Column("SingleChoiceFormularyQty", Numeric(38, 6))
    SingleChoiceNonFormularyQty = Column("SingleChoiceNonFormularyQty", Numeric(38, 6))
    SingleChoiceFormularyOpportunity = Column("SingleChoiceFormularyOpportunity", Numeric(38, 6))

    # depth attribute with a default value
    depth: int = 0

class SpecialityDRG(Base):
    __tablename__ = 'Agg_RegSpecDRG'
    __table_args__ = {'schema': 'model'}
    depth = 4
    
    Speciality = Column("Speciality", String(50), nullable=False)
    ItemGroup = Column("ItemGroup", String(8), nullable=False)
    ItemComposition = Column("ItemComposition", String(150))
    ItemDescription = Column("ItemDescription", String(100))
    DRGDescription = Column("DRGDescription", String(250))


from sqlalchemy.orm import Session
from sqlalchemy import func

session = Session(ENGINE)

query = (
    session.query(
        SpecialityDRG.DRGDescription,
        SpecialityDRG.ItemGroup,
        SpecialityDRG.ItemComposition,
        func.sum(SpecialityDRG.SelfUtilisation).label('SelfUtilisation'),
        func.sum(SpecialityDRG.SelfOpportunity).label('SelfOpportunity')
    )
    .filter(
        SpecialityDRG.FinancialYear == 2023,
        SpecialityDRG.SubRegionName == 'Inland South',
        SpecialityDRG.FacilityName == 'Life Flora Hospital',
        SpecialityDRG.Speciality == 'Orthopaedic Surgeon',
    )
    .group_by(SpecialityDRG.DRGDescription, SpecialityDRG.ItemGroup, SpecialityDRG.ItemComposition)
)

query.all()

Description

The table I am querying from is a very large table containing millions of rows. I cannot provide access to the data within, but the fields documented in the classes are accurate.

There was no sqlmodel documentation available on how best to use a group by operation, so I used the sqlalchemy way of producing a group by query. The query executed to completion after 19s.

I then applied the sqlalchemy ORM to my tables and produced the exact same query. The execution of this table took just 1 second.

Please help me understand if I have made a mistake somewhere in defining my data model, or if there is a better way to perform the group by operation using sqlmodel. I don't believe that the issue would be with the data itself since the query ran very quick using sqlalchemy.

PS: This is my first time posting on GitHub so apologies if the format of the question was not written ideally.

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.9

Additional Context

No response

[chriscarrollsmith]

sqlmodel is indeed very slow compared to sqlalchemy. Probably because it's doing a lot of Pydantic operations alongside the sqlalchemy ones. This is definitely a library that prioritizes developer experience over speed.

[FilipeMarch]

@YusufTyer what you ended up doing?
Are you still using SQLModel?

[YusufTyer]

I ended up using the ORM to model the tables I had, and used it to generate my queries to avoid using raw SQL in python.

But when it came to executing the query, I instead used an ENGINE connection to execute the query rather than the ORM session. I created a function to convert the ORM query to raw SQL.

read_sql_query(text(pretty_print_query(query)), ENGINE)

Perhaps not the best workaround, but it worked for me.