# How do I import data FAST? (AR-extensions)

**URL:** <https://www.ruby-forum.com/t/how-do-i-import-data-fast-ar-extensions/119952>\
**Category:** Rails\
**Created:** [November 12, 2007, 2:55pm UTC](https://www.ruby-forum.com/t/how-do-i-import-data-fast-ar-extensions/119952 "2007-11-12T14:55:33Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![lusid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/e56c9b/32.png) [@lusid](https://www.ruby-forum.com/u/lusid)\
**Post date:** [November 12, 2007, 2:55pm UTC](https://www.ruby-forum.com/t/how-do-i-import-data-fast-ar-extensions/119952/1 "2007-11-12T14:55:33Z")

</div>

I need to import large amounts of data records into a database with my  
rails application. The problem: It’s REALLY SLOW.

I found Zach D.’ ActiveRecord-extensions to be extremely  
interesting:  
[http://www.rubyinside.com/advent2006/17-extendingar.html#import](http://www.rubyinside.com/advent2006/17-extendingar.html#import)

Quote:“Benchmarks with MySQL’s MyISAM and InnoDb table types without  
validations show up to a 48x performance increase.”

BUT: How can I use this with joined tables?

Example:  
Supposed “Parent” and “Child” have a has\_and\_belongs\_to\_many relation,  
i.e. they are linked with a join table “childrens\_parents”. Now, I want  
to use the import functionality from ar-extension to optimize this:

1000.times {  
parent.children.create(:par0 =\> 42)  
}

Problem: Every “create” generates two(!) INSERTs in MySQL:  
…  
INSERT INTO children\_parents (‘child\_id’, ‘parent\_id’) VALUES (325648,  
480)  
INSERT INTO children (‘par0’) VALUES(42)  
…

Is there a way to optimize this with “import” from the AR extensions? Or  
is there any other way to do this fast?

Any help is appreciated!

Thanks,  
Marc

---

<div class="post-metadata">

**Author:** ![lusid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/e56c9b/32.png) [@lusid](https://www.ruby-forum.com/u/lusid)\
**Post date:** [June 15, 2008, 12:15am UTC](https://www.ruby-forum.com/t/how-do-i-import-data-fast-ar-extensions/119952/2 "2008-06-15T00:15:29Z")

</div>

On Nov 12 2007, 8:55 am, Marc M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> Problem: Every “create” generates two(!) INSERTs in MySQL:
> 
> ## Thanks, Marc
> 
> Posted viahttp://www.ruby-forum.com/.

Try something like this. The trick is to use arrays to capture the  
data for each iteration. Its fast.

require “rubygems”  
require “active\_record”

#change the following for your database  
ActiveRecord::Base.establish\_connection(:adapter =\> “mysql”, :host =\>  
“localhost”,:database =\> “railsspace\_development”,  
:username =\> “your use name”, :password =\> “your password”)

class Spec \< ActiveRecord::Base;end  
class Spectors \< Spec

attr\_accessor( :user\_id, :first\_name, :last\_name, :gender, :birthdate,  
:zip\_code)  
end  
i =0;test\_data = YAML.load\_file(‘specs2.yml’)  
while i \< (test\_data.length) #number of players

lastname=[];first\_name=[];gender=[];birthdate=[];zip\_code=[];userid=[]  
userid \<\< test\_data[i].user\_id;first\_name \<\<  
test\_data[i].first\_name  
lastname \<\< test\_data[i].last\_name;gender \<\< test\_data[i].gender  
birthdate \<\< test\_data[i].birthdate;zip\_code \<\<  
test\_data[i].zip\_code  
spec = Spec.new;spec.id=i + 1;spec.last\_name = lastname.first  
spec.first\_name = first\_name.first.to\_s;spec.gender = gender.first  
spec.birthdate= birthdate.first;spec.zip\_code =  
zip\_code.first.to\_s.to\_i  
spec.user\_id = userid.first.to\_s.to\_i;spec.save!;i += 1  
end

Todd Miller