Patch #33431
Better performance for Time entries without issue and activity filters
| Status: | New | Start date: | ||
|---|---|---|---|---|
| Priority: | Normal | Due date: | ||
| Assignee: | - | % Done: | 0% | |
| Category: | Performance | |||
| Target version: | Candidate for next major release |
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.
time_query_performace.patch 
(1.64 KB)
time_query_performace_v2.patch
(790 Bytes)
time_query_performace_v3.patch
(591 Bytes)
time_query_performace_v4.patch
(3.97 KB)
time_query_performace_v5.patch
(4.03 KB)
History
#1
Updated by Go MAEDA over 2 years ago
- Category changed from Time tracking to Performance
- Target version set to Candidate for next major release
#2
Updated by Go MAEDA over 2 years ago
Looks good to me.
#3
Updated by Alexander Meindl over 2 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.
#4
Updated by Marius BALTEANU over 2 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.
#5
Updated by Alexander Meindl over 2 years ago
- File time_query_performace_v2.patch added
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.
#6
Updated by Alexander Meindl over 2 years ago
- File time_query_performace_v3.patch added
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.
#7
Updated by Alexander Meindl 8 days ago
- File time_query_performace_v4.patch added
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.
#8
Updated by Alexander Meindl 8 days ago
- File time_query_performace_v5.patch added
New version of patch has some optimization for count with activities.
#9
Updated by Omega Code 6 days 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!