Efficient Updates & Data Import in Rails

By on July 11, 2007

/* Typical ActiveRecord insert * number of records = slow */
mysql> insert into widgets (title, price) values ('gizmo',5);
Query OK, 1 row affected (0.01 sec)

/* Multi-record insert with ar-extensions * 1 insert = fast */
mysql> insert into widgets (title,price) values ('gizmo',5),('super-gizmo',10),('hammer',6);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

ActiveRecord is a fantastic database abstraction layer and is rightfully one of the core pillars of Rails, but that doesn't mean we can't improve it. In fact, most Rails developers become painfully aware of this fact whenever they try to import or update a large number of records. The process of instantiating each object, validating it, and doing an individual save in a transactional database can be rather painful. Thankfully, Zach Dennis wrote a great gem (ar-extensions), which among other things, gives us access to the native SQL 'import' functions geared at exactly this task: take a large update transaction, and do it all at once - fast.

SQL insert syntax - MySQL

If you look at the MySQL documentation, you'll notice that INSERT is not limited to a single set of values. In fact, we can provide as many objects as we want, as long as our query fits into the 'max_packet_size' of your database. Hence, by default Rails will issue an insert request per object, and our goal is to insert them all at once. It is also worth mentioning the "on duplicate key" directive, you'll see how handy it can be in just a second (hint: updates).

Extending ActiveRecord with ar-extensions

Zach's gem is as un-intrusive as it can get. Install it gem install ar-extensions, add a require statement in your controller and you're virtually done. Let's take a look at some of the available import scenarios:

# Assume a simple Widget model, with three columns: id, title, price

# Import an array of objects - single insert statement
widgets = [ Widget.new(:title => 'gizmo', :price => 5),
            Widget.new(:title => 'super-gizmo', :price => 10)]
Widget.import widgets

# Perhaps you don't want to instantiante the objects, no problem..
# - The order of values within the 'values' array should match the order of columns
columns = [:title, :price]
values = [['gizmo', 5], ['super-gizmo', 6]]
Widget.import columns, values

And now for bonus points: an update is really an insert, but with a duplicate condition. For example, if we make the title column unique, then inserting an object with an existing title will raise a 'duplicate key error', that is, unless we provide the 'on duplicate key' clause in our original query (very clever, aren't we!):

# Assume a simple Widget model, with three columns: id, unique(title), price

# Increment the price of widgets by one (from previous insert)
widgets = [ Widget.new(:title => 'gizmo', :price => 6),
            Widget.new(:title => 'super-gizmo', :price => 11)]
Widget.import widgets, :on_duplicate_key_update => [:price]

# Likewise, we can perform the same task without instantiating the objects
columns = [:title, :price]
values = [['gizmo', 6], ['super-gizmo', 11]]
Widget.import columns, values, :on_duplicate_key_update => [:price]

If you're going for speed, you can also omit ActiveRecord validations by providing a :validate => false option on your import query. At the very least, you should see a 10x performance improvement, which is something definitely worth working towards. For more great ar-extensions recipes, be sure to check out Zach's post on the RubyInside advent calendar, and the RDoc documentation itself.

Ilya GrigorikIlya Grigorik is a web ecosystem engineer, author of High Performance Browser Networking (O'Reilly), and Principal Engineer at Shopify — follow on Twitter.