Patch #36503

Reduce extra queries in IssueQuery.default

Added by Go MAEDA 4 months ago. Updated about 1 month ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Go MAEDA% Done:

0%

Category:Performance
Target version:-

Description

The attached patch reduces the total number of SQL queries during the execution of IssueQuery.default.

No default queries in any levels:

before:

  UserPreference Load (0.2ms)  SELECT "user_preferences".* FROM "user_preferences" WHERE "user_preferences"."user_id" = ? LIMIT ?  [["user_id", 1], ["LIMIT", 1]]
  ↳ app/models/user.rb:394:in `pref'
  IssueQuery Load (0.1ms)  SELECT "queries".* FROM "queries" WHERE "queries"."type" = ? AND "queries"."id" IS NULL LIMIT ?  [["type", "IssueQuery"], ["LIMIT", 1]]
  ↳ app/models/issue_query.rb:83:in `default'
  Setting Load (0.1ms)  SELECT "settings".* FROM "settings" WHERE "settings"."name" = ? ORDER BY "settings"."id" DESC LIMIT ?  [["name", "default_issue_query"], ["LIMIT", 1]]
  ↳ app/models/setting.rb:360:in `find_or_default'
  IssueQuery Load (0.1ms)  SELECT "queries".* FROM "queries" WHERE "queries"."type" = ? AND "queries"."id" = ? LIMIT ?  [["type", "IssueQuery"], ["id", nil], ["LIMIT", 1]]
  ↳ app/models/issue_query.rb:86:in `default'

after:

  UserPreference Load (0.1ms)  SELECT "user_preferences".* FROM "user_preferences" WHERE "user_preferences"."user_id" = ? LIMIT ?  [["user_id", 1], ["LIMIT", 1]]
  ↳ app/models/user.rb:394:in `pref'
  Setting Load (0.1ms)  SELECT "settings".* FROM "settings" WHERE "settings"."name" = ? ORDER BY "settings"."id" DESC LIMIT ?  [["name", "default_issue_query"], ["LIMIT", 1]]
  ↳ app/models/setting.rb:360:in `find_or_default'

A project-level default query defined:

before:

  UserPreference Load (0.2ms)  SELECT "user_preferences".* FROM "user_preferences" WHERE "user_preferences"."user_id" = ? LIMIT ?  [["user_id", 1], ["LIMIT", 1]]
  ↳ app/models/user.rb:394:in `pref'
  IssueQuery Load (0.2ms)  SELECT "queries".* FROM "queries" WHERE "queries"."type" = ? AND "queries"."id" IS NULL LIMIT ?  [["type", "IssueQuery"], ["LIMIT", 1]]
  ↳ app/models/issue_query.rb:83:in `default'
  IssueQuery Load (0.2ms)  SELECT "queries".* FROM "queries" WHERE "queries"."type" = ? AND "queries"."id" = ? LIMIT ?  [["type", "IssueQuery"], ["id", 4], ["LIMIT", 1]]
  ↳ app/models/issue_query.rb:85:in `default'

after:

  UserPreference Load (0.2ms)  SELECT "user_preferences".* FROM "user_preferences" WHERE "user_preferences"."user_id" = ? LIMIT ?  [["user_id", 1], ["LIMIT", 1]]
  ↳ app/models/user.rb:394:in `pref'
  IssueQuery Load (0.2ms)  SELECT "queries".* FROM "queries" WHERE "queries"."type" = ? AND "queries"."id" = ? LIMIT ?  [["type", "IssueQuery"], ["id", 4], ["LIMIT", 1]]
  ↳ app/models/issue_query.rb:87:in `default'

improve_issue_query_default.patch Magnifier (825 Bytes) Go MAEDA, 2022-01-23 08:23


Related issues

Related to Redmine - Feature #7360: Issue custom query: default query per instance, project a... Closed 2011-01-18

Associated revisions

Revision 21387
Added by Go MAEDA 4 months ago

Reduce extra database queries in IssueQuery.default (#7360, #36503).

Revision 21480
Added by Marius BALTEANU about 1 month ago

Do not fire a query to get user default query when user has no default query set (#36503).

Patch by Dmitry Makurin.

Revision 21485
Added by Go MAEDA about 1 month ago

Do not fire a query to get user default query when user has no default query set (#36503).

Patch by Dmitry Makurin.

History

#1 Updated by Go MAEDA 4 months ago

  • Tracker changed from Defect to Patch

#2 Updated by Go MAEDA 4 months ago

  • Related to Feature #7360: Issue custom query: default query per instance, project and user added

#3 Updated by Go MAEDA 4 months ago

  • Status changed from New to Closed
  • Assignee set to Go MAEDA

Committed the patch as a part of #7360.

#4 Updated by Dmitry Makurin 4 months ago

Isn't there https://www.redmine.org/projects/redmine/repository/entry/trunk/app/models/issue_query.rb#L83 also supposed to be a present? at the end of second condition?

If (query_id = user.pref.default_issue_query) equals to "" the query stil fires.

diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb
index 5a3e14474..e7ac1c821 100644
--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -80,7 +80,7 @@ class IssueQuery < Query
   def self.default(project: nil, user: User.current)
     query = nil
     # user default
-    if user&.logged? && (query_id = user.pref.default_issue_query)
+    if user&.logged? && (query_id = user.pref.default_issue_query).present?
       query = find_by(id: query_id)
     end
     # project default

#5 Updated by Marius BALTEANU about 1 month ago

  • Status changed from Closed to Reopened

#6 Updated by Marius BALTEANU about 1 month ago

  • Status changed from Reopened to Closed

Dmitry Makurin wrote:

Isn't there https://www.redmine.org/projects/redmine/repository/entry/trunk/app/models/issue_query.rb#L83 also supposed to be a present? at the end of second condition?

If (query_id = user.pref.default_issue_query) equals to "" the query stil fires.

[...]

Committed the patch, thanks for pointing this out.

Also available in: Atom PDF