Not-great performance for paged OData access of repeatgroup items

[brontolosone]

Potentially related: #1618

Follow along:

(if you want to run it yourself on the same data, grab your session token from the staging DB)

export CENTRAL_TOKEN="your-session-token-for-staging-here"

First, as a baseline of sorts, timing the retrieval of a single submission via OData (10 times):

for i in `seq 10`; do /usr/bin/time --format=%e curl --compressed --silent --show-error -H 'Accept: application/json, text/plain, */*' -H "Authorization: Bearer ${CENTRAL_TOKEN}" 'https://staging.getodk.cloud/v1/projects/112/forms/geotest.svc/Submissions?$top=1' > /dev/null; sleep 1; done
1.25
1.33
1.20
1.19
1.15
1.14
1.38
1.17
1.21
1.31

Not very fast, to start with.
Now, let's time the retrieval of the first repeatgroup item (plural "table") (10 times):

for i in `seq 10`; do /usr/bin/time --format=%e curl --compressed --silent --show-error -H 'Accept: application/json, text/plain, */*' -H "Authorization: Bearer ${CENTRAL_TOKEN}" 'https://staging.getodk.cloud/v1/projects/112/forms/geotest.svc/Submissions.plural?$top=1' > /dev/null; sleep 1; done
26.25
24.49
29.20
29.59
56.63
52.78
35.49
25.65
37.24
68.52

Not great! Mind the response body size is just 902 bytes. My intuition is that there's something suboptimal going on here 🤔

[matthew-white]

@sadiqkhoja: We could abort the query once we've reached the specified number of repeat group instances. Right now, the query continues on uninterrupted even after all the instances to be returned with the response have been identified/collected.

@brontolosone suggests that we persist the cardinality of repeat groups at ingestion time (e.g., submission X has Y instances of repeat group Z) so that we can determine which submissions to start and end with. That's something that we could work on after benchmarking is in place (#1610).

@brontolosone suggests using a server-side cursor, which lets you fetch a certain number of rows from the result set, then do something with it, then fetch the next part.

@lognaturel thinks that the whole thing is surprisingly slow. Like if you request an entire repeat group without pagination, it takes longer than expected. Does it just take some time to parse each submission, or is there something inefficient going on? (@sadiqkhoja thinks it's probably just the time needed to parse.)