Actions
Patch #40798
closed
Optimize Version model
Description
The attached patch improves the performance of several methods in the Version model such as Version#issues_count, FixedIssuesExtension#completed_percent, FixedIssuesExtension#closed_percent, and FixedIssuesExtension#issues_progress.
These methods use count method to get the total number of fixed issues in the version. It performs an SQL count query every time it is called. This patch replaces count with open_count + closed_count in order to use cached values instead of performing SQL count queries.
The change has an effect on Gantt as well as Roadmap.
Files
Updated by Go MAEDA 7 months ago
The following are SQL queries triggered by Version#behind_schedule?.
Before:
irb(main):001> Version.find(2).behind_schedule?
Version Load (0.1ms) SELECT "versions".* FROM "versions" WHERE "versions"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
Issue Count (0.1ms) SELECT COUNT(*) FROM "issues" WHERE "issues"."fixed_version_id" = ? [["fixed_version_id", 2]]
Issue Count (0.1ms) SELECT COUNT(*) AS "count_all", "issues"."status_id" AS "issues_status_id" FROM "issues" WHERE "issues"."fixed_version_id" = ? GROUP BY "issues"."status_id" [["fixed_version_id", 2]]
IssueStatus Load (0.1ms) SELECT "issue_statuses".* FROM "issue_statuses" WHERE "issue_statuses"."id" IN (?, ?) [["id", 2], ["id", 5]]
Issue Count (0.1ms) SELECT COUNT(*) FROM "issues" WHERE "issues"."fixed_version_id" = ? [["fixed_version_id", 2]]
Issue Load (0.2ms) SELECT "issues".* FROM "issues" INNER JOIN "issue_statuses" ON "issue_statuses"."id" = "issues"."status_id" WHERE "issues"."fixed_version_id" = ? AND "issue_statuses"."is_closed" = ? [["fixed_version_id", 2], ["is_closed", 1]]
Issue Load (0.1ms) SELECT "issues".* FROM "issues" WHERE "issues"."fixed_version_id" = ? [["fixed_version_id", 2]]
IssueStatus Load (0.1ms) SELECT "issue_statuses".* FROM "issue_statuses" WHERE "issue_statuses"."id" = ? LIMIT ? [["id", 5], ["LIMIT", 1]]
Issue Count (0.0ms) SELECT COUNT(*) FROM "issues" WHERE "issues"."fixed_version_id" = ? [["fixed_version_id", 2]]
Issue Count (0.0ms) SELECT COUNT(*) FROM "issues" WHERE "issues"."fixed_version_id" = ? [["fixed_version_id", 2]]
Issue Load (0.0ms) SELECT "issues".* FROM "issues" INNER JOIN "issue_statuses" ON "issue_statuses"."id" = "issues"."status_id" WHERE "issues"."fixed_version_id" = ? AND "issue_statuses"."is_closed" = ? [["fixed_version_id", 2], ["is_closed", 0]]
IssueStatus Load (0.0ms) SELECT "issue_statuses".* FROM "issue_statuses" WHERE "issue_statuses"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
Setting Load (0.0ms) SELECT "settings".* FROM "settings" WHERE "settings"."name" = ? ORDER BY "settings"."id" DESC LIMIT ? [["name", "issue_done_ratio"], ["LIMIT", 1]]
Issue Count (0.0ms) SELECT COUNT(*) FROM "issues" WHERE "issues"."fixed_version_id" = ? [["fixed_version_id", 2]]
Issue Minimum (0.0ms) SELECT MIN("issues"."start_date") FROM "issues" WHERE "issues"."fixed_version_id" = ? [["fixed_version_id", 2]]
Issue Count (0.0ms) SELECT COUNT(*) FROM "issues" WHERE "issues"."fixed_version_id" = ? [["fixed_version_id", 2]]
AnonymousUser Load (0.1ms) SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."lastname" = ? LIMIT ? [["type", "AnonymousUser"], ["lastname", "Anonymous"], ["LIMIT", 1]]
=> false
After:
irb(main):001> Version.find(2).behind_schedule?
Version Load (0.1ms) SELECT "versions".* FROM "versions" WHERE "versions"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
Issue Count (0.1ms) SELECT COUNT(*) AS "count_all", "issues"."status_id" AS "issues_status_id" FROM "issues" WHERE "issues"."fixed_version_id" = ? GROUP BY "issues"."status_id" [["fixed_version_id", 2]]
IssueStatus Load (0.1ms) SELECT "issue_statuses".* FROM "issue_statuses" WHERE "issue_statuses"."id" IN (?, ?) [["id", 2], ["id", 5]]
Issue Load (0.1ms) SELECT "issues".* FROM "issues" INNER JOIN "issue_statuses" ON "issue_statuses"."id" = "issues"."status_id" WHERE "issues"."fixed_version_id" = ? AND "issue_statuses"."is_closed" = ? [["fixed_version_id", 2], ["is_closed", 1]]
Issue Load (0.5ms) SELECT "issues".* FROM "issues" WHERE "issues"."fixed_version_id" = ? [["fixed_version_id", 2]]
IssueStatus Load (0.0ms) SELECT "issue_statuses".* FROM "issue_statuses" WHERE "issue_statuses"."id" = ? LIMIT ? [["id", 5], ["LIMIT", 1]]
Issue Load (0.0ms) SELECT "issues".* FROM "issues" INNER JOIN "issue_statuses" ON "issue_statuses"."id" = "issues"."status_id" WHERE "issues"."fixed_version_id" = ? AND "issue_statuses"."is_closed" = ? [["fixed_version_id", 2], ["is_closed", 0]]
IssueStatus Load (0.0ms) SELECT "issue_statuses".* FROM "issue_statuses" WHERE "issue_statuses"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
Setting Load (0.0ms) SELECT "settings".* FROM "settings" WHERE "settings"."name" = ? ORDER BY "settings"."id" DESC LIMIT ? [["name", "issue_done_ratio"], ["LIMIT", 1]]
Issue Minimum (0.1ms) SELECT MIN("issues"."start_date") FROM "issues" WHERE "issues"."fixed_version_id" = ? [["fixed_version_id", 2]]
AnonymousUser Load (0.1ms) SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."lastname" = ? LIMIT ? [["type", "AnonymousUser"], ["lastname", "Anonymous"], ["LIMIT", 1]]
=> false
Updated by Go MAEDA 7 months ago
- File 0001-Improve-performance-of-Version-model-by-reducing-red.patch 0001-Improve-performance-of-Version-model-by-reducing-red.patch added
- File 0002-Optimize-FixedIssuesExtension-closed_percent-by-bypa.patch 0002-Optimize-FixedIssuesExtension-closed_percent-by-bypa.patch added
- File 0003-Optimize-Version-behind_schedule-by-avoiding-the-cal.patch 0003-Optimize-Version-behind_schedule-by-avoiding-the-cal.patch added
Slightly improved the patches.
Actions