blocks|key|2162557|text|感谢Chris+Heald+@cheald在2009年的article，他向我展示了最好的方法是多行插入命令。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2162558|将以下代码添加到我的initializers/active_record.rb文件中，将我的Model.create!(...)调用更改为Model.import!(...)，然后就可以了。以下是一些注意事项：|style|CODE|2162559|1)不验证数据。|2162560|2)它使用SQL+INSERT命令的形式，读起来像这样...|2162561|INSERT+INTO+<table>+(field-1,+field-2,+...)+
+++++++VALUES+(value-1-1,+value-1-2,+...),+(value-2-1,+value-2-2,+...),+...`|code-block|syntax|javascript|2162562|..。这可能不是适用于所有数据库的正确语法，但它适用于Postgres。根据您的SQL版本修改适当语法的代码并不困难。|2162563|在我的特殊情况下，将19K%2B记录插入到我的开发机器(8+8GB内存的MacBook专业版，2.4+and英特尔核心i5和固态硬盘)上的一个简单表中，使用'model.create!‘的时间从223秒缩短到了223秒。到7.2秒，使用'model.import!‘。|2162564|class+ActiveRecord::Base

++def+self.import!(record_list)
++++raise+ArgumentError+"record_list+not+an+Array+of+Hashes"+unless+record_list.is_a?(Array)+&&+record_list.all?+{%7Crec%7C+rec.is_a?+Hash+}
++++key_list,+value_list+=+convert_record_list(record_list)++++++++
++++sql+=+"INSERT+INTO+#{self.table_name}+(#{key_list.join(",+")})+VALUES+#{value_list.map+{%7Crec%7C+"(#{rec.join(",+")})"+}.join("+,")}"
++++self.connection.insert_sql(sql)
++end

++def+self.convert_record_list(record_list)
++++key_list+=+record_list.map(&:keys).flatten.uniq.sort

++++value_list+=+record_list.map+do+%7Crec%7C
++++++list+=+[]
++++++key_list.each+{%7Ckey%7C+list+<<++ActiveRecord::Base.connection.quote(rec[key])+}
++++++list
++++end

++++return+[key_list,+value_list]
++end
end|2162565|entityMap|0|LINK|mutability|MUTABLE|url|http://www.coffeepowered.net/2009/01/23/mass-inserting-data-in-rails-without-killing-your-performance/^0|S|7|0|0|A|T|1A|I|1X|I|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|D|3|E|5|6|7|18|8|@$A|19|B|1A|F|G]|$A|1B|B|1C|F|G]|$A|1D|B|1E|F|G]]|9|@]|C|$]]|$1|H|3|I|5|6|7|1F|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|1G|8|@]|9|@]|C|$]]|$1|L|3|M|5|N|7|1H|8|@]|9|@]|C|$O|P]]|$1|Q|3|R|5|6|7|1I|8|@]|9|@]|C|$]]|$1|S|3|T|5|6|7|1J|8|@]|9|@]|C|$]]|$1|U|3|V|5|N|7|1K|8|@]|9|@]|C|$O|P]]|$1|W|3|-4|5|6|7|1L|8|@]|9|@]|C|$]]]|X|$Y|$5|Z|10|11|C|$12|13]]]]

Thanks to Chris Heald @cheald for his 2009 <a href="http://www.coffeepowered.net/2009/01/23/mass-inserting-data-in-rails-without-killing-your-performance/">article</a>, with showed me that the best way to go was the multi-row insert command.

Added the following code to my <code>initializers/active_record.rb</code> file, changed my <code>Model.create!(...)</code> calls to <code>Model.import!(...)</code> and away it goes. A couple caveats:

1) It does not validate the data. 
2) It uses the form of the SQL INSERT command that reads like ...

<pre><code>INSERT INTO &lt;table&gt; (field-1, field-2, ...) 
 VALUES (value-1-1, value-1-2, ...), (value-2-1, value-2-2, ...), ...`
</code></pre>

... which may not be the correct syntax for all databases, but it works with Postgres. It would not be difficult to alter the code for the appropriate syntax for your SQL version.

In my particular case, inserting 19K+ records into a simple table on my development machine (MacBook Pro with 8GB RAM, 2.4GHz Intel Core i5 and and SSD) went from 223 seconds using 'model.create!' to 7.2 seconds using a 'model.import!'.

