Project

General

Profile

Actions

Defect #26691

closed

N + 1 queries when rendering the Gantt

Added by jwjw yy over 6 years ago. Updated about 5 years ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Performance
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Wont fix
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.


Files

project.rb (38.8 KB) project.rb jwjw yy, 2017-08-15 03:27
cache_start_due_date.rb.diff (1.97 KB) cache_start_due_date.rb.diff Toshi MARUYAMA, 2017-08-15 10:34
Actions #1

Updated by Toshi MARUYAMA over 6 years ago

  • Description updated (diff)
Actions #2

Updated by Go MAEDA over 6 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?

See also:
Actions #3

Updated by jwjw yy over 6 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)
Actions #4

Updated by Go MAEDA over 6 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" 
Actions #5

Updated by Go MAEDA over 6 years ago

  • Category set to Performance
Actions #6

Updated by Toshi MARUYAMA over 6 years ago

Actions #7

Updated by Toshi MARUYAMA over 6 years ago

  • File deleted (cache_start_due_date.rb)
Actions #8

Updated by jwjw yy over 6 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
Actions #9

Updated by jwjw yy over 6 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.

Actions #10

Updated by Toshi MARUYAMA over 6 years ago

  • Target version set to 4.1.0
Actions #11

Updated by Jean-Philippe Lang about 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.

Actions #12

Updated by Go MAEDA about 5 years ago

  • Status changed from New to Closed
  • Resolution set to Wont fix
Actions

Also available in: Atom PDF