Search code examples
ormsqlalchemyslicenegative-integer

speed up sqlalchemy orm dynamic relationship slicing with negative indicies

I have the following SQLA models and relationships. I am logging a measurement for each channel every second, so there are lots of measurements in the DB.

class Channel( Model ) :
    __tablename__   = 'channel'
    id              = Column( Integer, primary_key=True )
    #! --- Relationships ---
    measurements    = relationship( 'Measurement', back_populates='channel', lazy='dynamic' )

class Measurement( Model ) :
    __tablename__   = 'measurement'
    id              = Column( Integer, primary_key=True )
    timestamp       = Column( DateTime, nullable=False )
    value           = Column( Float, nullable=False )
    #! --- Relationships ---
    channel         = relationship( 'Channel', back_populates='measurements', uselist=False )

If I want to get the latest measurement I can get it via ORM and slicing with a negative index.

channel.measurements[-1]

However, it is very very slow !!

I can do another filter the relationship query further with .filter() and .order_by() etc, to get what I want, but I like using the ORM (why have it otherwise?)

I noticed that if I slice with a positive index that it is fast (similar to explicit SQLA queries mentioned above).

channel.measurements[0]

I changed the relationship to keep measurements in reverse order, and that seems work in conjunction with using a zero index.

    measurements    = relationship( 'Measurement', back_populates='channel', lazy='dynamic', order_by='Measurement.id.desc()' )

So, why is negative index slicing so slow ??

Is it a bug in SQLAlchemy? I would have thought it would be smart enough to do the correct SQL to get only the latest item from the DB?

Is there something else I need to do to have the measurements sorted in natural order and use negative index slicing and get the same speed as the other methods ??

Solution

  • It seems the answer is that efficient slicing or realtionship collections with negative indicies is not supported in SQLA. In fact there seems to be some kludgy attempt in the code but is going to be removed from SQLA as it wasn't carefully thought out.

    https://github.com/sqlalchemy/sqlalchemy/issues/5605

    I have solved my issue by implementing a hybrid property that returns me the latest measurement, rather than slicing the relationship collection directly.

        @hybrid_property
    def latest_measurement( self ) -> float :
        """
        Hybrid property that returns the latest measurement for the channel.
        """
        measurement = self.measurements.order_by( Measurement.id.desc() ).first()
        return measurement