Patch #37565

Performance problem when filtering issues by custom-field value

Added by Vincent Robert about 1 month ago. Updated 6 days ago.

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

0%

Category:Performance
Target version:-

Description

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.

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])

patch.diff Magnifier (2.27 KB) Vincent Robert, 2022-08-09 15:22


Related issues

Duplicates Redmine - Defect #37268: Performance problem with Redmine 4.2.7 and 5.0.2 Closed

History

#1 Updated by Vincent Robert about 1 month ago

  • Description updated (diff)

#2 Updated by Mischa The Evil about 1 month ago

  • Duplicates Defect #37268: Performance problem with Redmine 4.2.7 and 5.0.2 added

#3 Updated by Mischa The Evil about 1 month ago

I am setting this issue as a duplicate because the problem has already been reported as #37268. Please take a look at it and continue any general discussion on the matter on that issue.

P.s. I keep this issue open because it includes a patch which might be discussed separately and independently.

#4 Updated by Marius BALTEANU about 1 month ago

Vincent, thanks for providing a fix for this performance issue. Can you take a look also on the latest patch posted by Felix on #37268?

#5 Updated by Marius BALTEANU 6 days ago

  • Status changed from New to Closed

Fix merged to stable branches and the new releases will be published this weekend.

Also available in: Atom PDF