Project

General

Profile

Actions

Patch #33431

open

Better performance for Time entries without issue and activity filters

Added by Alexander Meindl almost 4 years ago. Updated 4 months ago.

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

0%

Estimated time:

Description

At the moment all time entry queries join issue and activities. If there is a large number of time entries and issues, this is a big performance issue.

Some of our customers have more than 500.000 time entries and even more issues. A query without a time filter requires minutes. With this attached patch this improves it to some seconds.

Issue and activity joins are only add, if they are required.

It would be great, to get this in Redmine.


Files

time_query_performace.patch (1.64 KB) time_query_performace.patch Alexander Meindl, 2020-05-11 13:47
time_query_performace_v2.patch (790 Bytes) time_query_performace_v2.patch Alexander Meindl, 2020-05-12 14:49
time_query_performace_v3.patch (591 Bytes) time_query_performace_v3.patch Alexander Meindl, 2020-05-12 16:21
time_query_performace_v4.patch (3.97 KB) time_query_performace_v4.patch Alexander Meindl, 2022-11-24 12:23
time_query_performace_v5.patch (4.03 KB) time_query_performace_v5.patch Alexander Meindl, 2022-11-24 12:35
Actions #1

Updated by Go MAEDA almost 4 years ago

  • Category changed from Time tracking to Performance
  • Target version set to Candidate for next major release
Actions #2

Updated by Go MAEDA almost 4 years ago

Looks good to me.

Actions #3

Updated by Alexander Meindl almost 4 years ago

My patch has problems with column sort order.

But I found the real problem now. It is a missing condition to project_id for Enumeration. I am working on a new patch to fix this problem. Please do not commit the current patch.

Actions #4

Updated by Marius BĂLTEANU almost 4 years ago

Alexander Meindl wrote:

My patch has problems with column sort order.

But I found the real problem now. It is a missing condition to project_id for Enumeration. I am working on a new patch to fix this problem. Please do not commit the current patch.

Nice feature, but we should have tests for this change.

Actions #5

Updated by Alexander Meindl almost 4 years ago

Here is my new fixed version.

Adding

where("#{Enumeration.table_name}.project_id = #{TimeEntry.table_name}.project_id")

fixed the problem. But I moved activity join from base_scope to results_scope, because as I understood it, activity join is not required for totals and count.

I am not sure, how I can test this change with an additional test. Existing test should work, of course.

Actions #6

Updated by Alexander Meindl almost 4 years ago

And again, now all tests are running.

With my test data, /time_entries without filters

  • without patch: ActiveRecord: 345038.1ms
  • with patch ActiveRecord: 6038.4ms

This is not perfect, but way better than before. At the moment I have no idea how it can be more improved. The problem is table layout of enumerations table.

Actions #7

Updated by Alexander Meindl over 1 year ago

2 years later, a new patch introduce skipping joins for count time entries, which are not required. Test added.

All tests run successful with this changes.

Actions #8

Updated by Alexander Meindl over 1 year ago

New version of patch has some optimization for count with activities.

Actions #9

Updated by Omega Code about 1 year ago

on the ther hand (in regard to #37962) this patch helped a lot under Redmine 4.2.8 (mysql 8):
time for time entries queries was reduced approx by 40% for me.
thank you!

Actions #10

Updated by John Ramsden about 1 year ago

We have hundreds of thousands of time entries and redmine became almost unusable recently, this patch made a huge difference to us. It would be great if it can be pushed into an upcoming release.

Actions #11

Updated by Go MAEDA about 1 year ago

  • Target version changed from Candidate for next major release to 5.1.0
Actions #12

Updated by sly sly 10 months ago

I have a problem with the v5 patch.
If you move an issue to another project, the project_id no longer matches the request and is ignored when displaying the numbers.

An example:
Issue 2982 belonged to project with ID = 8 when the time entry 34446 was created and then was moved in another project ID = 17

MariaDB [redmine_import]> select te.*,projects.id,enumerations.id,enumerations.project_id,enumerations.type,users.id,users.type,projects.id FROM time_entries AS te       INNER JOIN `projects` ON `projects`.`id` = te.project_id     INNER JOIN enumerations ON enumerations.id = te.activity_id AND enumerations.type = 'TimeEntryActivity'          INNER JOIN users ON users.id = te.user_id AND users.type IN ('User', 'AnonymousUser')      WHERE te.id=34446;
+-------+------------+-----------+---------+----------+-------+------------------------------------+-------------+------------+-------+--------+-------+---------------------+---------------------+---------+------+----+-----+------------+-------------------+----+------+----+
| id    | project_id | author_id | user_id | issue_id | hours | comments                           | activity_id | spent_on   | tyear | tmonth | tweek | created_on          | updated_on          | rate_id | cost | id | id  | project_id | type              | id | type | id |
+-------+------------+-----------+---------+----------+-------+------------------------------------+-------------+------------+-------+--------+-------+---------------------+---------------------+---------+------+----+-----+------------+-------------------+----+------+----+
| 34446 |         17 |        12 |      12 |     2982 |     4 | Fix alma container issue for devel |         172 | 2021-10-04 |  2021 |     10 |    40 | 2021-10-05 07:59:26 | 2021-10-05 07:59:26 |    NULL | NULL | 17 | 172 |          8 | TimeEntryActivity | 12 | User | 17 |
+-------+------------+-----------+---------+----------+-------+------------------------------------+-------------+------------+-------+--------+-------+---------------------+---------------------+---------+------+----+-----+------------+-------------------+----+------+----+
1 row in set (0.001 sec)

So the DB queries was KO and doesn't display the good numbers, for an hotfix we just removed

def base_result_scope
  base_scope.joins(:activity, :user)
            .includes(:activity)
            .left_join_issue
          # XXX - remove to let time entry report working fine
          # .where(activity_scope_sql)
end

Actions #13

Updated by Go MAEDA 4 months ago

  • Target version changed from 5.1.0 to 6.0.0
Actions

Also available in: Atom PDF