.net 使用 NHibernate 加速批量插入操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9943967/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Speed up bulk insert operations with NHibernate
提问by deamon
I want to speed up bulk insertoperations with NHibernate 3.2 on Oracle 11g. To do this I tried
我想insert在 Oracle 11g 上使用 NHibernate 3.2加速批量操作。为此，我试过
Session.Save(entity);
Session.Flush();
Session.Clear();
... in my foreachloop but got an exception caused by objects missing in the Session:
...在我的foreach循环中，但由于会话中缺少对象而导致异常：
failed to lazily initialize a collection of role: MyClass.PropertyX, no session or session was closed
未能延迟初始化角色集合：MyClass.PropertyX，没有会话或会话被关闭
Another attempt was to set the batch size:
另一种尝试是设置批量大小：
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property>
<property name="connection.connection_string">xxx</property>
<property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
<property name="adonet.batch_size">50</property>
<property name="query.substitutions">true=1, false=0</property>
<property name="proxyfactory.factory_class">NHibernate.Bytecode.DefaultProxyFactoryFactory, NHibernate</property>
</session-factory>
</hibernate-configuration>
additionally I set Session.SetBatchSize(50)in my code an got the following exception:
另外我Session.SetBatchSize(50)在我的代码中设置了以下异常：
No batch size was defined for the session factory, batching is disabled. Set adonet.batch_size = 1 to enable batching.
没有为会话工厂定义批处理大小，禁用批处理。设置 adonet.batch_size = 1 以启用批处理。
The only location where this exception is thrown is NonBatchingBatcher, so it looks like my session has the wrong batcher.
引发此异常的唯一位置是NonBatchingBatcher，所以看起来我的会话有错误的批处理器。
What is wrong here? How can I speed up batch inserts with NHibernate (without using statlese sessions)?
这里有什么问题？如何使用 NHibernate 加速批量插入（不使用状态会话）？
回答by Low Flying Pelican
Following should work,
以下应该工作，
var testObjects = CreateTestObjects(500000);
var stopwatch = new Stopwatch();
stopwatch.Start();
using (IStatelessSession session = sessionFactory.OpenStatelessSession())
using (ITransaction transaction = session.BeginTransaction())
{
foreach (var testObject in testObjects)
session.Insert(testObject);
transaction.Commit();
}
stopwatch.Stop();
var time = stopwatch.Elapsed;
参考：http: //nhibernate.info/blog/2008/10/30/bulk-data-operations-with-nhibernate-s-stateless-sessions.html
回答by Dav
All the above tips are very valid and very useful. Wanted to add one to the collection: disable logging. Having your SQL shown in the console slows you down noticeably, as does profiling using NHProf, auto commenting and pretty-formatting the SQL logged via NLog or log4net. In our case setting:
以上所有提示都非常有效且非常有用。想在集合中添加一个：禁用日志记录。在控制台中显示您的 SQL 会显着减慢您的速度，使用 NHProf 进行分析、自动注释和通过 NLog 或 log4net 记录的 SQL 的漂亮格式化也是如此。在我们的案例设置中：
cfg.AutoCommentSql = false;
cfg.LogFormattedSql = false;
decreased our bulk insert time from ~6 seconds to just over 1 second. So while logging will potentially help you nail down more serious problems, it comes with a performance hit of its own!
将我们的批量插入时间从约 6 秒减少到 1 秒多一点。因此，虽然日志记录可能会帮助您解决更严重的问题，但它本身也会对性能造成影响！
回答by nalaiqChughtai
- http://davybrion.com/blog/2008/10/bulk-data-operations-with-nhibernates-stateless-sessions
- http://martin.podval.eu/2010/12/nhibernate-performance-isssues-insert.html
- http://davybrion.com/blog/2008/10/bulk-data-operations-with-nhibernates-stateless-sessions
- http://martin.podval.eu/2010/12/nhibernate-performance-isssues-insert.html
Second URL having worth to read.
值得一读的第二个 URL。
回答by bernhardrusch
Why are you clearing the session ?
你为什么要清除会话？
I think you shouldn't clear the session in the loop. To make sure that the changes are written into the database I'd rather use a transaction.
我认为你不应该清除循环中的会话。为了确保将更改写入数据库，我宁愿使用事务。
Pseudocode:
伪代码：
foreach (var i in allElements)
{
using (var tx = session.BeginTransaction())
{
... do what you have to do with the object
tx.Commit();
}
}
To speed things up there are other things which may help - you have to define what you really want to do in the loop.
为了加快速度，还有其他一些可能会有所帮助的事情 - 您必须定义您真正想要在循环中做什么。
回答by AlexDev
I know the question was about Oracle but for SQL server I was going to write a routine to take the class mappings and generate a DataTable to be used by SqlBulkInsert, but I found out someone already did this.
我知道问题是关于 Oracle 的，但是对于 SQL 服务器，我打算编写一个例程来获取类映射并生成一个供 SqlBulkInsert 使用的 DataTable，但我发现有人已经这样做了。
https://kaylaniam.wordpress.com/2015/03/13/nhibernate-and-sqlbulkcopy/
https://kaylaniam.wordpress.com/2015/03/13/nhibernate-and-sqlbulkcopy/
This is probably the fastest way to do bulk inserts on SQL Server.
这可能是在 SQL Server 上进行批量插入的最快方法。
回答by jdagosta
Code below works for me on inserting several composite entities
下面的代码适用于我插入多个复合实体
public static void SqlBulkInsert(this ISession session, DataTable dataTable, string tableName)
{
var conn = (SqlConnection)session.Connection;
using (var cmd = new SqlCommand())
{
session.Transaction.Enlist(cmd);
using (var copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.FireTriggers, cmd.Transaction))
{
copy.BulkCopyTimeout = 10000;
copy.DestinationTableName = tableName;
foreach (DataColumn column in dataTable.Columns)
{
copy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
copy.WriteToServer(dataTable);
copy.Close();
}
}
}
You will have to create a method to fill a DataTable object from the composite entity object you want to persist.
您必须创建一个方法来从要保留的复合实体对象填充 DataTable 对象。
