CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3activerecord
Notinlist
Notinlist

Reputation: 16640

Rails3: SQL execution with hash substitution like .where()

With a simple model like that

class Model < ActiveRecord::Base
   # ...
end

we can do queries like that

Model.where(["name = :name and updated_at >= :D", \
  { :D => (Date.today - 1.day).to_datetime, :name => "O'Connor" }])

Where the values in the hash will be substituted into the final SQL statement with proper escaping depending on the underlying database engine.

I would like to know a similar feature for SQL execution like:

ActiveRecord::Base.connection.execute( \
    ["update models set name = :name, hired_at = :D where id = :id;"], \
    { :id => 73465, :D => DateTime.now, :name => "O'My God" }] \
  ) # THIS CODE IS A FANTASY. NOT WORKING.

(Please do not solve the example with loading a Model object, modifying and then saving! The example is only an illustration for the feature I would like to have / know. Concentrate on the subject!)

The original problem is that I want to insert large amount (many thousand lines) of data into the database. I want to use some features of the SQL abstraction of the ActiveRecord framework but I don't want to use model objects based on ActiveRecord::Base because they are damn slow! (8 queries per second for my current problem.)

Upvotes: 2

Views: 4408

Answers (5)

Zeeshan
Zeeshan

Reputation: 3462

Extending the @peufeu solution with concrete code example for bulk insert:

users_places = []
users_values = []
timestamp = Time.now.strftime('%Y-%m-%d %H:%M:%S')
params[:users].each do |user|
    users_places << "(?,?,?,?)"
    users_values << user[:name] << user[:punch_line] << timestamp << timestamp
end

bulk_insert_users_sql_arr = ["INSERT INTO users (name, punch_line, created_at, updated_at) VALUES #{users_places.join(", ")}"] + users_values
begin
    sql = ActiveRecord::Base.send(:sanitize_sql_array, bulk_insert_users_sql_arr)
    ActiveRecord::Base.connection.execute(sql)
rescue
    "something went wrong with the bulk insert sql query"
end

Here is the reference to sanitize_sql_array method in ActiveRecord::Base, it generates the proper query string by escaping the single quotes in the strings. For example the punch_line "Don't let them get you down" will become "Don\'t let them get you down".

Upvotes: 3

Sandeep M
Sandeep M

Reputation: 2992

I encountered a similar issue recently when tying to insert 100K+ records into a MySQL database for a Rails 4 app using mysql2 gem. The data included characters that had to be sanitized prior to insert.

The solution I ended going with was a slightly modified version of Option 3 described at https://www.coffeepowered.net/2009/01/23/mass-inserting-data-in-rails-without-killing-your-performance/

Here's the relevant code block from the above link:

TIMES = 10000
inserts = []
TIMES.times do
  inserts.push "(3.0, '2009-01-23 20:21:13', 2, 1)"
end
sql = "INSERT INTO user_node_scores (`score`, `updated_at`, `node_id`, `user_id`) VALUES #{inserts.join(", ")}"

The modification I made was using the public method ActiveRecord::Base.sanitize() on values that required it.

inserts = []
created = Time.now.strftime "%Y-%m-%d %H:%M:%S"
params[:audits].each do |audit|
  inserts.push "(#{audit.user_id), #{created}," + ActiveRecord::Base.sanitize(audit.comment) + ", #{audit.status})"
end
sql = "INSERT INTO user_audits (`user_id`, `created_at`, `comment`, `status`) VALUES #{inserts.join(", ")}"

Upvotes: 1

bobflux
bobflux

Reputation: 11581

For INSERTs, batching them using a long VALUES clause (as shown by Simon's link) is the fastest way (unless you want to generate a text file and load it in your database with MySQL's LOAD DATA INFILE). But you have to be very careful about escaping your text values (which is not done in the example).

I was asking "what database are you using" because it does matter for mass UPDATEs.

For instance, you can do this on postgres (and I believe SQL Server changing "columnX" to "colX" ):

UPDATE foo 
JOIN (VALUES (1,2),(3,4),... long list) v ON (foo.id=v.column1)
SET foo.bar = v.column2

And you can update a load of rows using a single statement, very fast.

If you don't need Ruby to perform some Ruby-specific magic on your data, the fastest way to transfer data from one DB to a different one is to export as a text file (CSV or tab separated), load it on the other DB (LOAD DATA INFILE on MySQL), perhaps in a temporary table, and bulk process using SQL.

EDIT : Here's how I do this in Python :

sql = [ "INSERT INTO foo (column list) VALUES " ]
values = []

for tuple in tuple_list:
     append "(?,?,?,?)" to sql
     extend values list with tuple

Then join sql into a string, you get "INSERT INTO foo (column list) VALUES (?,?,?,?),(?,?,?,?),(?,?,?,?)" with the "(?,?,?,?)" repeated as many times as you have lines to insert.

Then "values" contains a list of (a1,b1,c1,d1,a2,b2,c2,d2,a3,b3,c3,d3) with an,bn,cn,dn being the tuples you want to insert for line n. Each one corresponds to a placeholder in the sql string.

Then pass this to the usual "execute query with parameters" function which will handle quoting and escaping as usual.

Upvotes: 1

twmills
twmills

Reputation: 3015

Yes you could do raw SQL, but checkout the ar-extensions gem that helps with batch inserts:

https://github.com/zdennis/ar-extensions

Here's a post on it, and various other techniques:

http://www.coffeepowered.net/2009/01/23/mass-inserting-data-in-rails-without-killing-your-performance/

Upvotes: 1

Vasiliy Ermolovich
Vasiliy Ermolovich

Reputation: 24617

query = ActiveRecord::Base.connection.raw_connection.prepare("INSERT INTO users (name) VALUES(:name)")
query.execute(:name => 'test_name')
query.close

Upvotes: 3

Related Questions