Defect #26691
closed
N + 1 queries when rendering the Gantt
Added by jwjw yy over 7 years ago.
Updated over 5 years ago.
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
- Description updated (diff)
- 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?
See also:
- 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)
- 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"
- Category set to Performance
- File deleted (
cache_start_due_date.rb)
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
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.
- Target version set to 4.1.0
- 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.
- Status changed from New to Closed
- Resolution set to Wont fix
Also available in: Atom
PDF
Updated by Toshi MARUYAMA 
Updated by 
Updated by 
Updated by