Optimize querying file attachments in the API.

Review Request #13794 — Created April 25, 2024 and submitted

Information

Review Board
release-7.x

Reviewers

File attachments are a bit expensive to query fully, because they may be
bound to a ReviewRequest or a ReviewRequestDraft, wither as an
"active" or "inactive" file attachment. Depending on the API and the
request, we may need to get a subset of those or all of those, and that
can lead to a lot of queries. On top of that, we needed to then fetch
the review request or draft associated with those, and this triggered up
to two more requests per file attachment.

This change improves upon all of this, minimizing queries and accesses.

We now use the relation counters on the ReviewRequest and
ReviewRequestDraft to determine which fields we want to even consider
querying. This will, in many cases, reduce the query counts.

We then build a list of fields for prefetch_related(), which will let
us query those review requests or drafts all at once, rather than
per-result.

We also avoid any exclusion filtering for the main review request file
attachment resource if we know the exclusion list will be empty.

And finally, if all considered counts are 0, we just return an empty
result immediately, rather than hitting the database.

This is still a lot more queries than we'd ideally have, but it does
keep the query counts at an upper limit, rather than being proportional
to the number of file attachments returned.

Unit tests pass.

Summary ID
Optimize querying file attachments in the API.
File attachments are a bit expensive to query fully, because they may be bound to a `ReviewRequest` or a `ReviewRequestDraft`, wither as an "active" or "inactive" file attachment. Depending on the API and the request, we may need to get a subset of those or all of those, and that can lead to a lot of queries. On top of that, we needed to then fetch the review request or draft associated with those, and this triggered up to two more requests per file attachment. This change improves upon all of this, minimizing queries and accesses. We now use the relation counters on the `ReviewRequest` and `ReviewRequestDraft` to determine which fields we want to even consider querying. This will, in many cases, reduce the query counts. We also build a list of fields for `prefetch_related()`, which will let us query those review requests or drafts all at once, rather than per-result. And finally, if all considered counts are 0, we just return an empty result immediately, rather than hitting the database. This is still a lot more queries than we'd ideally have, but it does keep the query counts at an upper limit, rather than being proportional to the number of file attachments returned.
34a8b9e71b435bde70530d865db1a7db21d9c8ea
Description From Last Updated

'django.db.models.Value' imported but unused Column: 1 Error code: F401

reviewbotreviewbot
Checks run (1 failed, 1 succeeded)
flake8 failed.
JSHint passed.

flake8

chipx86
david
  1. Ship It!
  2. 
      
maubin
  1. Ship It!
  2. 
      
chipx86
Review request changed
Status:
Completed
Change Summary:
Pushed to release-7.x (00e3bd4)