[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.
--------------------------------------------------------------------------------
Teraoka-san, how many projects does the Redmine have?
--------------------------------------------------------------------------------
Redmine has 800 active projects.
--------------------------------------------------------------------------------
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.
<pre>
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)
</pre>
--------------------------------------------------------------------------------
Thank you for your analysis.

If SQL is executed 66 times, it will take time to update.
Is it possible to improve it?
--------------------------------------------------------------------------------
I am attaching a patch to improve this situation.

Probably "Enumaration#update_position":https://www.redmine.org/projects/redmine/repository/revisions/19983/entry/trunk/app/models/enumeration.rb#L150 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.
<pre>
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)
</pre>
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
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:

<pre>
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
</pre>

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.
--------------------------------------------------------------------------------
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.
--------------------------------------------------------------------------------
Setting the target version to 4.2.0.
--------------------------------------------------------------------------------
Committed the patch. Thank you all for your contribution.
--------------------------------------------------------------------------------