# Inserting A Large Amount of Records into DB using Rake, Ruby

**URL:** <https://www.ruby-forum.com/t/inserting-a-large-amount-of-records-into-db-using-rake-ruby/77070>\
**Category:** Rails\
**Created:** [November 23, 2006, 11:40pm UTC](https://www.ruby-forum.com/t/inserting-a-large-amount-of-records-into-db-using-rake-ruby/77070 "2006-11-23T23:40:13Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![johnk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/59ef9b/32.png) [@johnk](https://www.ruby-forum.com/u/johnk)\
**Post date:** [November 23, 2006, 11:40pm UTC](https://www.ruby-forum.com/t/inserting-a-large-amount-of-records-into-db-using-rake-ruby/77070/1 "2006-11-23T23:40:13Z")

</div>

I have a text file with a couple hundred thousand rows that I use ruby  
to massage and then active record to insert each row after being  
massaged into the DB. Now… inserting a couple hundred thousand rows  
using ActiveRecord and ruby is crazy. I know.

It was beautiful to write my rake task to do everything for me but now  
the problem is time. For 1,000 records it is taking me on average  
1:00 minute to process. I have an old PowerBook 867.

Now, I noticed when looking at the log file that the COMMIT opperation  
is what is taking so much time. I was wondering if I can extend the  
commit so the commit is done at the end or every X amount of updates?

Or any other suggestions on how to speed this up?

Thanks my friends :-).

–  
John K.  
removed\_email\_address@domain.invalid

[http://www.kopanas.com](http://www.kopanas.com)

> **[Canadian University Software Engineering Conference - CUSEC](http://cusec.net/)**
>
> Canadian University Software Engineering Conference is a yearly conference created by students for students.

[http://www.soen.info](http://www.soen.info)

---

<div class="post-metadata">

**Author:** ![johnk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/59ef9b/32.png) [@johnk](https://www.ruby-forum.com/u/johnk)\
**Post date:** [November 24, 2006, 12:30am UTC](https://www.ruby-forum.com/t/inserting-a-large-amount-of-records-into-db-using-rake-ruby/77070/2 "2006-11-24T00:30:17Z")

</div>

You might want to consider exporting to a file which can be bulk  
loaded into the DB. We are dealing with data sources which have tens  
of millions of rows and bulk loading is the only way to effeciently  
load it.

If you absolutely want to manage the commit manually you can, via  
ActiveRecord::Base.connection methods:

begin\_db\_transaction  
commit\_db\_transaction  
rollback\_db\_transaction

Or you may be able to wrap the inserts in a transaction block.

Still, I’d highly recommend considering bulk load.

V/r  
Anthony E.

---

<div class="post-metadata">

**Author:** ![johnk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/59ef9b/32.png) [@johnk](https://www.ruby-forum.com/u/johnk)\
**Post date:** [November 24, 2006, 12:42am UTC](https://www.ruby-forum.com/t/inserting-a-large-amount-of-records-into-db-using-rake-ruby/77070/3 "2006-11-24T00:42:18Z")

</div>

-----BEGIN PGP SIGNED MESSAGE-----  
Hash: SHA1

John K. wrote:

> is what is taking so much time. I was wondering if I can extend the  
> commit so the commit is done at the end or every X amount of updates?
> 
> Or any other suggestions on how to speed this up?

ActiveRecord::Extensions (for MySQL) handles bulk inserts using  
multi-value insert statements. It goes up to 40x faster then current  
ActiveRecord behavior.

API looks like:  
MyModel.import( columns, array\_of\_value\_sets, options\_hash )

Documentation on it can be found here:  
[http://www.continuousthinking.com/are/import](http://www.continuousthinking.com/are/import)

Here are some MySQL benchmarks for current ActiveRecord#create vs.  
ActiveRecord::Extensions#import comparing MyISAM to InnoDb to Memory  
tables:  
[http://www.continuousthinking.com/are/import-benchmarks-for-mysql](http://www.continuousthinking.com/are/import-benchmarks-for-mysql)

You can download the latest version from:  
[http://rubyforge.org/projects/arext/](http://rubyforge.org/projects/arext/)

What db adapter do you use? I am currently adding PostgreSQL support  
over this holiday weekend.

Zach

-----BEGIN PGP SIGNATURE-----  
Version: GnuPG v1.4.3 (GNU/Linux)  
Comment: Using GnuPG with Mozilla - [http://enigmail.mozdev.org](http://enigmail.mozdev.org)

iD8DBQFFZjJ7Myx0fW1d8G0RAt33AJ4tpTNptf5jDzK/JxmAkmWAWyRoIgCaAoiM  
Ha5cR5ZOPzmaNpTlxeZEHQY=  
=XPVC  
-----END PGP SIGNATURE-----