Feature #42150
closedOptimize TimeEntryActivity#default_activity_id method by introducing early returns to reduce SQL queries
0%
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
Updated by Go MAEDA about 2 months ago
- Status changed from New to Closed
- Assignee set to Go MAEDA
- Resolution set to Fixed
Committed the patch in r23477.