Feature #42150: Optimize TimeEntryActivity#default_activity_id method by introducing early returns to reduce SQL queries - Redmine

Actions

Copy link

Feature #42150

closed

Optimize TimeEntryActivity#default_activity_id method by introducing early returns to reduce SQL queries

Added by Go MAEDA 2 months ago. Updated 2 months ago.

Status:

Closed

Priority:

Normal

Assignee:

Go MAEDA

Category:

Performance

Target version:

6.1.0

Start date:

Due date:

% Done:

Estimated time:

Resolution:

Fixed

Description

The TimeEntryActivity#default_activity_id method, which is called even when displaying a single issue, determines the default time entry activity by iterating through multiple conditions, including user roles, project-level defaults, and global defaults. However, even in cases where the default activity can be determined early, such as when a role with a default activity is present, the method continues to execute the remaining code without an early return. This results in unnecessary SQL queries being executed.

This patch introduces early returns to the TimeEntryActivity#default_activity_id method, allowing it to exit as soon as the default activity is determined. For example, if a user role has a default activity set, the method now immediately returns without querying project or global defaults. By avoiding unnecessary SQL queries, this change improves the method's performance.

Queries when a default activity is set for the user's role:

The current implementation:
11 queries in TimeEntryActivity#default_activity_id

redmine-app(dev)> user, project = [User.find_by(login: 'dlopper'), Project.find_by(identifier: 'ecookbook')]
  User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."login" = ? LIMIT ?  [["type", "User"], ["login", "dlopper"], ["LIMIT", 1]]
  Project Load (0.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."identifier" = ? LIMIT ?  [["identifier", "ecookbook"], ["LIMIT", 1]]
=> 
[#<User:0x00000001402fab70
...
redmine-app(dev)> TimeEntryActivity.default_activity_id(user, project)
  TimeEntryActivity Pluck (0.2ms)  SELECT "enumerations"."parent_id" FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."project_id" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], ["project_id", 1]]
  Member Load (0.2ms)  SELECT "members".* FROM "members" INNER JOIN "projects" ON "projects"."id" = "members"."project_id" WHERE "members"."user_id" = ? AND "projects"."status" != ? AND "members"."project_id" = ? ORDER BY "members"."id" ASC LIMIT ?  [["user_id", 3], ["status", 9], ["project_id", 1], ["LIMIT", 1]]
  Role Load (0.1ms)  SELECT DISTINCT "roles".* FROM "roles" INNER JOIN "member_roles" ON "roles"."id" = "member_roles"."role_id" WHERE "member_roles"."member_id" = ? AND "roles"."default_time_entry_activity_id" IS NOT NULL  [["member_id", 2]]
  TimeEntryActivity Load (0.2ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."is_default" = ? ORDER BY "enumerations"."position" ASC LIMIT ?  [["type", "TimeEntryActivity"], ["is_default", 1], ["LIMIT", 1]]
  TimeEntryActivity Pluck (0.0ms)  SELECT "enumerations"."parent_id" FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."project_id" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], ["project_id", 1]]
  TimeEntryActivity Load (0.1ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1]]
  TimeEntryActivity Pluck (0.0ms)  SELECT "enumerations"."parent_id" FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."project_id" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], ["project_id", 1]]
  TimeEntryActivity Exists? (0.1ms)  SELECT 1 AS one FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ? AND "enumerations"."id" = ? LIMIT ?  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1], ["id", 10], ["LIMIT", 1]]
  TimeEntryActivity Load (0.1ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."is_default" = ? ORDER BY "enumerations"."position" ASC LIMIT ?  [["type", "TimeEntryActivity"], ["is_default", 1], ["LIMIT", 1]]
  TimeEntryActivity Count (0.1ms)  SELECT COUNT(*) FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ?  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1]]
  TimeEntryActivity Load (0.1ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1]]
=> 10

With the patch applied:
4 queries in TimeEntryActivity#default_activity_id

redmine-app(dev)> user, project = [User.find_by(login: 'dlopper'), Project.find_by(identifier: 'ecookbook')]
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."login" = ? LIMIT ?  [["type", "User"], ["login", "dlopper"], ["LIMIT", 1]]
  Project Load (0.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."identifier" = ? LIMIT ?  [["identifier", "ecookbook"], ["LIMIT", 1]]
=> 
[#<User:0x0000000148236ce0
...
redmine-app(dev)> TimeEntryActivity.default_activity_id(user, project)
  TimeEntryActivity Pluck (0.1ms)  SELECT "enumerations"."parent_id" FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."project_id" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], ["project_id", 1]]
  TimeEntryActivity Load (0.1ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1]]
  Member Load (0.2ms)  SELECT "members".* FROM "members" INNER JOIN "projects" ON "projects"."id" = "members"."project_id" WHERE "members"."user_id" = ? AND "projects"."status" != ? AND "members"."project_id" = ? ORDER BY "members"."id" ASC LIMIT ?  [["user_id", 3], ["status", 9], ["project_id", 1], ["LIMIT", 1]]
  Role Load (0.1ms)  SELECT DISTINCT "roles".* FROM "roles" INNER JOIN "member_roles" ON "roles"."id" = "member_roles"."role_id" WHERE "member_roles"."member_id" = ? AND "roles"."default_time_entry_activity_id" IS NOT NULL  [["member_id", 2]]
=> 10

Queries when no default activity is set for the user's role:

The current implementation:
11 queries in TimeEntryActivity#default_activity_id

