Improve paging by changing the filter instead of using skip, if an order is specified

FraunhoferIOSB / FROST-Server

A Complete Server implementation of the OGC SensorThings API

https://fraunhoferiosb.github.io/FROST-Server/

GNU Lesser General Public License v3.0

199 stars 74 forks source link

Improve paging by changing the filter instead of using skip, if an order is specified #721

Open hylkevds opened 2 years ago

hylkevds commented 2 years ago

Skipping rows is slow, since each skipped row still needs to be fully calculated. When an orderby is used, paging can be implemented by changing the where (filter) instead. Rows skipped because of a filter do not need to be fully calculated and result in much faster queries.

hylkevds commented 2 years ago

The develop-2.0 branch now has this enhancement and it makes paging significantly faster. Some limitations:

Only works if there is an orderBy on the primary key in the list of oderBys. This can be added automatically with the alwaysOrderbyId setting.
Only works for path ordering, not for ordering by function.