Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Optionally translate "start" / "limit" to SQL #674

Open
andrus opened this issue Jun 5, 2024 · 0 comments
Open

Optionally translate "start" / "limit" to SQL #674

andrus opened this issue Jun 5, 2024 · 0 comments

Comments

@andrus
Copy link
Contributor

andrus commented Jun 5, 2024

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.

@andrus andrus changed the title Alt "start" / "limit" strategy - translate them to SQL Optionally translate "start" / "limit" to SQL Jun 5, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant