Speed up find_by search in Ruby on Rails

I've got a table called Staging into which I place all the data from an excel spreadsheet, get the ID number from it by looking up the existing model/table, and then compare it with the current database which is SQL Server 2008.

My code is as follows:

def compare 

require 'rubygems'
require 'spreadsheet'
require 'set'

Spreadsheet.client_encoding = 'UTF-8'
file_full_path = File.expand_path(File.join(File.dirname(__FILE__), "../../SISlist.xls"))
book = Spreadsheet.open(file_full_path) #Select excel file
sheet = book.worksheet 0 #Select 1st worksheet
app,server,env = 0

for i in 1..500
  row = sheet.row(i)

    if row[0].to_s != "" # Makes sure no empty cells are saved
     row.each do |t|
     app = App.find_by_name(row[0].to_s)
     server = Server.find_by_name(row[2].to_s)
     env = Environment.find_by_code(row[3].to_s)
    end
Staging.create(:app => app.id, :server => server.id, :environment => env.id)
  end
 end开发者_StackOverflow社区
end

The problem I'm having now is that it takes extremely long to carry out this method (almost 20 seconds), when all my other methods of similar sort don't take that long.

Any way to speed up this process or maybe my flow of work is incorrect and hence the whole architecture is wrong?

Help needed

---

To speed up try

ActiveRecord::Base.transaction do
  500.times do |i|
    row = sheet.row(i)
    if row[0].to_s != "" # Makes sure no empty cells are saved
      app = App.find_by_name(row[0].to_s)
      server = Server.find_by_name(row[2].to_s)
      env = Environment.find_by_code(row[3].to_s)
      Staging.create(:app => app.id, :server => server.id, :environment => env.id)
     end
  end
end

also are you aware that app,server,env = 0 doesn't initialize all values with zero?

---

If you only have a couple hundred rows then you could try doing it in three steps:

1. Spin through the spreadsheet to collect all the App, Server, and Environment names/codes.
2. Bulk load your App, Server, and Environment into hashes.
3. Spin through the spreadsheet again to do your Staging.create calls.

Something like this:

sets = {
    :apps         => Set.new,
    :servers      => Set.new,
    :environments => Set.new
}
(1 .. 500).select { |i| !sheet.row(i).to_s.empty? }.each do |i|
    sets[:apps].add(row[0].to_s)
    #...
end

# You could just pull in the ids and names here rather than whole objects too.
sets[:apps] = Set.where(:name => sets[:apps].to_a).each_with_object({ }) { |a,h| h[a.name] = a.id }
#...

(1 .. 500).select { |i| !sheet.row(i).to_s.empty? }.each do |i|
    Staging.create(
        :app => sets[:apps][row[0].to_s],
        #...
    )
end

Basically I'm guessing that your biggest hit is calling find_by... over and over again instead of just doing it once.