Actions
Feature #35324
closed
Preload principal and roles in members#index
Added by Thomas Löber almost 3 years ago. Updated about 2 years ago.
Start date:
Due date:
% Done:
0%
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 3 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:
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:
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)
Updated by Go MAEDA almost 3 years ago
- Target version changed from Candidate for next major release to 5.0.0
Setting the target version to 5.0.0.
Updated by Go MAEDA almost 3 years ago
- Status changed from New to Closed
- Assignee set to Go MAEDA
Committed the patch. Thank you for your contribution.
Actions