1 answer
- 10-1
Louis,
The best way to improve this experience is for the organization to close out Submitted Forms and for you to query only active/open applications - possibly in smaller batches, not larger ones.
I believe the account you are working with has about 20,000 open/active applications which likely means they aren't managing the Submitted Forms statuses (i.e., closing out inactive submitted forms).
Here's a related Question: What is the recommended way to handle Submitted Form statuses?
Hope that helps,
Richard
- Louis Goldstein
Thanks Richard, but I would need to query closed applications as well, as we need to query across historical data as well as new applications (for example, looking for foster applicants who have never fostered). Yes, we have > 20k forms. I'll try using a smaller batch size as you recommend.
As for closing submitted forms, our workflow for at least some of the form types don't have a termination. We need the foster applications available for all of our active fosters, as well as the adoption applications should those adopters either not adopt (animal became unavailable) and come back later, or wish to adopt another animal in the future. That doesn't seem like a tenable way to address this issue for all scenarios and use cases.
Perhaps the query could be tuned? I'd imagine this is a join across at least a couple tables. Are the appropriate indexes in place? (I'm a software engineer... I'm actually downloading this data into a SQLite database for offline querying and processing.)
- Louis Goldstein
Looks like 1/10 the batch size takes more than 1/10 the time of the original, so that didn't really help. It also failed after the 15th batch. I'll have to add some retry logic.
Returned in 1477 ms. Retrieved 100 of 20148 records starting at 0 Returned in 8878 ms. Retrieved 100 of 20148 records starting at 100 Returned in 4636 ms. Retrieved 100 of 20148 records starting at 200 Returned in 6953 ms. Retrieved 100 of 20148 records starting at 300 Returned in 4686 ms. Retrieved 100 of 20148 records starting at 400 Returned in 9187 ms. Retrieved 100 of 20148 records starting at 500 Returned in 7634 ms. Retrieved 100 of 20148 records starting at 600 Returned in 7785 ms. Retrieved 100 of 20148 records starting at 700 Returned in 16455 ms. Retrieved 100 of 20148 records starting at 800 Returned in 8225 ms. Retrieved 100 of 20148 records starting at 900 Returned in 9220 ms. Retrieved 100 of 20148 records starting at 1000 Returned in 9634 ms. Retrieved 100 of 20148 records starting at 1100 Returned in 8228 ms. Retrieved 100 of 20148 records starting at 1200 Returned in 7114 ms. Retrieved 100 of 20148 records starting at 1300 Returned in 5476 ms. Retrieved 100 of 20148 records starting at 1400 Returned in 10529 ms. Retrieved 100 of 20148 records starting at 1500
- Louis Goldstein
Interestingly, without the
submittedformAnswers(and with a bunch of other fields), retrieving most batches of 1000 submittedforms take around 500ms. The answers are definitely what is slowing it down (I would expect some slow down, since it's retrieving considerably more data, but not the 80x+ we're seeing).
Add your comment...
Hi,
I'm trying to retrieve all of our submittedforms along with answers via the API:
{ "search": { "resultStart": 0, "resultLimit": 1000, "resultSort": null, "resultOrder": "asc", "filters": [], "filterProcessing": null, "fields": [ "submittedformID", "submittedformAnswers" ] }, "token": "REDACTED", "tokenHash": "REDACTED", "objectType": "submittedforms", "objectAction": "search" }With resultLimit of 1000, it takes about 40 seconds to return the first batch, which seems quite slow. If I increase the resultLimit to 2000 (to reduce the number of batches), the connection consistently fails after about 100 seconds. (Actual times are suspect due to small number of tries, but outcomes are consistent.) I'm guessing there's some timeout on your servers causing the failure?
Is there a way (on my end or yours) to increase the speed of retrieving this data?
Thanks,
Louis