https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292020-05-11T14:09:09ZRedmineRedmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=977652020-05-11T14:09:09ZGo MAEDA
<ul><li><strong>Category</strong> changed from <i>Time tracking</i> to <i>Performance</i></li><li><strong>Target version</strong> set to <i>Candidate for next major release</i></li></ul> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=977692020-05-12T00:33:53ZGo MAEDA
<ul></ul><p>Looks good to me.</p> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=977732020-05-12T04:08:31ZAlexander Meindl
<ul></ul><p>My patch has problems with column sort order.</p>
<p>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.</p> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=977752020-05-12T05:57:24ZMarius BÄ‚LTEANU
<ul></ul><p>Alexander Meindl wrote:</p>
<blockquote>
<p>My patch has problems with column sort order.</p>
<p>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.</p>
</blockquote>
<p>Nice feature, but we should have tests for this change.</p> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=977832020-05-12T12:55:20ZAlexander Meindl
<ul><li><strong>File</strong> <a href="/attachments/25413">time_query_performace_v2.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/25413/time_query_performace_v2.patch">time_query_performace_v2.patch</a> added</li></ul><p>Here is my new fixed version.</p>
<p>Adding</p>
<pre><code class="sql syntaxhl"><span class="k">where</span><span class="p">(</span><span class="nv">"#{Enumeration.table_name}.project_id = #{TimeEntry.table_name}.project_id"</span><span class="p">)</span>
</code></pre>
<p>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.</p>
<p>I am not sure, how I can test this change with an additional test. Existing test should work, of course.</p> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=977842020-05-12T14:24:37ZAlexander Meindl
<ul><li><strong>File</strong> <a href="/attachments/25414">time_query_performace_v3.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/25414/time_query_performace_v3.patch">time_query_performace_v3.patch</a> added</li></ul><p>And again, now all tests are running.</p>
<p>With my test data, /time_entries without filters</p>
<ul>
<li>without patch: ActiveRecord: 345038.1ms</li>
<li>with patch ActiveRecord: 6038.4ms</li>
</ul>
<p>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.</p> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=1085922022-11-24T11:26:36ZAlexander Meindl
<ul><li><strong>File</strong> <a href="/attachments/29936">time_query_performace_v4.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/29936/time_query_performace_v4.patch">time_query_performace_v4.patch</a> added</li></ul><p>2 years later, a new patch introduce skipping joins for count time entries, which are not required. Test added.</p>
<p>All tests run successful with this changes.</p> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=1085932022-11-24T11:36:51ZAlexander Meindl
<ul><li><strong>File</strong> <a href="/attachments/29937">time_query_performace_v5.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/29937/time_query_performace_v5.patch">time_query_performace_v5.patch</a> added</li></ul><p>New version of patch has some optimization for count with activities.</p> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=1085992022-11-26T06:22:54ZOmega Code
<ul></ul><p>on the ther hand (in regard to <a class="issue tracker-1 status-1 priority-4 priority-default" title="Defect: Missing where cause for allowed_to_condition (New)" href="https://www.redmine.org/issues/37962">#37962</a>) this patch helped a lot under Redmine 4.2.8 (mysql 8):<br />time for time entries queries was reduced approx by 40% for me.<br />thank you!</p> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=1090792023-01-13T23:12:16ZJohn Ramsden
<ul></ul><p>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.</p> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=1090962023-01-15T23:34:55ZGo MAEDA
<ul><li><strong>Target version</strong> changed from <i>Candidate for next major release</i> to <i>5.1.0</i></li></ul> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=1099132023-05-04T09:59:07Zsly sly
<ul></ul><p>I have a problem with the v5 patch.<br />If you move an issue to another project, the project_id no longer matches the request and is ignored when displaying the numbers.</p>
<p>An example:<br />Issue 2982 belonged to project with ID = 8 when the time entry 34446 was created and then was moved in another project ID = 17</p>
<pre><code class="shell syntaxhl">MariaDB <span class="o">[</span>redmine_import]> <span class="k">select </span>te.<span class="k">*</span>,projects.id,enumerations.id,enumerations.project_id,enumerations.type,users.id,users.type,projects.id FROM time_entries AS te INNER JOIN <span class="sb">`</span>projects<span class="sb">`</span> ON <span class="sb">`</span>projects<span class="sb">`</span>.<span class="sb">`</span><span class="nb">id</span><span class="sb">`</span> <span class="o">=</span> te.project_id INNER JOIN enumerations ON enumerations.id <span class="o">=</span> te.activity_id AND enumerations.type <span class="o">=</span> <span class="s1">'TimeEntryActivity'</span> INNER JOIN <span class="nb">users </span>ON users.id <span class="o">=</span> te.user_id AND users.type IN <span class="o">(</span><span class="s1">'User'</span>, <span class="s1">'AnonymousUser'</span><span class="o">)</span> WHERE te.id<span class="o">=</span>34446<span class="p">;</span>
+-------+------------+-----------+---------+----------+-------+------------------------------------+-------------+------------+-------+--------+-------+---------------------+---------------------+---------+------+----+-----+------------+-------------------+----+------+----+
| <span class="nb">id</span> | project_id | author_id | user_id | issue_id | hours | comments | activity_id | spent_on | tyear | tmonth | tweek | created_on | updated_on | rate_id | cost | <span class="nb">id</span> | <span class="nb">id</span> | project_id | <span class="nb">type</span> | <span class="nb">id</span> | <span class="nb">type</span> | <span class="nb">id</span> |
+-------+------------+-----------+---------+----------+-------+------------------------------------+-------------+------------+-------+--------+-------+---------------------+---------------------+---------+------+----+-----+------------+-------------------+----+------+----+
| 34446 | 17 | 12 | 12 | 2982 | 4 | Fix alma container issue <span class="k">for </span>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 <span class="k">in </span><span class="nb">set</span> <span class="o">(</span>0.001 sec<span class="o">)</span>
</code></pre>
<p>So the DB queries was KO and doesn't display the good numbers, for an hotfix we just removed <br /><pre><code class="ruby syntaxhl"><span class="k">def</span> <span class="nf">base_result_scope</span>
<span class="n">base_scope</span><span class="p">.</span><span class="nf">joins</span><span class="p">(</span><span class="ss">:activity</span><span class="p">,</span> <span class="ss">:user</span><span class="p">)</span>
<span class="p">.</span><span class="nf">includes</span><span class="p">(</span><span class="ss">:activity</span><span class="p">)</span>
<span class="p">.</span><span class="nf">left_join_issue</span>
<span class="c1"># XXX - remove to let time entry report working fine</span>
<span class="c1"># .where(activity_scope_sql)</span>
<span class="k">end</span>
</code></pre></p> Redmine - Patch #33431: Better performance for Time entries without issue and activity filtershttps://www.redmine.org/issues/33431?journal_id=1110282023-10-21T01:02:31ZGo MAEDA
<ul><li><strong>Target version</strong> changed from <i>5.1.0</i> to <i>6.0.0</i></li></ul>