Patch #37565
Updated by Vincent Robert over 1 year ago
Hello
We noticed a significant impact on performances in the latest versions, after the fix of the issue described in #37255.
Some queries, which previously run in a few milliseconds, now take more than 60 seconds ; specifically when filtering issues by custom value.
Digging into the generated SQL queries, it appears some visibility conditions are duplicated in sub-queries and should, sometimes, be skipped to maintain good performances.
So, here is a patch to address this issue.
Thank you for reviewing it.
<pre><code class="diff">
diff --git a/app/models/issue_custom_field.rb b/app/models/issue_custom_field.rb
index 285a0d48c..57e2cc8a7 100644
--- a/app/models/issue_custom_field.rb
+++ b/app/models/issue_custom_field.rb
@@ -40,6 +40,7 @@ class IssueCustomField < CustomField
" OR #{Issue.table_name}.project_id IN (SELECT project_id FROM #{table_name_prefix}custom_fields_projects#{table_name_suffix} WHERE custom_field_id = #{id_column})"
-
"((#{sql}) AND (#{tracker_condition}) AND (#{project_condition}) AND (#{Issue.visible_condition(user)}))"
+ "((#{sql}) AND (#{tracker_condition}) AND (#{project_condition}) AND (#{Issue.visible_condition(user, {skip_pre_condition: true})}))"
end
def validate_custom_field
diff --git a/app/models/project.rb b/app/models/project.rb
index 2d2ff5748..0992f4c8f 100644
--- a/app/models/project.rb
+++ b/app/models/project.rb
@@ -178,18 +178,21 @@ class Project < ActiveRecord::Base
# * :member => true limit the condition to the user projects
def self.allowed_to_condition(user, permission, options={})
perm = Redmine::AccessControl.permission(permission)
- base_statement =
- if perm && perm.read?
+ if options[:skip_pre_condition]
+ base_statement = "1=1"
+ else
+ base_statement = if perm && perm.read?
"#{Project.table_name}.status <> #{Project::STATUS_ARCHIVED}"
else
"#{Project.table_name}.status = #{Project::STATUS_ACTIVE}"
end
- if !options[:skip_pre_condition] && perm && perm.project_module
- # If the permission belongs to a project module, make sure the module is enabled
- base_statement +=
- " AND EXISTS (SELECT 1 AS one FROM #{EnabledModule.table_name} em" \
- " WHERE em.project_id = #{Project.table_name}.id" \
- " AND em.name='#{perm.project_module}')"
+ if perm && perm.project_module
+ # If the permission belongs to a project module, make sure the module is enabled
+ base_statement +=
+ " AND EXISTS (SELECT 1 AS one FROM #{EnabledModule.table_name} em" \
+ " WHERE em.project_id = #{Project.table_name}.id" \
+ " AND em.name='#{perm.project_module}')"
+ end
end
if project = options[:project]
project_statement = project.project_condition(options[:with_subprojects])
</code></pre>