CODEWITHSUNDEEP
ruby-on-railsactiverecordbulkinsertactiverecord-import
Oleksandr Bratashov
Oleksandr Bratashov

Reputation: 908

Why 'activerecord-import' doesn't perform bulk insert?

After testing activerecord-import library for bulk insert i found that it doesn't perform one huge INSERT but a lot of small INSERTs

Xml.import(
  insert_values,
  ignore: true,
  validate: false,
  batch_size: 1000
)

# => 

INSERT INTO `xmls` (`path`,`import_id`,`status`,`last_modified`,`created_at`,`updated_at`) VALUES ('test-folder/0_0.xml',114,10,'2019-08-16 20:02:20','2019-08-16 20:02:20','2019-08-16 20:02:20')
INSERT INTO `xmls` (`path`,`import_id`,`status`,`last_modified`,`created_at`,`updated_at`) VALUES ('test-folder/0_1.xml',114,10,'2019-08-16 20:02:20','2019-08-16 20:02:20','2019-08-16 20:02:20')
#...
INSERT INTO `xmls` (`path`,`import_id`,`status`,`last_modified`,`created_at`,`updated_at`) VALUES ('test-folder/0_2.xml',114,10,'2019-08-16 20:02:20','2019-08-16 20:02:20','2019-08-16 20:02:20')

I've tried set insert_values as an array of XML.new and also as a pure array of arrays:

cols = [:path, :import_id, :status, :last_modified]

insert_values = [
    [ 0] [
        [0] "test-folder/0_0.xml",
        [1] 115,
        [2] 10,
        [3] Sat, 17 Aug 2019 05:37:02 EDT -04:00
    ],
    [ 1] [
        [0] "test-folder/0_1.xml",
        [1] 115,
        [2] 10,
        [3] Sat, 17 Aug 2019 05:37:02 EDT -04:00
    ],
#...
]

Xml.import(
  cols,
  insert_values,
  ignore: true,
  validate: false,
  batch_size: 1000
)

Does anybody know why it works in this way? I couldn't find anything in the documentation.

Upvotes: 2

Views: 998

Answers (1)

Oleksandr Bratashov
Oleksandr Bratashov

Reputation: 908

So, currently, i've found a bit modified 3rd party solution https://gist.github.com/abratashov/155bcd0ea2e02940cc6157e6970e7a2b

It creates the batches(with 1000 by default) and performs a huge BULK INSERT.

Also Rails 6 supports bulk insert

Upvotes: 1

Related Questions