<pre><code>class ActiveRecord::Base

 def self.import!(record_list)
 raise ArgumentError "record_list not an Array of Hashes" unless record_list.is_a?(Array) &amp;&amp; record_list.all? {|rec| rec.is_a? Hash }
 key_list, value_list = convert_record_list(record_list) 
 sql = "INSERT INTO #{self.table_name} (#{key_list.join(", ")}) VALUES #{value_list.map {|rec| "(#{rec.join(", ")})" }.join(" ,")}"
 self.connection.insert_sql(sql)
 end

 def self.convert_record_list(record_list)
 key_list = record_list.map(&amp;:keys).flatten.uniq.sort

 value_list = record_list.map do |rec|
 list = []
 key_list.each {|key| list &lt;&lt; ActiveRecord::Base.connection.quote(rec[key]) }
 list
 end

 return [key_list, value_list]
 end
end
</code></pre>

blocks|key|2162582|text|使用activerecord-import+gem。假设您正在读取CSV文件并生成Product目录，并且希望批量插入1000条记录：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2162583|batch,batch_size+=+[],+1_000+
CSV.foreach("/data/new_products.csv",+:headers+=>+true)+do+%7Crow%7C
++batch+<<+Product.new(row)

++if+batch.size+>=+batch_size
++++Product.import+batch
++++batch+=+[]
++end
end
Product.import+batch|code-block|syntax|javascript|2162584|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/zdennis/activerecord-import^0|15|7|2|J|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

Use the <a href="https://github.com/zdennis/activerecord-import" rel="noreferrer">activerecord-import</a> gem. Let us say you are reading a CSV file and generating a <code>Product</code> catalogue and you want to insert records in batches of 1000:

<pre><code>batch,batch_size = [], 1_000 
CSV.foreach("/data/new_products.csv", :headers =&gt; true) do |row|
 batch &lt;&lt; Product.new(row)

 if batch.size &gt;= batch_size
 Product.import batch
 batch = []
 end
end
Product.import batch
</code></pre>

blocks|key|2162604|text|我开始遇到大量记录(>+10000条)的问题，所以我修改了代码，以便一次处理1000条记录。以下是新代码的链接：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2162605|https://gist.github.com/jackrg/76ade1724bd816292e4e|offset|length|2162606|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1F|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

I started running into problems with large numbers of records (> 10000), so I modified the code to work in groups of 1000 records at a time. Here is a link to the new code: 

<a href="https://gist.github.com/jackrg/76ade1724bd816292e4e" rel="noreferrer">https://gist.github.com/jackrg/76ade1724bd816292e4e</a>

blocks|key|2162612|text|您也可以使用activerecord-insert_many+gem。只需创建一个对象数组！|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2162613|events+=+[{name:+"Movie+Night",+time:+"10:00"},+{name:+"Tutoring",+time:+"7:00"},+...]

Event.insert_many(events)|code-block|syntax|javascript|2162614|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/concordia-publishing-house/activerecord-insert_many^0|6|O|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

You can also use the <a href="https://github.com/concordia-publishing-house/activerecord-insert_many" rel="nofollow noreferrer">activerecord-insert_many</a> gem. Just make an array of objects!

<pre><code>events = [{name: "Movie Night", time: "10:00"}, {name: "Tutoring", time: "7:00"}, ...]

Event.insert_many(events)
</code></pre>

blocks|key|842886|text|使用事务可以大大提高批量插入的速度！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|842887|Model.transaction+do
++++many.times{+Model.create!+}
end|code-block|syntax|javascript|842888|如果涉及多个机型，请对每个受影响的机型执行Model.transaction：|842889|Model1.transaction+do
++++Model2.transaction+do
++++++++many.times+do
++++++++++++m1+=+Model1.create!
++++++++++++m1.add_model2
++++++++end
++++end
end|842890|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Using a transaction speeds up bulk inserts a lot!

<pre><code>Model.transaction do
 many.times{ Model.create! }
end
</code></pre>

If multiple Models are involved, do a Model.transaction for each model, which is affected:

<pre><code>Model1.transaction do
 Model2.transaction do
 many.times do
 m1 = Model1.create!
 m1.add_model2
 end
 end
end
</code></pre>

blocks|key|39294|text|对于Rails6.x，使用insert_all。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|39295|entityMap|0|LINK|mutability|MUTABLE|url|https://edgeapi.rubyonrails.org/classes/ActiveRecord/Persistence/ClassMethods.html#method-i-insert_all^0|D|A|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

For Rails 6.x use <a href="https://edgeapi.rubyonrails.org/classes/ActiveRecord/Persistence/ClassMethods.html#method-i-insert_all" rel="nofollow noreferrer">insert_all</a>.

I found that my <code>Model.create!</code> statements were taking a very long time to run when I added a large number of records at once. Looked at <a href="https://github.com/zdennis/activerecord-import/wiki">ActiveRecord-Import</a> but it didn't work with an array of hashes (which is what I have and which I think is pretty common). How can I improve the performance?

Bulk Insert records into Active Record table

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

当我一次添加大量记录时，我发现运行Model.create!语句需要很长时间。我看过，但它不能与哈希数组一起工作(这就是我所拥有的，我认为这是很常见的)。如何提高性能？

问将记录批量插入到活动记录表中
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将记录批量插入到活动记录表中EN