search

jlu-ilr-hydro / odmf

Observatory Data Management Framework
MIT License
4 stars 5 forks source link

Timeseries.maxrecordid can be veeeery slow #99

Closed philippkraft closed 3 years ago

philippkraft commented 3 years ago

https://github.com/jlu-ilr-hydro/odmf/blob/68d4fa8de1db0c5783b066d0570e459b7c4d2752/odmf/db/dataset.py#L415-L418

Eg for ecuador/dataset/10341

d = ds[10341]

Not faster:

d.records.order_by(db.Record.id.desc()).limit(1).scalar()

Much faster:

def maxrecordid(self):
    size = d.records.count()  # 1.7ms
    return next(d.records.order_by(db.Record.id).offset(size - 1).values('id'))[0]   # 0.7ms
philippkraft commented 3 years ago

Test with https://fb09-pasig.umwelt.uni-giessen.de/schwingbach/dataset/5000

d = ds[5000]

30 seconds

d.records.order_by(db.Record.id.desc()).limit(1).scalar()

1 second

session.query(sql.func.max(db.Record.id)).filter_by(_dataset=d.id).scalar() or 0

16.5 ms

size = d.records.count() 
maxid = next(d.records.order_by(db.Record.id).offset(size - 1).values('id'))[0]