Rails: huge data import to three connected tables...

Question

Hi everybody,

Im looking for a good way to solve my performance issues in my rails application. I have three tables which have: one to one to many connections in between. If I want to fill in 130 items of the first table with all the data for the underneath tables, It results in about 1000 queries and takes about 10 seconds (SQLite DB).

I found the

accept_nested_attributes_for

statement, witch lets you enter data for multiple tables in one line of code. My question is, wether this is a good option in a performance point of view. Does somebody have any experience with it?

Thanks Markus

Answer 1

Hi Mike,

here it is:

Table: cards  
front_side_id  
back_side_id

Table: card_sides

Table: card_side_entries  
card_side_id

I have now a XML like this:

<Cards>
  <Card>
    <FrontSide>
      <CardSideEntries>
        <CardSideEntrie/>
        ...
      </CardSideEntries>
    </FrontSide>
    <BackSide>
      <CardSideEntries>
        <CardSideEntrie/>
        ...
      </CardSideEntries>
    </BackSide>
  </Card>
  ...
</Cards>

In my solution I parse the whole XML file line by line, and because I sometimes need a card_id I have to save a certain table entry twice... Does anybody now something about accept_nested_attributes_for?

Thanks, Markus

Answer 2

accept_nested_attributes_for add the possibility to ActiveRecord to be able to write into association directly from one model.

exemple : You have models like :

class User
   accepts_nested_attributes_for :cars
end

class Car
 belongs_to :user
end

and a hash like :

param[:user] = {}
params[:user][:name] = "Mike"
params[:user][:car] = {}
params[:user][:car][:brand] = "Nissan"

User.create(params[:user])

This will create a new user and a new car, without *accepts_nested_attributes_for* :

@user = User.create(params[:user])
@car = Car.create(params[:user][:car])
@user.car = @car

This function is usually with *fields_for* in HTML forms so you can easily handle the creation of an object and his associations.

In your case I imagine your models like that (regarding your XML) :

class Card
  has_one :front_side, :class => "Side"
  has_one :back_side, :class => "Side"
end

class Side
  belongs_to :card
  has_many :card_side_entry
end

class CardSideEntry
  belongs_to :side
end

I don't know where your XML come from (your data are extracted from it ??), but I imagine you could use accepts_nested_attributes_for so you could have each card hash generating the associations. But I'm not sure to understand all the problem and if this is the best solution