Feature #36505: Reduce database queries when rendering Custom fields box in the project settings tab - Redmine

Feature #36505

Reduce database queries when rendering Custom fields box in the project settings tab

Added by Go MAEDA 4 months ago. Updated about 1 month ago.

Status:

Closed

Start date:

Priority:

Normal

Due date:

Assignee:

Go MAEDA

% Done:

Category:

Performance

Target version:

5.0.0

Resolution:

Description

Currently, rendering Custom fields box in the project settings tab generates SQL statements as many as the number of available issue custom fields. The attached patch improves the code to generate only one SQL statement.

Before:

  IssueCustomField Exists? (0.3ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = ? AND (is_for_all = 1 OR id IN (S
ELECT DISTINCT cfp.custom_field_id FROM custom_fields_projects cfp WHERE cfp.project_id = 1)) AND "custom_fields"."id" = ? LIMIT ?  [["ty
pe", "IssueCustomField"], ["id", 2], ["LIMIT", 1]]
  ↳ app/views/projects/settings/_issues.html.erb:26
  IssueCustomField Exists? (0.3ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = ? AND (is_for_all = 1 OR id IN (S
ELECT DISTINCT cfp.custom_field_id FROM custom_fields_projects cfp WHERE cfp.project_id = 1)) AND "custom_fields"."id" = ? LIMIT ?  [["ty
pe", "IssueCustomField"], ["id", 1], ["LIMIT", 1]]
  ↳ app/views/projects/settings/_issues.html.erb:26
  IssueCustomField Exists? (0.2ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = ? AND (is_for_all = 1 OR id IN (S
ELECT DISTINCT cfp.custom_field_id FROM custom_fields_projects cfp WHERE cfp.project_id = 1)) AND "custom_fields"."id" = ? LIMIT ?  [["ty
pe", "IssueCustomField"], ["id", 6], ["LIMIT", 1]]
  ↳ app/views/projects/settings/_issues.html.erb:26
  IssueCustomField Exists? (0.2ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = ? AND (is_for_all = 1 OR id IN (S
ELECT DISTINCT cfp.custom_field_id FROM custom_fields_projects cfp WHERE cfp.project_id = 1)) AND "custom_fields"."id" = ? LIMIT ?  [["ty
pe", "IssueCustomField"], ["id", 8], ["LIMIT", 1]]
  ↳ app/views/projects/settings/_issues.html.erb:26
  IssueCustomField Exists? (0.3ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = ? AND (is_for_all = 1 OR id IN (S
ELECT DISTINCT cfp.custom_field_id FROM custom_fields_projects cfp WHERE cfp.project_id = 1)) AND "custom_fields"."id" = ? LIMIT ?  [["ty
pe", "IssueCustomField"], ["id", 9], ["LIMIT", 1]]
  ↳ app/views/projects/settings/_issues.html.erb:26

After:

   (0.3ms)  SELECT "custom_fields"."id" FROM "custom_fields" WHERE "custom_fields"."type" = ? AND (is_for_all = 1 OR id IN (SELECT DISTIN
CT cfp.custom_field_id FROM custom_fields_projects cfp WHERE cfp.project_id = 1)) ORDER BY "custom_fields"."position" ASC  [["type", "Iss
ueCustomField"]]
  ↳ app/views/projects/settings/_issues.html.erb:24

reduce-database-query-project-settings.patch (1007 Bytes) Go MAEDA, 2022-01-23 13:55