From 0ad70eb2d063cab577a559f6c3d28e787ca1dca8 Mon Sep 17 00:00:00 2001 From: Ryuta Kamizono Date: Sun, 17 Mar 2019 06:06:21 +0900 Subject: Make `truncate_tables` to bulk statements Before: ``` (16.4ms) TRUNCATE TABLE `author_addresses` (20.5ms) TRUNCATE TABLE `authors` (19.4ms) TRUNCATE TABLE `posts` ``` After: ``` Truncate Tables (19.5ms) TRUNCATE TABLE `author_addresses`; TRUNCATE TABLE `authors`; TRUNCATE TABLE `posts` ``` --- .../abstract/database_statements.rb | 44 ++++++++++++++++----- .../connection_adapters/abstract_mysql_adapter.rb | 33 ---------------- .../mysql/database_statements.rb | 46 ++++++++++++++++++++++ .../postgresql/database_statements.rb | 10 ++--- .../sqlite3/database_statements.rb | 31 +++++++++++++++ .../connection_adapters/sqlite3_adapter.rb | 6 +-- .../lib/active_record/tasks/database_tasks.rb | 4 +- 7 files changed, 119 insertions(+), 55 deletions(-) create mode 100644 activerecord/lib/active_record/connection_adapters/sqlite3/database_statements.rb (limited to 'activerecord/lib/active_record') diff --git a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb index bfd1c8402c..0cf0290770 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb @@ -144,11 +144,20 @@ module ActiveRecord # Executes the truncate statement. def truncate(table_name, name = nil) - execute "TRUNCATE TABLE #{quote_table_name(table_name)}", name + execute(build_truncate_statements(table_name), name) end def truncate_tables(*table_names) # :nodoc: - table_names.each { |table_name| truncate(table_name) } + unless table_names.empty? + with_multi_statements do + disable_referential_integrity do + Array(build_truncate_statements(*table_names)).each do |sql| + execute_batch(sql, "Truncate Tables") + yield if block_given? + end + end + end + end end # Executes update +sql+ statement in the context of this connection using @@ -378,13 +387,15 @@ module ActiveRecord end.compact table_deletes = tables_to_delete.map { |table| +"DELETE FROM #{quote_table_name table}" } - total_sql = Array.wrap(combine_multi_statements(table_deletes + fixture_inserts)) - - disable_referential_integrity do - transaction(requires_new: true) do - total_sql.each do |sql| - execute sql, "Fixtures Load" - yield if block_given? + total_sql = Array(combine_multi_statements(table_deletes + fixture_inserts)) + + with_multi_statements do + disable_referential_integrity do + transaction(requires_new: true) do + total_sql.each do |sql| + execute_batch(sql, "Fixtures Load") + yield if block_given? + end end end end @@ -420,6 +431,10 @@ module ActiveRecord end private + def execute_batch(sql, name = nil) + execute(sql, name) + end + def default_insert_value(column) Arel.sql("DEFAULT") end @@ -455,6 +470,17 @@ module ActiveRecord manager.to_sql end + def build_truncate_statements(*table_names) + truncate_tables = table_names.map do |table_name| + "TRUNCATE TABLE #{quote_table_name(table_name)}" + end + combine_multi_statements(truncate_tables) + end + + def with_multi_statements + yield + end + def combine_multi_statements(total_sql) total_sql.join(";\n") end diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb index 5ceddf449c..8ca2cfa9ed 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -512,12 +512,6 @@ module ActiveRecord index.using == :btree || super end - def insert_fixtures_set(fixture_set, tables_to_delete = []) - with_multi_statements do - super { discard_remaining_results } - end - end - def build_insert_sql(insert) # :nodoc: sql = +"INSERT #{insert.into} #{insert.values_list}" @@ -539,33 +533,6 @@ module ActiveRecord end end - def combine_multi_statements(total_sql) - total_sql.each_with_object([]) do |sql, total_sql_chunks| - previous_packet = total_sql_chunks.last - sql << ";\n" - if max_allowed_packet_reached?(sql, previous_packet) || total_sql_chunks.empty? - total_sql_chunks << sql - else - previous_packet << sql - end - end - end - - def max_allowed_packet_reached?(current_packet, previous_packet) - if current_packet.bytesize > max_allowed_packet - raise ActiveRecordError, "Fixtures set is too large #{current_packet.bytesize}. Consider increasing the max_allowed_packet variable." - elsif previous_packet.nil? - false - else - (current_packet.bytesize + previous_packet.bytesize) > max_allowed_packet - end - end - - def max_allowed_packet - bytes_margin = 2 - @max_allowed_packet ||= (show_variable("max_allowed_packet") - bytes_margin) - end - def initialize_type_map(m = type_map) super diff --git a/activerecord/lib/active_record/connection_adapters/mysql/database_statements.rb b/activerecord/lib/active_record/connection_adapters/mysql/database_statements.rb index 6adcc14545..421afc34bc 100644 --- a/activerecord/lib/active_record/connection_adapters/mysql/database_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/mysql/database_statements.rb @@ -68,6 +68,14 @@ module ActiveRecord end alias :exec_update :exec_delete + def insert_fixtures_set(fixture_set, tables_to_delete = []) # :nodoc: + super { discard_remaining_results } + end + + def truncate_tables(*table_names) # :nodoc: + super { discard_remaining_results } + end + private def default_insert_value(column) Arel.sql("DEFAULT") unless column.auto_increment? @@ -85,6 +93,14 @@ module ActiveRecord @connection.respond_to?(:set_server_option) end + def build_truncate_statements(*table_names) + if table_names.size == 1 + super.first + else + super + end + end + def multi_statements_enabled?(flags) if flags.is_a?(Array) flags.include?("MULTI_STATEMENTS") @@ -117,6 +133,36 @@ module ActiveRecord end end + def combine_multi_statements(total_sql) + total_sql.each_with_object([]) do |sql, total_sql_chunks| + previous_packet = total_sql_chunks.last + if max_allowed_packet_reached?(sql, previous_packet) + total_sql_chunks << +sql + else + previous_packet << ";\n" + previous_packet << sql + end + end + end + + def max_allowed_packet_reached?(current_packet, previous_packet) + if current_packet.bytesize > max_allowed_packet + raise ActiveRecordError, + "Fixtures set is too large #{current_packet.bytesize}. Consider increasing the max_allowed_packet variable." + elsif previous_packet.nil? + true + else + (current_packet.bytesize + previous_packet.bytesize) > max_allowed_packet + end + end + + def max_allowed_packet + @max_allowed_packet ||= begin + bytes_margin = 2 + show_variable("max_allowed_packet") - bytes_margin + end + end + def exec_stmt_and_free(sql, name, binds, cache_stmt: false) if preventing_writes? && write_query?(sql) raise ActiveRecord::ReadOnlyError, "Write query attempted while in readonly mode: #{sql}" diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb index 208934385f..ae7dbd2868 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb @@ -143,12 +143,6 @@ module ActiveRecord end end - def truncate_tables(*table_names) # :nodoc: - unless table_names.empty? - execute "TRUNCATE TABLE #{table_names.map(&method(:quote_table_name)).join(", ")}" - end - end - # Begins a transaction. def begin_db_transaction execute "BEGIN" @@ -170,6 +164,10 @@ module ActiveRecord end private + def build_truncate_statements(*table_names) + "TRUNCATE TABLE #{table_names.map(&method(:quote_table_name)).join(", ")}" + end + # Returns the current ID of a table's sequence. def last_insert_id_result(sequence_name) exec_query("SELECT currval(#{quote(sequence_name)})", "SQL") diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3/database_statements.rb b/activerecord/lib/active_record/connection_adapters/sqlite3/database_statements.rb new file mode 100644 index 0000000000..64ef53e2e4 --- /dev/null +++ b/activerecord/lib/active_record/connection_adapters/sqlite3/database_statements.rb @@ -0,0 +1,31 @@ +# frozen_string_literal: true + +module ActiveRecord + module ConnectionAdapters + module SQLite3 + module DatabaseStatements + private + def execute_batch(sql, name = nil) + if preventing_writes? && write_query?(sql) + raise ActiveRecord::ReadOnlyError, "Write query attempted while in readonly mode: #{sql}" + end + + materialize_transactions + + log(sql, name) do + ActiveSupport::Dependencies.interlock.permit_concurrent_loads do + @connection.execute_batch(sql) + end + end + end + + def build_truncate_statements(*table_names) + truncate_tables = table_names.map do |table_name| + "DELETE FROM #{quote_table_name(table_name)}" + end + combine_multi_statements(truncate_tables) + end + end + end + end +end diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb index 8ee7e4c763..30b69c1e60 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb @@ -4,6 +4,7 @@ require "active_record/connection_adapters/abstract_adapter" require "active_record/connection_adapters/statement_pool" require "active_record/connection_adapters/sqlite3/explain_pretty_printer" require "active_record/connection_adapters/sqlite3/quoting" +require "active_record/connection_adapters/sqlite3/database_statements" require "active_record/connection_adapters/sqlite3/schema_creation" require "active_record/connection_adapters/sqlite3/schema_definitions" require "active_record/connection_adapters/sqlite3/schema_dumper" @@ -58,6 +59,7 @@ module ActiveRecord include SQLite3::Quoting include SQLite3::SchemaStatements + include SQLite3::DatabaseStatements NATIVE_DATABASE_TYPES = { primary_key: "integer PRIMARY KEY AUTOINCREMENT NOT NULL", @@ -275,10 +277,6 @@ module ActiveRecord end end - def truncate(table_name, name = nil) # :nodoc: - execute "DELETE FROM #{quote_table_name(table_name)}", name - end - def begin_db_transaction #:nodoc: log("begin transaction", nil) { @connection.transaction } end diff --git a/activerecord/lib/active_record/tasks/database_tasks.rb b/activerecord/lib/active_record/tasks/database_tasks.rb index 5a46003732..f18990cc5b 100644 --- a/activerecord/lib/active_record/tasks/database_tasks.rb +++ b/activerecord/lib/active_record/tasks/database_tasks.rb @@ -190,9 +190,7 @@ module ActiveRecord ActiveRecord::Base.internal_metadata_table_name ] - ActiveRecord::Base.connection.disable_referential_integrity do - ActiveRecord::Base.connection.truncate_tables(*table_names) - end unless table_names.empty? + ActiveRecord::Base.connection.truncate_tables(*table_names) unless table_names.empty? end end private :truncate_tables -- cgit v1.2.3