Feature #44364
closed
Improve the performance of the issues API when listing issues
Description
When listing issues, the issues API renders the tracker, assignee, category, target version and custom field values of every issue. Most of these associations are loaded one issue at a time, so the number of queries grows with the page size. Preloading them reduces the number of queries by about 90% for a full page. In my test, it also cut the response time by more than half.
The first patch adds a :preload option to IssueQuery#issues, so that callers can specify the associations they need. The issues API uses this option when listing issues. Attachments are only preloaded when include=attachments is given in the API request.
he second patch is independent of the first. index.api.rsb and show.api.rsb render only the id of the parent issue, but they test its presence through the
association, which loads the parent issue.
Measured on GET /issues.json?limit=100 with 100 issues in the response, including 34 with a parent issue (SQLite, development machine):
| without custom fields | 5 custom fields | |
|---|---|---|
| trunk | 398 queries (76 ms) | 598 queries (140 ms) |
| patch 1 | 86 queries (30 ms) | 87 queries (41 ms) |
| patch 1 + 2 | 52 queries (26 ms) | 53 queries (36 ms) |
On trunk, adding five custom fields increases the number of queries from 398 to 598. With the patches, it only increases from 52 to 53.
Files
Updated by Go MAEDA 25 days ago
- File 0001-Preload-the-associations-rendered-by-the-issues-API-.patch 0001-Preload-the-associations-rendered-by-the-issues-API-.patch added
Here is an updated version of the first patch.
The API renders the author of every attachment, and these authors were still loaded one attachment at a time. The patch now preloads them along
with the attachments:
-preload << :attachments if include_in_api_response?('attachments')
+preload << {:attachments => :author} if include_in_api_response?('attachments')
The rest of the patch is unchanged.
Measured on GET /issues.json?limit=100&include=attachments, with 100 issues in the response and 134 attachments posted by 5 different users:
- previous patch: 193 queries (70 ms)
- updated patch: 52 queries (48 ms)
Requests without include=attachments are not affected.