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:
- Spin through the spreadsheet to collect all the App, Server, and Environment names/codes.
- Bulk load your App, Server, and Environment into hashes.
- 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.
精彩评论