Currently, start and limit parameters are not translated to SQL. Instead, they result in a "paginated" query that only fetches object ids (to save memory), and then reading an appropriate "page" from the result. The main reason we can't pass start and limit to SQL is that sometimes pagination is combined with Java-side filtering of the result, and hence can not be fully described via SQL.
So the existing strategy works great up to "medium-large" datasets (say 100K-5M of rows with int/long ids), but will invariably result in OutOfMemory errors on larger datasets and/or services with many parallel requests.
An alternative strategy that we want to implement here, is to include START / LIMIT in SQL. This will require at least 2 fetches (the main data with START / LIMIT, and overall COUNT). It may give incorrect counts and misaligned pages if Java-side filtering is in effect. So it has to be optional, and turned on explicitly by the users based on their knowledge of the filtering of a specific endpoint.
Currently,
startandlimitparameters are not translated to SQL. Instead, they result in a "paginated" query that only fetches object ids (to save memory), and then reading an appropriate "page" from the result. The main reason we can't passstartandlimitto SQL is that sometimes pagination is combined with Java-side filtering of the result, and hence can not be fully described via SQL.So the existing strategy works great up to "medium-large" datasets (say 100K-5M of rows with int/long ids), but will invariably result in OutOfMemory errors on larger datasets and/or services with many parallel requests.
An alternative strategy that we want to implement here, is to include START / LIMIT in SQL. This will require at least 2 fetches (the main data with START / LIMIT, and overall COUNT). It may give incorrect counts and misaligned pages if Java-side filtering is in effect. So it has to be optional, and turned on explicitly by the users based on their knowledge of the filtering of a specific endpoint.