Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
INSERTing 50,000 records into a database in ActiveRecord, Arel, SQL, activerecord-import and Sequel.
require "active_record"
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Migration.class_eval do
create_table(:records) do |t|
t.string :column
end
end
data = 50_000.times.map { |i| Hash[column: "Column #{i}"] }
# =============================================================
class Record < ActiveRecord::Base
end
Record.create(data)
# Takes 60 seconds, because it's instantiating all the ActiveRecord::Base objects
require "active_record"
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Migration.class_eval do
create_table(:records) do |t|
t.string :column
end
end
data = 50_000.times.map { |i| Hash[column: "Column #{i}"] }
# =============================================================
data.each do |hash|
insert = Arel::Nodes::InsertStatement.new
insert.relation = Arel::Table.new(:records)
insert.columns = hash.keys.map { |k| Arel::Table.new(:records)[k] }
insert.values = Arel::Nodes::Values.new(hash.values, insert.columns)
ActiveRecord::Base.connection.execute(insert.to_sql)
end
# Takes 20 seconds, because of 50,000 INSERT statements, since Arel doesn't support
# INSERTing multiple records in a single query. This code is obviously really ugly.
require "active_record"
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Migration.class_eval do
create_table(:records) do |t|
t.string :column
end
end
data = 50_000.times.map { |i| Hash[column: "Column #{i}"] }
# =============================================================
columns = data.first.keys
values_list = data.map do |hash|
hash.values.map do |value|
ActiveRecord::Base.connection.quote(value)
end
end
ActiveRecord::Base.connection.execute <<-SQL
INSERT INTO records (#{columns.join(",")}) VALUES
#{values_list.map { |values| "(#{values.join(",")})" }.join(", ")}
SQL
# Takes 2 seconds, because it does a single multi INSERT statement.
# But the code is even uglier than with Arel.
require "active_record"
require "activerecord-import" # https://github.com/zdennis/activerecord-import
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Migration.class_eval do
create_table(:records) do |t|
t.string :column
end
end
data = 50_000.times.map { |i| Hash[column: "Column #{i}"] }
# =============================================================
class Record < ActiveRecord::Base
end
Record.import(data.first.keys, data.map(&:values), validate: false)
# Takes 2 seconds (like the SQL version), but it's an additional dependency.
require "sequel"
DB = Sequel.sqlite
DB.create_table(:records) do
primary_key :id
String :column
end
data = 50_000.times.map { |i| Hash[column: "Column #{i}"] }
# =============================================================
DB[:records].multi_insert(data)
# Takes 2 seconds (like the SQL and activerecord-import versions), and it's a simple one-liner.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.