Defect #26691

N + 1 queries when rendering the Gantt

Added by jwjw yy about 1 month ago. Updated about 1 month ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Performance
Target version:4.1.0
Resolution: Affected version:

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.

project.rb Magnifier (38.8 KB) jwjw yy, 2017-08-15 03:27

cache_start_due_date.rb.diff Magnifier (1.97 KB) Toshi MARUYAMA, 2017-08-15 10:34

History

#1 Updated by Toshi MARUYAMA about 1 month ago

  • Description updated (diff)

#2 Updated by Go MAEDA about 1 month 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?

See also:

#3 Updated by jwjw yy about 1 month 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)

#4 Updated by Go MAEDA about 1 month 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" 

#5 Updated by Go MAEDA about 1 month ago

  • Category set to Performance

#7 Updated by Toshi MARUYAMA about 1 month ago

  • File deleted (cache_start_due_date.rb)

#8 Updated by jwjw yy about 1 month 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

#9 Updated by jwjw yy about 1 month 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.

#10 Updated by Toshi MARUYAMA about 1 month ago

  • Target version set to 4.1.0

Also available in: Atom PDF