Feature #35324: Preload principal and roles in members#index - Redmine

Actions

Copy link

Feature #35324

closed

Preload principal and roles in members#index

Added by Thomas Löber almost 4 years ago. Updated about 3 years ago.

Status:

Closed

Priority:

Normal

Assignee:

Go MAEDA

Category:

Performance

Target version:

5.0.0

Start date:

Due date:

% Done:

Estimated time:

Resolution:

Description

Hi,

MembersController#index should preload the principal and roles of the members.

When this data is not preloaded, iterating over the @members array in app/views/members/index.api.rsb causes a lot of database queries.

A patch is attached.

Thanks,
Thomas

Files

members_index_preload.diff (741 Bytes) members_index_preload.diff

Thomas Löber, 2021-05-28 12:27

Updated by Go MAEDA almost 4 years ago

Target version set to Candidate for next major release

I have confirmed the issue. Before applying the patch, three SQL statements like the following are executed for each member.

  Principal Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 9], ["LIMIT", 1]]
  MemberRole Load (0.5ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" = ?  [["member_id", 15]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]

Before:

Show Hide

Started GET "/projects/ecookbook/memberships.json" for 127.0.0.1 at 2021-06-02 09:15:54 +0900
Processing by MembersController#index as JSON
  Parameters: {"project_id"=>"ecookbook"}
   (0.2ms)  SELECT MAX("settings"."updated_on") FROM "settings" 
  AnonymousUser Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."lastname" = ? LIMIT ?  [["type", "AnonymousUser"], ["lastname", "Anonymous"], ["LIMIT", 1]]
  Current user: anonymous
  FilterRule Load (0.1ms)  SELECT "settings".* FROM "settings" WHERE "settings"."name" = ? ORDER BY "settings"."id" DESC LIMIT ?  [["name", "plugin_redmine_ip_filter"], ["LIMIT", 1]]
  Project Load (0.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."identifier" = ? LIMIT ?  [["identifier", "ecookbook"], ["LIMIT", 1]]
   (0.2ms)  SELECT "enabled_modules"."name" FROM "enabled_modules" WHERE "enabled_modules"."project_id" = ?  [["project_id", 1]]
  Role Load (0.5ms)  SELECT "roles".* FROM "roles" WHERE "roles"."builtin" = ? LIMIT ?  [["builtin", 2], ["LIMIT", 1]]
  Member Load (0.3ms)  SELECT "members".* FROM "members" INNER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "members"."project_id" = ? AND "users"."type" IN (?, ?)  [["project_id", 1], [nil, "GroupAnonymous"], [nil, "GroupNonMember"]]
   (0.2ms)  SELECT COUNT(*) FROM "members" WHERE "members"."project_id" = ?  [["project_id", 1]]
  Member Load (0.2ms)  SELECT "members".* FROM "members" WHERE "members"."project_id" = ? ORDER BY "members"."id" ASC LIMIT ? OFFSET ?  [["project_id", 1], ["LIMIT", 25], ["OFFSET", 0]]
  Rendering members/index.api.rsb
  Principal Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  MemberRole Load (0.1ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" = ?  [["member_id", 1]]
  Role Load (0.2ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Principal Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 3], ["LIMIT", 1]]
  MemberRole Load (0.1ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" = ?  [["member_id", 2]]
  Role Load (0.1ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  Principal Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 5], ["LIMIT", 1]]
  MemberRole Load (0.1ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" = ?  [["member_id", 4]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  Principal Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  MemberRole Load (0.1ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" = ?  [["member_id", 11]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  Principal Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 4], ["LIMIT", 1]]
  MemberRole Load (0.1ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" = ?  [["member_id", 12]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  Principal Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 7], ["LIMIT", 1]]
  MemberRole Load (0.2ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" = ?  [["member_id", 13]]
  CACHE Role Load (0.1ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  Principal Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 8], ["LIMIT", 1]]
  MemberRole Load (0.1ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" = ?  [["member_id", 14]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  Principal Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 9], ["LIMIT", 1]]
  MemberRole Load (0.5ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" = ?  [["member_id", 15]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  Rendered members/index.api.rsb (Duration: 32.0ms | Allocations: 6057)
Completed 200 OK in 49ms (Views: 29.7ms | ActiveRecord: 4.8ms | Allocations: 9100)

After:

Show Hide

Started GET "/projects/ecookbook/memberships.json" for 127.0.0.1 at 2021-06-02 09:14:25 +0900
Processing by MembersController#index as JSON
  Parameters: {"project_id"=>"ecookbook"}
   (0.2ms)  SELECT MAX("settings"."updated_on") FROM "settings" 
  AnonymousUser Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."lastname" = ? LIMIT ?  [["type", "AnonymousUser"], ["lastname", "Anonymous"], ["LIMIT", 1]]
  Current user: anonymous
  FilterRule Load (0.1ms)  SELECT "settings".* FROM "settings" WHERE "settings"."name" = ? ORDER BY "settings"."id" DESC LIMIT ?  [["name", "plugin_redmine_ip_filter"], ["LIMIT", 1]]
  Project Load (0.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."identifier" = ? LIMIT ?  [["identifier", "ecookbook"], ["LIMIT", 1]]
   (0.2ms)  SELECT "enabled_modules"."name" FROM "enabled_modules" WHERE "enabled_modules"."project_id" = ?  [["project_id", 1]]
  Role Load (0.1ms)  SELECT "roles".* FROM "roles" WHERE "roles"."builtin" = ? LIMIT ?  [["builtin", 2], ["LIMIT", 1]]
  Member Load (0.3ms)  SELECT "members".* FROM "members" INNER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "members"."project_id" = ? AND "users"."type" IN (?, ?)  [["project_id", 1], [nil, "GroupAnonymous"], [nil, "GroupNonMember"]]
   (0.2ms)  SELECT COUNT(*) FROM "members" WHERE "members"."project_id" = ?  [["project_id", 1]]
  Member Load (0.2ms)  SELECT "members".* FROM "members" WHERE "members"."project_id" = ? ORDER BY "members"."id" ASC LIMIT ? OFFSET ?  [["project_id", 1], ["LIMIT", 25], ["OFFSET", 0]]
  Principal Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?, ?, ?, ?, ?, ?)  [[nil, 2], [nil, 3], [nil, 5], [nil, 1], [nil, 4], [nil, 7], [nil, 8], [nil, 9]]
  MemberRole Load (0.3ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" IN (?, ?, ?, ?, ?, ?, ?, ?)  [[nil, 1], [nil, 2], [nil, 4], [nil, 11], [nil, 12], [nil, 13], [nil, 14], [nil, 15]]
  Role Load (0.2ms)  SELECT DISTINCT "roles".* FROM "roles" WHERE "roles"."id" IN (?, ?)  [[nil, 1], [nil, 2]]
  Rendering members/index.api.rsb
  Role Load (0.1ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Role Load (0.1ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  CACHE Role Load (0.0ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  Rendered members/index.api.rsb (Duration: 12.0ms | Allocations: 2731)
Completed 200 OK in 37ms (Views: 12.3ms | ActiveRecord: 2.8ms | Allocations: 7396)