Defect #26691
closed
N + 1 queries when rendering the Gantt
0%
Description
Rails 4.2.7.1
20000 issues in the database
When rendering the GanttsController.show action, it will issue a lot of queries to get the the start_date and due_date of an project like:
SELECT MIN(`issues`.`start_date`) FROM `issues` WHERE `issues`.`project_id` = ? [["project_id", 1436]]
SELECT MAX(`issues`.`due_date`) FROM `issues` WHERE `issues`.`project_id` = ? [["project_id", 1436]]
SELECT MIN(`issues`.`start_date`) FROM `issues` WHERE `issues`.`project_id` = ? [["project_id", 1317]]
SELECT MAX(`issues`.`due_date`) FROM `issues` WHERE `issues`.`project_id` = ? [["project_id", 1317]]
Which will make the performance very bad.
My solution is that add cached_due_date and cached_start_date for projects.rb, and while constructing the project_tree, I will use a group query to retrieve the corresponding start_date and due_date for a group of projects, so that they don't need to issue N queries later on.
Files
Updated by Go MAEDA over 7 years ago
- Status changed from New to Needs feedback
Thank you for pointing out the problem and sharing your work.
Could you provide a patch file instead of project.rb?
Updated by jwjw yy over 7 years ago
- File cache_start_due_date.rb added
here is the patch, what I have done is to calculate the start date and due date in a group queries.
start_dates = Issue.where(project_id: projects).group(:project_id).minimum(:start_date)
due_dates = Issue.where(project_id: projects).group(:project_id).maximum(:due_date)
Updated by Go MAEDA over 7 years ago
- Status changed from Needs feedback to New
Thank you for providing the patch.
It is interesting but IMHO, the cache should be invalidated when an issue or a version in the project is updated. With the patch applied, Project#start_date and Project#due_date returns a wrong value under some conditions. Please see the following example.
project = Project.first
Project.project_tree([project]) {}
p project.due_date
# => Mon, 04 Sep 2017
issue = project.issues.last
issue.update(due_date: Date.new(2017, 9, 30))
p issue.due_date
# => Sat, 30 Sep 2017
p project.due_date
# => Mon, 04 Sep 2017 !!! The value should be "Sat, 30 Sep 2017"
Updated by Toshi MARUYAMA over 7 years ago
Updated by Toshi MARUYAMA over 7 years ago
- File deleted (
cache_start_due_date.rb)
Updated by jwjw yy over 7 years ago
If there is only problem, then some updated issue can be applied to project with the update of issue, such as:
if issue.project.cached_due_date > issue.due_date
issue.project.cached_due_date = issue.due_date
end
Updated by jwjw yy over 7 years ago
Another thing is that, if the issue is updated, then the GanttsController.index action page needs to be refresh, which will reconstruct the project_tree, so there will no problem for the rendering.
Updated by Jean-Philippe Lang over 5 years ago
- Target version deleted (
4.1.0)
jwjw yy wrote:
Rails 4.2.7.1
20000 issues in the database
When rendering the GanttsController.show action, it will issue a lot of queries to get the the start_date and due_date of an project like:
These queries are executed once per project, not for each one of your 20k issues. I think that having hundreds of projects on the gantt chart is pretty uncommon, I'd prefer to keep it simple and not add this kind of cache at the projet level.
Updated by Go MAEDA over 5 years ago
- Status changed from New to Closed
- Resolution set to Wont fix