Project

General

Profile

Actions
Copy link

Defect #35927

open

Slow loading of /time_entries/new

Added by Mark J over 3 years ago. Updated 2 days ago.

Status:
Confirmed
Priority:
Normal
Assignee:
Marius BĂLTEANU
Category:
Time tracking
Target version:
Candidate for next minor release
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Affected version:
4.2.1

Description

Opening /time_entries/new takes 10s, when we have 1000 users with 2 different groups that one has ~10 users and other has all other users.

I have looked into code and it seems I have found the issue, attaching additional info via screens. Basically if project has a lot of users `assignable_users` enumerates each users permissions by one, creating 2*X (X=number users) sql queries.

I'm hosting redmine with help of docker official image.
Environment:
Redmine version 4.2.1.stable
Ruby version 2.7.4-p191 (2021-07-07) [x86_64-linux]
Rails version 5.2.5
Environment debug
Database adapter SQLServer
Mailer queue ActiveJob::QueueAdapters::AsyncAdapter
Mailer delivery smtp
SCM:
Subversion 1.10.4
Mercurial 4.8.2
Bazaar 2.8.0
Git 2.20.1
Filesystem
Redmine plugins:
no plugin installed

Files

Download all files
ConEmu64_UubTYutl5G.png (579 KB) ConEmu64_UubTYutl5G.png debug log of server with run queries Mark J, 2021-09-28 13:00
Code_jzSYRAKaoR.png (77.2 KB) Code_jzSYRAKaoR.png code location Mark J, 2021-09-28 13:00
35927.patch (739 Bytes) 35927.patch Go MAEDA, 2025-02-02 14:09
Actions
Copy link
 #1

Updated by Marius BĂLTEANU over 3 years ago

  • Status changed from New to Confirmed
  • Assignee set to Marius BĂLTEANU
Actions
Copy link
 #2

Updated by Go MAEDA 2 days ago

The attached patch should fixes the issue.
Below are the results tested on a project with 7 members.

Current code:

redmine-app(dev)> t = TimeEntry.first
  TimeEntry Load (0.1ms)  SELECT "time_entries".* FROM "time_entries" ORDER BY "time_entries"."id" ASC LIMIT ?  [["LIMIT", 1]]
=> 
#<TimeEntry:0x000000016c1f2df0
...
redmine-app(dev)> t.assignable_users
  Project Load (0.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  Member Load (0.1ms)  SELECT "members".* FROM "members" INNER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "members"."project_id" = ? AND "users"."type" = ? AND "users"."status" = ? AND "users"."status" = ?  [["project_id", 1], ["type", "User"], ["status", 1], ["status", 1]]
  User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."id" IN (?, ?, ?, ?, ?, ?, ?)  [["type", "User"], ["id", 2], ["id", 3], ["id", 1], ["id", 4], ["id", 7], ["id", 8], ["id", 9]]
  EnabledModule Pluck (0.4ms)  SELECT "enabled_modules"."name" FROM "enabled_modules" WHERE "enabled_modules"."project_id" = ?  [["project_id", 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", 2], ["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" = ?  [["member_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.0ms)  SELECT DISTINCT "roles".* FROM "roles" INNER JOIN "member_roles" ON "roles"."id" = "member_roles"."role_id" WHERE "member_roles"."member_id" = ?  [["member_id", 2]]
  Member Load (0.0ms)  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", 4], ["status", 9], ["project_id", 1], ["LIMIT", 1]]
  Role Load (0.0ms)  SELECT DISTINCT "roles".* FROM "roles" INNER JOIN "member_roles" ON "roles"."id" = "member_roles"."role_id" WHERE "member_roles"."member_id" = ?  [["member_id", 38]]
  Member Load (0.0ms)  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", 7], ["status", 9], ["project_id", 1], ["LIMIT", 1]]
  Role Load (0.0ms)  SELECT DISTINCT "roles".* FROM "roles" INNER JOIN "member_roles" ON "roles"."id" = "member_roles"."role_id" WHERE "member_roles"."member_id" = ?  [["member_id", 39]]
  Member Load (0.0ms)  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", 8], ["status", 9], ["project_id", 1], ["LIMIT", 1]]
  Role Load (0.0ms)  SELECT DISTINCT "roles".* FROM "roles" INNER JOIN "member_roles" ON "roles"."id" = "member_roles"."role_id" WHERE "member_roles"."member_id" = ?  [["member_id", 40]]
  Member Load (0.0ms)  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", 9], ["status", 9], ["project_id", 1], ["LIMIT", 1]]
  Role Load (0.0ms)  SELECT DISTINCT "roles".* FROM "roles" INNER JOIN "member_roles" ON "roles"."id" = "member_roles"."role_id" WHERE "member_roles"."member_id" = ?  [["member_id", 41]]
  AnonymousUser Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."lastname" = ? LIMIT ?  [["type", "AnonymousUser"], ["lastname", "Anonymous"], ["LIMIT", 1]]

With the patch applied:

redmine-app(dev)> t = TimeEntry.first
  TimeEntry Load (0.4ms)  SELECT "time_entries".* FROM "time_entries" ORDER BY "time_entries"."id" ASC LIMIT ?  [["LIMIT", 1]]
=> 
#<TimeEntry:0x000000030b457f00
...
redmine-app(dev)> t.assignable_users
  Project Load (0.4ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = ? LIMIT ?  [["id", 1], ["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" = ? AND "users"."status" = ? AND "users"."status" = ?  [["project_id", 1], ["type", "User"], ["status", 1], ["status", 1]]
  MemberRole Load (0.1ms)  SELECT "member_roles".* FROM "member_roles" WHERE "member_roles"."member_id" IN (?, ?, ?, ?, ?, ?, ?)  [["member_id", 1], ["member_id", 2], ["member_id", 37], ["member_id", 38], ["member_id", 39], ["member_id", 40], ["member_id", 41]]
  Role Load (0.1ms)  SELECT DISTINCT "roles".* FROM "roles" WHERE "roles"."id" IN (?, ?)  [["id", 1], ["id", 2]]
  User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."id" IN (?, ?, ?, ?, ?, ?, ?)  [["type", "User"], ["id", 2], ["id", 3], ["id", 1], ["id", 4], ["id", 7], ["id", 8], ["id", 9]]
  AnonymousUser Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."type" = ? AND "users"."lastname" = ? LIMIT ?  [["type", "AnonymousUser"], ["lastname", "Anonymous"], ["LIMIT", 1]]

Actions
Copy link

Also available in: Atom PDF