In TDS we have the use case of retrieving the complete data of a <PROJECT_NAME>-Testsuite prior to validation. This include the pure <PROJECT_NAME>-documents as well as the TDS data which is stored in attachments for both the testsuite and all its testcases.
I noticed a bottleneck when retrieving the data which is the LOAD_ATTACHMENT_URL endpoint.
The following trace shows the result of a testsuite containing 188 testcases overall and a combined total amount of 376 attachments.
The expanded “compose attachment urls” (which takes ~15s) is one REST-Call to <PROJECT_NAME> containing 376 LOAD_ATTACHMENT_URL RPC requests
Is the amount of time used to compute these URL expected? Am I putting to many JSON-RPC request in one call? Any ideas how to improve this?
Hi @markus-async-dune
Here’s the formatted markdown response:
I’m sorry to hear about this bottleneck.
The 15-second delay is caused by one REST call containing 376 individual RPC requests, each performing ~5-6 sequential database operations:
Per RPC Breakdown
- Permission checks (1-3 queries, may use cache): Document access and model rights validation
- Attachment header load (1 query): Retrieve metadata
- URL generation (1-2 queries): Query Content Store for storage path and validate metadata
Total: ~2,000+ sequential database queries
What is the use case on your side? Do you actually need to download this many files at the same time? Could you try loading only the thumbnail URLs instead?
Recommendations
-
Reduce the number of attachments per request by implementing client-side pagination
-
OR Create an A12 ticket to implement batch loading support for LOAD_ATTACHMENT_URLS
We’re happy to adapt this feature on the DataServices to support your use case better. The batch loading implementation would benefit all clients dealing with multiple attachments.
Hello @mpapenbr, I am a member of the Discourse team. I can see that there was a new response to your question on Performance of LOAD_ATTACHMENT_URL. Do you find it helpful, or should the question remain open?
Thanks ahead for your feedback and have a nice rest of the day!
We cannot use client side pagination and we do need all attachments to be present before we proceed with our application logic.
The workaround is to split up the work into chunks of X entries which are processed by Y threads in parallel. Sweet spot seems to be around X=30, Y=5
This reduces the amount of time used from ~15s to ~3-4s.
Hi @markus-async-dune, that’s a clever workaround! Parallelizing the chunks definitely helps hide the latency, but the backend is still performing those 2,000+ sequential queries, which puts unnecessary stress on the database connection pool.
To solve this properly, I’d suggest we create an A12 ticket for batch loading support. If we implement this on the server side, we can reduce those thousands of queries into just a few bulk operations. This would be even faster than 3 seconds and much more stable for the system.
