Redmine

[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.

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)

Thank you for your analysis.

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

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.

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.