Details
-
Type:
Improvement
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 4.9.0-b1
-
Component/s: None
-
Labels:None
Description
The Payment Reminders cron task (and possibly the Autodebit one) can be slow for some users when it fetches invoices for autodebit. This is because a union takes place for client settings via Invoices::getClientSettingSubquery. An EXPLAIN shows that the query uses filesort to order the result set, but the result set shouldn't need to be ordered. That can be alleviated by updating each union query to specifically ORDER BY NULL. However, MySQL will ignore ORDER BY NULL unless there is a LIMIT clause, so a LIMIT clause will need to be added with a sufficiently large number to return all results. This should be set to the maximum integer size if available, e.g. LIMIT 9999999999.
The end result should also have an order/limit clause to avoid filesorts in the query plan.
There are other places we perform unions for queries, such as Clients and Client Groups models. These may also benefit from the order by/limit clause additions. Testing will need to be done with a large data set for a more realistic comparison.
Activity
Tyson Phillips (Inactive)
created issue -
Tyson Phillips (Inactive)
made changes -
| Field | Original Value | New Value |
|---|---|---|
| Rank | Ranked higher |
Tyson Phillips (Inactive)
made changes -
| Story Points | 5 |
Tyson Phillips (Inactive)
made changes -
| Sprint | 4.9.0 Sprint 3 [ 101 ] |
Tyson Phillips (Inactive)
made changes -
| Rank | Ranked higher |
Tyson Phillips (Inactive)
made changes -
| Description |
The Payment Reminders cron task (and possibly the Autodebit one) can be slow for some users when it fetches invoices for autodebit. This is because a union takes place for client settings via _Invoices::getClientSettingSubquery_. An EXPLAIN shows that the query uses filesort to order the result set, but the result set shouldn't need to be ordered. That can be alleviated by updating each union query to specifically ORDER BY NULL. However, MySQL will ignore ORDER BY NULL unless there is a LIMIT clause, so a LIMIT clause will need to be added with a sufficiently large number to return all results. This should be set to the maximum integer size if available, e.g. LIMIT 9999999999.
There are other places we perform unions for queries, such as Clients and Client Groups models. These may also benefit from the order by/limit clause additions. Testing will need to be done with a large data set for a more realistic comparison. |
The Payment Reminders cron task (and possibly the Autodebit one) can be slow for some users when it fetches invoices for autodebit. This is because a union takes place for client settings via _Invoices::getClientSettingSubquery_. An EXPLAIN shows that the query uses filesort to order the result set, but the result set shouldn't need to be ordered. That can be alleviated by updating each union query to specifically ORDER BY NULL. However, MySQL will ignore ORDER BY NULL unless there is a LIMIT clause, so a LIMIT clause will need to be added with a sufficiently large number to return all results. This should be set to the maximum integer size if available, e.g. LIMIT 9999999999.
The end result should also have an order/limit clause to avoid filesorts in the query plan. There are other places we perform unions for queries, such as Clients and Client Groups models. These may also benefit from the order by/limit clause additions. Testing will need to be done with a large data set for a more realistic comparison. |
Tyson Phillips (Inactive)
made changes -
| Sprint | 4.9.0 Sprint 3 [ 101 ] | 4.9.0 Sprint 3, 4.9.0 Sprint 4 [ 101, 102 ] |
Tyson Phillips (Inactive)
made changes -
| Rank | Ranked higher |
| Assignee | Jonathan Reissmueller [ jonathan ] |
Automated transition triggered when Jonathan Reissmueller created a branch in Stash -
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Remaining Estimate | 0 minutes [ 0 ] | |
| Time Spent | 5 hours, 27 minutes [ 19620 ] | |
| Worklog Id | 13212 [ 13212 ] |
Automated transition triggered when Jonathan Reissmueller created pull request #797 in Stash -
| Status | In Progress [ 3 ] | In Review [ 5 ] |
| Resolution | Fixed [ 1 ] |
Tyson Phillips (Inactive)
made changes -
| Sprint | 4.9.0 Sprint 3, 4.9.0 Sprint 4 [ 101, 102 ] | 4.9.0 Sprint 3, 4.9.0 Sprint 4, 4.9.0 Sprint 5 [ 101, 102, 103 ] |
Tyson Phillips (Inactive)
made changes -
| Rank | Ranked higher |
| Time Spent | 5 hours, 27 minutes [ 19620 ] | 6 hours, 5 minutes [ 21900 ] |
| Worklog Id | 13233 [ 13233 ] |
Automated transition triggered when Tyson Phillips (Inactive) merged pull request #797 in Stash -
| Status | In Review [ 5 ] | Closed [ 6 ] |
