{"id":168,"date":"2021-11-25T23:00:00","date_gmt":"2021-11-26T03:00:00","guid":{"rendered":"https:\/\/gerardomiranda.dev\/blog\/?p=168"},"modified":"2024-04-05T01:06:12","modified_gmt":"2024-04-05T05:06:12","slug":"how-to-manage-large-amounts-of-data-in-ruby-on-rails","status":"publish","type":"post","link":"https:\/\/gerardomiranda.dev\/blog\/how-to-manage-large-amounts-of-data-in-ruby-on-rails\/","title":{"rendered":"How to Manage Large Amounts of Data in Ruby on Rails"},"content":{"rendered":"\n

The other day I got a task for which I needed to generate 2,000,000 records in the database. The author of the ticket was kind enough to write down a script to generate the data. I didn’t pay attention to it and just leave it running in a CLI while I was completing other tasks. These other tasks took me the rest of the day to complete and when I went back to the script I realized it wasn’t even half completed.<\/p>\n\n\n\n

Obviously the script was not efficient at all, so I took a second look at it. First thing I noticed is that it was generating instances one by one, and that it was inevitable because of the nature of the records we needed to generate. The actual cause and the one that was avoidable was HOW it was saving the records, using ActiveRecord.save method.<\/p>\n\n\n\n

I know first hand that ActiveRecord save method is not efficient when working with big chunks of data, but I didn’t know how slow it was. So after changing the script to use the activerecord-import<\/a> library I was curious about how big of a difference it actually makes.<\/p>\n\n\n\n

<\/div>\n\n\n\n

Preparing the tests<\/h4>\n\n\n\n

First we need a model, we will keep it very simple because we don’t need to overcomplicate things.<\/p>\n\n\n

create_table :dummy_models<\/span> do<\/span> |t|<\/span>\n   t.string :dummy_text1<\/span>\n   t.string :dummy_text2<\/span>\n   t.string :dummy_text3<\/span>\n  \n   t.timestamps\n end<\/span><\/code><\/span>Code language:<\/span> Ruby<\/span> (<\/span>ruby<\/span>)<\/span><\/small><\/pre>\n\n\n
def<\/span> self<\/span>.generate_record<\/span><\/span>\n    DummyModel.new(dummy_text1:<\/span> Faker::Lorem.word, dummy_text2:<\/span> Faker::Lorem.word, dummy_text3:<\/span> Faker::Lorem.word)\nend<\/span><\/code><\/span>Code language:<\/span> Ruby<\/span> (<\/span>ruby<\/span>)<\/span><\/small><\/pre>\n\n\n
Now let’s start with the method to compare, first using ActiveRecord.save<\/p>\n\n\n
def<\/span> self<\/span>.with_active_record_create<\/span>(size)<\/span><\/span>\n    (1<\/span>..size).each do<\/span> |i|<\/span>\n        record = generate_record\n        record.save!\n    end<\/span> \nend<\/span><\/code><\/span>Code language:<\/span> Ruby<\/span> (<\/span>ruby<\/span>)<\/span><\/small><\/pre>\n\n\n
We continue with activerecord-import<\/p>\n\n\n
def<\/span> self<\/span>.with_active_record_import<\/span>(size)<\/span><\/span>\n    records = []\n    (1<\/span>..size).each do<\/span> |i|<\/span>\n        records << generate_record\n    end<\/span>\n    DummyModel.import(records)\nend<\/span><\/code><\/span>Code language:<\/span> Ruby<\/span> (<\/span>ruby<\/span>)<\/span><\/small><\/pre>\n\n\n
We can go further, and use a couple of options that allow us to skip validations and additionally pass the columns directly without having to pass an ActiveRecord object. For this last option we are going to create the ActiveRecord object first to see if the difference in performance justify it.<\/p>\n\n\n
def<\/span> self<\/span>.with_active_record_import_without_validations<\/span>(size)<\/span><\/span>\n    records = []\n    (1<\/span>..size).each do<\/span> |i|<\/span>\n        records << generate_record\n    end<\/span>\n    DummyModel.import(records, validate:<\/span> false<\/span>)\nend<\/span>\n    \ndef<\/span> self<\/span>.with_active_record_import_columns_without_validations<\/span>(size)<\/span><\/span>\n    values = []\n    columns = ['dummy_text1'<\/span>, 'dummy_text2'<\/span>, 'dummy_text3'<\/span>]\n    (1<\/span>..size).each do<\/span> |i|<\/span>\n        record = generate_record\n        values << [record.dummy_text1, record.dummy_text2, record.dummy_text3]\n    end<\/span>\n    DummyModel.import(columns, values, validate:<\/span> false<\/span>)\nend<\/span><\/code><\/span>Code language:<\/span> Ruby<\/span> (<\/span>ruby<\/span>)<\/span><\/small><\/pre>\n\n\n
<\/div>\n\n\n\n
Comparing running times<\/h4>\n\n\n\n
And that’s it , the only thing left is to use benchmarking to see the differences in running time. Let’s start with just 1,000 records.<\/p>\n\n\n
With<\/span> ActiveRecord<\/span> save<\/span>\n  2.444752<\/span>   0.297301<\/span>   2.742053<\/span> (  5.692380<\/span>)\nWith<\/span> activerecord-import<\/span>\n  0.260530<\/span>   0.028553<\/span>   0.289083<\/span> (  0.325896<\/span>)\nWith<\/span> activerecord-import<\/span> without<\/span> validations<\/span>\n  0.250810<\/span>   0.024476<\/span>   0.275286<\/span> (  0.289907<\/span>)\nWith<\/span> activerecord-import<\/span> using<\/span> columns<\/span> without<\/span> validations<\/span>\n  0.223519<\/span>   0.028434<\/span>   0.251953<\/span> (  0.267757<\/span>)<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
With only this amount of data, we can see a noticeable difference of more than 5 seconds. But I think the ActiveRecord.save is still usable, since 5.6 seconds is not that bad. <\/p>\n\n\n\n
 activerecord-import <\/p>\n\n\n\n
Between the methods of activerecord-import there is almost no difference. Now we will try with 10,000 records.<\/p>\n\n\n
With<\/span> ActiveRecord<\/span> save<\/span>\n 19.409276<\/span>   1.963945<\/span>  21.373221<\/span> ( 47.560994<\/span>)\nWith<\/span> activerecord-import<\/span>\n  2.865762<\/span>   0.203885<\/span>   3.069647<\/span> (  3.313518<\/span>)\nWith<\/span> activerecord-import<\/span> without<\/span> validations<\/span>\n  2.657060<\/span>   0.195326<\/span>   2.852386<\/span> (  2.983243<\/span>)\nWith<\/span> activerecord-import<\/span> using<\/span> columns<\/span> without<\/span> validations<\/span>\n  2.423606<\/span>   0.217493<\/span>   2.641099<\/span> (  2.779699<\/span>)<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
With this execution time I think ActiveRecord is not an option anymore when dealing with more than 10,000 records. But the difference between the methods of activerecord-import is still not significative.<\/p>\n\n\n\n
Let’s put the computer to work and generate 100,000 records and see what happens.<\/p>\n\n\n
With<\/span> ActiveRecord<\/span> save<\/span>\n207.721426<\/span>  20.519692<\/span> 228.241118<\/span> (503.432937<\/span>)\nWith<\/span> activerecord-import<\/span>\n 27.889573<\/span>   2.115005<\/span>  30.004578<\/span> ( 32.025789<\/span>)\nWith<\/span> activerecord-import<\/span> without<\/span> validations<\/span>\n 27.754645<\/span>   2.084204<\/span>  29.838849<\/span> ( 31.793397<\/span>)\nWith<\/span> activerecord-import<\/span> using<\/span> columns<\/span> without<\/span> validations<\/span>\n 24.807959<\/span>   1.963377<\/span>  26.771336<\/span> ( 28.741473<\/span>)<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Definitively ActiveRecord.save should not even be considered for 100,000+ records. Now I understand why the script didn’t finish executing after hours. Regarding the activerecord-import methods, using the columns instead of the ActiveRecord object was at least 2 seconds faster than the other ones. This is nothing outstanding but it is worth considering especially if you are working with more than that.<\/p>\n\n\n\n
The comparisons showed me that when I’m developing with Ruby on Rails I have to seriously take in consideration the amount of data I’m gonna work with. Lest one of those scripts end up in production one day.<\/p>\n\n\n\n
You can find the complete code used for this post here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
I know first hand that ActiveRecord save method is not efficient when working with big chunks of data, but I didn’t know how slow it was.<\/p>\n","protected":false},"author":1,"featured_media":165,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[24],"tags":[92,94,96],"class_list":["post-168","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-learning","tag-data-en","tag-ruby-on-rails-en","tag-web-en"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/posts\/168","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/comments?post=168"}],"version-history":[{"count":12,"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/posts\/168\/revisions"}],"predecessor-version":[{"id":240,"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/posts\/168\/revisions\/240"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/media\/165"}],"wp:attachment":[{"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/media?parent=168"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/categories?post=168"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gerardomiranda.dev\/blog\/wp-json\/wp\/v2\/tags?post=168"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Next we are going to need the method to generate the records. For this we will use the Faker<\/a> gem to generate the text. To mimic the original problem we are going to create the record one by one before saving them.<\/p>\n\n\n