Defect #33289

Updating time tracking activities in project setting may take too long time

Added by shigeo teraoka 6 months ago. Updated about 1 month ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Go MAEDA% Done:

0%

Category:Performance
Target version:4.2.0
Resolution:Fixed Affected version:

Description

When the number of time tracking is large, it takes time to update the active check of time tracking setting.
I want you to improve the response of the update of the active check of the time tracking setting.

The number of time tracking is 66.
It takes more than 5 minutes to update the check for Active in setting.
Redmine performance will be reduced during the update.

Japanese---------------------------------------------------
作業分類(時間管理)の数が多いと設定の時間管理タブで有効のチェックの更新に時間が掛かります。
時間管理タブで有効チェックの更新レスポンスを改善してほしいです。

作業分類(時間管理)の数は66個です。
時間管理タブで有効チェックの更新に5分以上掛かります。
更新の間はredmineの性能が低下します。

33289.patch Magnifier (2.5 KB) Mizuki ISHIKAWA, 2020-09-03 07:45

Associated revisions

Revision 20066
Added by Go MAEDA about 1 month ago

Fix that updating time tracking activities in a project may take a long time (#33289).

Patch by Mizuki ISHIKAWA.

Revision 20093
Added by Go MAEDA 24 days ago

Fix a RuboCop offense Style/SymbolArray due to r20066 (#33289).

History

#1 Updated by shigeo teraoka about 1 month ago

[settings] -> [Time tracking] -> [Update Active checkbox] -> [save]

Currently there are 69 items, but Redmine freezes for tens of minutes when updated.

Can someone help me.

#2 Updated by Go MAEDA about 1 month ago

Teraoka-san, how many projects does the Redmine have?

#3 Updated by shigeo teraoka about 1 month ago

Redmine has 800 active projects.

#4 Updated by Mizuki ISHIKAWA about 1 month ago

It seems that processing https://www.redmine.org/projects/redmine/repository/revisions/19983/entry/trunk/app/models/enumeration.rb#L150 takes a long time.

The following SQL is executed every time one setting is changed.
If you have 66 time tracking activities, the following SQL will be executed 66 times.

TimeEntryActivity Update All (800.9 ms) 
UPDATE "enumerations" SET position = coalesce((
          select position
          from (select id, position from enumerations) as parent
          where parent_id = parent.id), 1) WHERE "enumerations"."id" IN (SELECT "enumerations"."id" FROM "enumerations" WHERE "enumerations"."type" IN ('TimeEntryActivity') AND "enumerations"."parent_id" IS NOT NULL ORDER BY "enumerations"."position" ASC)

#5 Updated by shigeo teraoka about 1 month ago

Thank you for your analysis.

If SQL is executed 66 times, it will take time to update.
Is it possible to improve it?

#6 Updated by Mizuki ISHIKAWA about 1 month ago

I am attaching a patch to improve this situation.

Probably Enumaration#update_position is the code to change the position of the project activity according to the position of the parent activity.
Simply adding a project activity should not require repositioning other project activities.
This patch changes the position of other project activities only if you sort the activities in Administration> Enumerations.

After applying this patch, the following SQL should not be executed even if you change the settings on the time tracking tab. It should speed up the settings.

TimeEntryActivity Update All (800.9 ms) 
UPDATE "enumerations" SET position = coalesce((
          select position
          from (select id, position from enumerations) as parent
          where parent_id = parent.id), 1) WHERE "enumerations"."id" IN (SELECT "enumerations"."id" FROM "enumerations" WHERE "enumerations"."type" IN ('TimeEntryActivity') AND "enumerations"."parent_id" IS NOT NULL ORDER BY "enumerations"."position" ASC)

#7 Updated by Go MAEDA about 1 month ago

  • Subject changed from I want to improve the update response of the checkbox on the time tracking tab of the setting to Updating time tracking activities in project setting may take too long time
  • Category changed from Time tracking to Performance

#8 Updated by Stoyan Zhekov about 1 month ago

I was able to reproduce the problem and confirm, that the patch, provided above, is improving the performance.

First a problem recap and how to reproduce it:

The way TimeEntryActivity works: you create them in 'Administration|Enumerations|Activities' and change them per project in 'Project|Settings|Time Tracing' .

The important thing: only when something is changed in per project settings, new DB record is created. So even with a lot of activities and projects, there will be no visible problem, if there are no changes.

To create the test setup, a script, provided by Mr. Mizuki Ishikawa (the patch author) was used:

70.times do |n|
  TimeEntryActivity.create!(name: "TimeEntryActivity#{n}")
end
100.times do |n|
  project = Project.create!(name: "Project#{n}", identifier: "project-#{n}")
  project.set_parent!(nil)
  project.update_or_create_time_entry_activities(
    TimeEntryActivity.where(project_id: nil).map do |t|
      [
        t.id.to_s, {
          "parent_id" => t.id.to_s,
          "active" => "0",
          "custom_field_values" => {"7" => "0"}
     }
      ]
    end.to_h
  )
end

A small warning: Apply the patch before running the script and then remove it, if needed, for the tests. Without the patch it is taking very long time to create the DB records. Which already is a prove for patch effectiveness.

The way to introduce a lot of changes is from 'Project|Settings|Time tracking' to first click 'Reset' and then manually change all the activities. Then click 'Save' button and check how long it takes to save the changes.

I tested with 100 and with 200 projects. Results from my tests (local PostgreSQL DB, running in Docker container, Redmine-4.1.1):

- no patch, 70 activities, 100 projects: ~35sec
- no patch, 70 activities, 200 projects: ~50sec
- with patch, 70 activities, 200 projects: ~2-3sec

As you can see increasing the number of projects increase the time of saving. So it is possible, as Mr. Shigeo Teraoka reported, with 800 projects this time to be more then 5 min.
And as you can see applying the patch will reduce that time significantly.

#9 Updated by Mizuki ISHIKAWA about 1 month ago

Stoyan Zhekov wrote:

I was able to reproduce the problem and confirm, that the patch, provided above, is improving the performance.

Thank you for checking the patch.

#10 Updated by Go MAEDA about 1 month ago

  • Target version set to 4.2.0

Setting the target version to 4.2.0.

#11 Updated by Go MAEDA about 1 month ago

  • Status changed from New to Closed
  • Assignee set to Go MAEDA
  • Resolution set to Fixed

Committed the patch. Thank you all for your contribution.

Also available in: Atom PDF