Project

General

Profile

Actions

Defect #33289

closed

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

Added by shigeo teraoka almost 4 years ago. Updated over 3 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Performance
Target version:
Start date:
Due date:
% Done:

0%

Estimated time:
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の性能が低下します。


Files

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

Updated by shigeo teraoka over 3 years 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.

Actions #2

Updated by Go MAEDA over 3 years ago

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

Actions #3

Updated by shigeo teraoka over 3 years ago

Redmine has 800 active projects.

Actions #4

Updated by Mizuki ISHIKAWA over 3 years 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)

Actions #5

Updated by shigeo teraoka over 3 years ago

Thank you for your analysis.

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

Actions #6

Updated by Mizuki ISHIKAWA over 3 years 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)

Actions #7

Updated by Go MAEDA over 3 years 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
Actions #8

Updated by Stoyan Zhekov over 3 years 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.

Actions #9

Updated by Mizuki ISHIKAWA over 3 years 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.

Actions #10

Updated by Go MAEDA over 3 years ago

  • Target version set to 4.2.0

Setting the target version to 4.2.0.

Actions #11

Updated by Go MAEDA over 3 years 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.

Actions

Also available in: Atom PDF