From 97347d8c409f14b682dd9ec52ded3c869d0ba479 Mon Sep 17 00:00:00 2001 From: Ryuta Kamizono Date: Wed, 13 Mar 2019 09:29:00 +0900 Subject: Support Optimizer Hints We as Arm Treasure Data are using Optimizer Hints with a monkey patch (https://gist.github.com/kamipo/4c8539f0ce4acf85075cf5a6b0d9712e), especially in order to use `MAX_EXECUTION_TIME` (refer #31129). Example: ```ruby class Job < ApplicationRecord default_scope { optimizer_hints("MAX_EXECUTION_TIME(50000) NO_INDEX_MERGE(jobs)") } end ``` Optimizer Hints is supported not only for MySQL but also for most databases (PostgreSQL on RDS, Oracle, SQL Server, etc), it is really helpful to turn heavy queries for large scale applications. --- activerecord/lib/arel/visitors/depth_first.rb | 1 + activerecord/lib/arel/visitors/dot.rb | 1 + activerecord/lib/arel/visitors/ibm_db.rb | 12 ++++++++++++ activerecord/lib/arel/visitors/informix.rb | 5 +++++ activerecord/lib/arel/visitors/mssql.rb | 13 +++++++++++++ activerecord/lib/arel/visitors/to_sql.rb | 13 +++++++++++++ 6 files changed, 45 insertions(+) (limited to 'activerecord/lib/arel/visitors') diff --git a/activerecord/lib/arel/visitors/depth_first.rb b/activerecord/lib/arel/visitors/depth_first.rb index 92d309453c..892d670c79 100644 --- a/activerecord/lib/arel/visitors/depth_first.rb +++ b/activerecord/lib/arel/visitors/depth_first.rb @@ -35,6 +35,7 @@ module Arel # :nodoc: all alias :visit_Arel_Nodes_Ascending :unary alias :visit_Arel_Nodes_Descending :unary alias :visit_Arel_Nodes_UnqualifiedColumn :unary + alias :visit_Arel_Nodes_OptimizerHints :unary def function(o) visit o.expressions diff --git a/activerecord/lib/arel/visitors/dot.rb b/activerecord/lib/arel/visitors/dot.rb index 6389c875cb..ffcbb7a7ab 100644 --- a/activerecord/lib/arel/visitors/dot.rb +++ b/activerecord/lib/arel/visitors/dot.rb @@ -82,6 +82,7 @@ module Arel # :nodoc: all alias :visit_Arel_Nodes_Offset :unary alias :visit_Arel_Nodes_On :unary alias :visit_Arel_Nodes_UnqualifiedColumn :unary + alias :visit_Arel_Nodes_OptimizerHints :unary alias :visit_Arel_Nodes_Preceding :unary alias :visit_Arel_Nodes_Following :unary alias :visit_Arel_Nodes_Rows :unary diff --git a/activerecord/lib/arel/visitors/ibm_db.rb b/activerecord/lib/arel/visitors/ibm_db.rb index 73166054da..0ffc0725f7 100644 --- a/activerecord/lib/arel/visitors/ibm_db.rb +++ b/activerecord/lib/arel/visitors/ibm_db.rb @@ -4,6 +4,14 @@ module Arel # :nodoc: all module Visitors class IBM_DB < Arel::Visitors::ToSql private + def visit_Arel_Nodes_SelectCore(o, collector) + collector = super + maybe_visit o.optimizer_hints, collector + end + + def visit_Arel_Nodes_OptimizerHints(o, collector) + collector << "/* #{sanitize_as_sql_comment(o).join} */" + end def visit_Arel_Nodes_Limit(o, collector) collector << "FETCH FIRST " @@ -16,6 +24,10 @@ module Arel # :nodoc: all collector = visit [o.left, o.right, 0, 1], collector collector << ")" end + + def collect_optimizer_hints(o, collector) + collector + end end end end diff --git a/activerecord/lib/arel/visitors/informix.rb b/activerecord/lib/arel/visitors/informix.rb index 208fa15aef..cd43be8858 100644 --- a/activerecord/lib/arel/visitors/informix.rb +++ b/activerecord/lib/arel/visitors/informix.rb @@ -42,10 +42,15 @@ module Arel # :nodoc: all collector end + def visit_Arel_Nodes_OptimizerHints(o, collector) + collector << "/*+ #{sanitize_as_sql_comment(o).join(", ")} */" + end + def visit_Arel_Nodes_Offset(o, collector) collector << "SKIP " visit o.expr, collector end + def visit_Arel_Nodes_Limit(o, collector) collector << "FIRST " visit o.expr, collector diff --git a/activerecord/lib/arel/visitors/mssql.rb b/activerecord/lib/arel/visitors/mssql.rb index fdd864b40d..85815baca2 100644 --- a/activerecord/lib/arel/visitors/mssql.rb +++ b/activerecord/lib/arel/visitors/mssql.rb @@ -76,6 +76,15 @@ module Arel # :nodoc: all end end + def visit_Arel_Nodes_SelectCore(o, collector) + collector = super + maybe_visit o.optimizer_hints, collector + end + + def visit_Arel_Nodes_OptimizerHints(o, collector) + collector << "OPTION (#{sanitize_as_sql_comment(o).join(", ")})" + end + def get_offset_limit_clause(o) first_row = o.offset ? o.offset.expr.to_i + 1 : 1 last_row = o.limit ? o.limit.expr.to_i - 1 + first_row : nil @@ -103,6 +112,10 @@ module Arel # :nodoc: all end end + def collect_optimizer_hints(o, collector) + collector + end + def determine_order_by(orders, x) if orders.any? orders diff --git a/activerecord/lib/arel/visitors/to_sql.rb b/activerecord/lib/arel/visitors/to_sql.rb index d0dec63860..7e3e265208 100644 --- a/activerecord/lib/arel/visitors/to_sql.rb +++ b/activerecord/lib/arel/visitors/to_sql.rb @@ -219,6 +219,7 @@ module Arel # :nodoc: all def visit_Arel_Nodes_SelectCore(o, collector) collector << "SELECT" + collector = collect_optimizer_hints(o, collector) collector = maybe_visit o.set_quantifier, collector collect_nodes_for o.projections, collector, SPACE @@ -236,6 +237,10 @@ module Arel # :nodoc: all collector end + def visit_Arel_Nodes_OptimizerHints(o, collector) + collector << "/*+ #{sanitize_as_sql_comment(o).join(" ")} */" + end + def collect_nodes_for(nodes, collector, spacer, connector = COMMA) unless nodes.empty? collector << spacer @@ -799,6 +804,14 @@ module Arel # :nodoc: all @connection.quote_column_name(name) end + def sanitize_as_sql_comment(o) + o.expr.map { |v| v.gsub(%r{ /\*\+?\s* | \s*\*/ }x, "") } + end + + def collect_optimizer_hints(o, collector) + maybe_visit o.optimizer_hints, collector + end + def maybe_visit(thing, collector) return collector unless thing collector << " " -- cgit v1.2.3