Rails 3, custom raw SQL insert statement

Question

I have an Evaluation model. Evaluation has many scores. Whenever a new evaluation is created, a score record is created for each user that needs an evaluation (see below for the current method I am using to do this.) So, for example, 40 score records might be created at once. The Evaluation owner then updates each score record with the User's score.

I'm looking to use raw SQL because each insert is its own transaction and is slow.

I would like to convert the following into a mass insert statement using raw SQL:

def build_evaluation_score_items
  self.job.active_employees.each do |employee|
    employee_score = self.scores.build
    employee_score.user_id = employee.id
    employee_score.save
  end
end

Any thoughts on how this can be done? I've tried adapting a code sample from Chris Heald's Coffee Powered site but, no dice.

Thanks to anyone willing to help!

EDIT 1

I neglected to mention the current method is wrapped in a transaction.

So, essentially, I am trying to add this to the code block so everything is inserted in one statement (** This code snippit is from Chris Heald's Coffee Powered site that discussed the topic. I would ask the question there but the post is > 3 yrs old.):

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(", ")}"

I'd be happy to show the code from some of my attempts that do not work...

Thanks again!

Well, I've cobbled together something that resembles the code above but I get a SQL statement invalid error around the ('evaluation_id' portion. Any thoughts?

def build_evaluation_score_items
  inserts = []
  self.job.active_employees.each do |employee|
    inserts.push "(#{self.id}, #{employee.id}, #{Time.now}, #{Time.now})"
  end
  sql = "INSERT INTO scores ('evaluation_id', `user_id`, 'created_at', `updated_at`)VALUES #{inserts.join(", ")}"
  ActiveRecord::Base.connection.execute(sql) 
end

Any idea as to what in the above SQL code is causing the error?

Geotaxis

the created_at part doesn't work for me since it is creating a string that postgres isn't parsing. ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "07" LINE 4: (1, 1, 100, 2017-02-20 07:00:56 +0000, 2017-02-20 07... – Ounce

Answer 1

Well, after much trial and error, here is the final answer. The cool thing is that all the records are inserted via one statement. Of course, validations are skipped (so this will not be appropriate if you require model validations on create) but in my case, that's not necessary because all I'm doing is setting up the score record for each employee's evaluation. Of course, validations work as expected when the job leader updates the employee's evaluation score.

def build_evaluation_score_items
  inserts = []
  time = Time.now.to_s(:db)
  self.job.active_employees.each do |employee|
    inserts.push "(#{self.id}, #{employee.id}, '#{time}')"
  end
  sql = "INSERT INTO scores (evaluation_id, user_id, created_at) VALUES #{inserts.join(", ")}"
  ActiveRecord::Base.connection.execute(sql) 
end

Geotaxis

I used your solution to put together something for my own needs. I was doing some performance tests, and needed to fill the DB with lots of random data (millions of rows) quickly (i.e. not using ActiveRecord). – Marcusmarcy

This doesn't work for me because time has a wrong output. Postgres is throwing this: ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "07" LINE 4: (1, 1, 100, 2017-02-20 07:04:11, 2017-02-20 07:04:11... – Ounce

Answer 2

Rather than building SQL directly (and opening yourself to SQL injection and other issues), I would recommend the activerecord-import gem. It can issue multi-row INSERT commands, among other strategies.

You could then write something like:

def build_evaluation_score_items
  new_scores = job.active_employees.map do |employee|
    scores.build(:user_id => employee.id)
  end
  Score.import new_scores
end

Norton

Answer 3

I think what you're looking for is:

def build_evaluation_score_items
  ActiveRecord::Base.transaction do
    self.job.active_employees.each do |employee|
      employee_score = self.scores.build
      employee_score.user_id = employee.id
      employee_score.save
    end
  end
end

All child transactions are automatically "pushed" up to the parent transaction. This will prevent the overhead of so many transactions and should increase performance.

You can read more about ActiveRecord transactions here.

UPDATE

Sorry, I misunderstood. Keeping the above answer for posterity. Try this:

def build_evaluation_score_items
  raw_sql = "INSERT INTO your_table ('user_id', 'something_else') VALUES "
  insert_values = "('%s', '%s'),"
  self.job.active_employees.each do |employee|
    raw_sql += insert_values % employee.id, "something else"
  end
  ActiveRecord::Base.connection.execute raw_sql
end

Lymphadenitis

Thanks for the suggestion. I neglected to point out in my post that I currently have the statement wrapped up in a transaction in the code but that's still sub-optimal, as it still executes insert statements for each employee. The only benefit to the transaction is that the db index isn't updated. – Geotaxis

I updated my answer with a potential solution. Please let me know if it helps you. – Lymphadenitis

Hi Chuck - I agree with keeping the first item there, that's why I upvoted it. The transaction method makes good sense. That said, unfortunately, I get the following error: undefined method `execute' for ActiveRecord::Base:Class. I'll dig around for the appropriate execute command and will report back. – Geotaxis