redmine-app(dev)> user, project = [User.find_by(login: 'dlopper'), Project.find_by(identifier: 'ecookbook')]
  User Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."login" = ? LIMIT ?  [["type", "User"], ["login", "dlopper"], ["LIMIT", 1]]
  Project Load (0.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."identifier" = ? LIMIT ?  [["identifier", "ecookbook"], ["LIMIT", 1]]
=> 
[#<User:0x000000011caf67d0
...
redmine-app(dev)> TimeEntryActivity.default_activity_id(user, project)
  TimeEntryActivity Pluck (0.4ms)  SELECT "enumerations"."parent_id" FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."project_id" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], ["project_id", 1]]
  Member Load (0.2ms)  SELECT "members".* FROM "members" INNER JOIN "projects" ON "projects"."id" = "members"."project_id" WHERE "members"."user_id" = ? AND "projects"."status" != ? AND "members"."project_id" = ? ORDER BY "members"."id" ASC LIMIT ?  [["user_id", 3], ["status", 9], ["project_id", 1], ["LIMIT", 1]]
  Role Load (0.1ms)  SELECT DISTINCT "roles".* FROM "roles" INNER JOIN "member_roles" ON "roles"."id" = "member_roles"."role_id" WHERE "member_roles"."member_id" = ? AND "roles"."default_time_entry_activity_id" IS NOT NULL  [["member_id", 2]]
  TimeEntryActivity Load (0.1ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."is_default" = ? ORDER BY "enumerations"."position" ASC LIMIT ?  [["type", "TimeEntryActivity"], ["is_default", 1], ["LIMIT", 1]]
  TimeEntryActivity Pluck (0.0ms)  SELECT "enumerations"."parent_id" FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."project_id" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], ["project_id", 1]]
  TimeEntryActivity Load (0.0ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1]]
  TimeEntryActivity Pluck (0.0ms)  SELECT "enumerations"."parent_id" FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."project_id" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], ["project_id", 1]]
  TimeEntryActivity Exists? (0.1ms)  SELECT 1 AS one FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ? AND "enumerations"."id" = ? LIMIT ?  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1], ["id", 10], ["LIMIT", 1]]
  TimeEntryActivity Load (0.1ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."is_default" = ? ORDER BY "enumerations"."position" ASC LIMIT ?  [["type", "TimeEntryActivity"], ["is_default", 1], ["LIMIT", 1]]
  TimeEntryActivity Count (0.1ms)  SELECT COUNT(*) FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ?  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1]]
  TimeEntryActivity Load (0.0ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1]]
=> 10

With the patch applied:
9 queries in TimeEntryActivity#default_activity_id

redmine-app(dev)> user, project = [User.find_by(login: 'dlopper'), Project.find_by(identifier: 'ecookbook')]
  User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."login" = ? LIMIT ?  [["type", "User"], ["login", "dlopper"], ["LIMIT", 1]]
  Project Load (0.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."identifier" = ? LIMIT ?  [["identifier", "ecookbook"], ["LIMIT", 1]]
=> 
[#<User:0x0000000130e74290
...
redmine-app(dev)> TimeEntryActivity.default_activity_id(user, project)
  TimeEntryActivity Pluck (0.1ms)  SELECT "enumerations"."parent_id" FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."project_id" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], ["project_id", 1]]
  TimeEntryActivity Load (0.1ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1]]
  Member Load (0.1ms)  SELECT "members".* FROM "members" INNER JOIN "projects" ON "projects"."id" = "members"."project_id" WHERE "members"."user_id" = ? AND "projects"."status" != ? AND "members"."project_id" = ? ORDER BY "members"."id" ASC LIMIT ?  [["user_id", 3], ["status", 9], ["project_id", 1], ["LIMIT", 1]]
  Role Load (0.1ms)  SELECT DISTINCT "roles".* FROM "roles" INNER JOIN "member_roles" ON "roles"."id" = "member_roles"."role_id" WHERE "member_roles"."member_id" = ? AND "roles"."default_time_entry_activity_id" IS NOT NULL  [["member_id", 2]]
  TimeEntryActivity Load (0.1ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."is_default" = ? ORDER BY "enumerations"."position" ASC LIMIT ?  [["type", "TimeEntryActivity"], ["is_default", 1], ["LIMIT", 1]]
  TimeEntryActivity Pluck (0.0ms)  SELECT "enumerations"."parent_id" FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."project_id" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], ["project_id", 1]]
  TimeEntryActivity Load (0.0ms)  SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1]]
  TimeEntryActivity Pluck (0.0ms)  SELECT "enumerations"."parent_id" FROM "enumerations" WHERE "enumerations"."type" = ? AND "enumerations"."project_id" = ? ORDER BY "enumerations"."position" ASC  [["type", "TimeEntryActivity"], ["project_id", 1]]
  TimeEntryActivity Exists? (0.0ms)  SELECT 1 AS one FROM "enumerations" WHERE "enumerations"."type" = ? AND (enumerations.project_id IS NULL OR enumerations.project_id = ?) AND "enumerations"."active" = ? AND "enumerations"."id" = ? LIMIT ?  [["type", "TimeEntryActivity"], [nil, 1], ["active", 1], ["id", 10], ["LIMIT", 1]]
=> 10

Files

optimize-default_activity_id.patch (2.39 KB) optimize-default_activity_id.patch

Go MAEDA, 2025-01-23 12:36