我需要使用NHibernate批量插入SQLite数据库。该对象的PK是HiLo int32。我试图将NH无状态会话与session.insert一起使用,并且工作正常。但是,我发现使用准备好的命令速度要快30-40%,因此我尝试利用它。
目前,我正在努力分配ID(即NH HiLo)。
我找到了一个关于stackoverflow(NHibernate HiLo ID Generator. Generating an ID before saving)的解决方案-参见方法GenerateIdentifier(),但是它在每次调用时查询和更新数据库,因此不是一个好的选择。
是否有任何方法可以使它按预期的方式工作-当id generator达到hivalue时,它仅向服务器往返1次即可获取新的low和hi值?
public static Int64 TestNHSQLiteBulk(bool newDB)
{
int ProjectCount = 1000000;
var factory = Database.CreateSQLiteSessionFactory(newDB);
Int64 objectCount = 0;
using (var session = factory.OpenStatelessSession())
{
var connection = session.Connection;
using (var transaction = session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
// this should be removed when proper HiLo will be implemented.
var nextProject = session.CreateCriteria<Project>().SetProjection(Projections.Max<Project>(p => p.ProjectId)).UniqueResult();
int startId = (nextProject == null) ? 0 : (int)nextProject + 1;
var command = connection.CreateCommand();
command.CommandText = "Insert INTO Project (ProjectId, ProjectName) Values(?,?)";
var projectIdParameter = command.CreateParameter();
projectIdParameter.ParameterName = "ProjectId";
projectIdParameter.DbType = System.Data.DbType.Int32;
var projectNameParameter = command.CreateParameter();
projectNameParameter.ParameterName = "ProjectName";
projectNameParameter.DbType = System.Data.DbType.String;
command.Parameters.Add(projectIdParameter);
command.Parameters.Add(projectNameParameter);
command.Prepare();
for (int p = startId; p < startId + ProjectCount; p++)
{
var project = new Project()
{
ProjectName = "Project " + p
};
//found on stackoverflow, but is results a roundtrip to server on each call.
GenerateIdentifier(project, Database.savedConfig, session);
//session.Insert(project);
//using prepared command is almost 2x faster!
projectIdParameter.Value = project.ProjectId;
projectNameParameter.Value = project.ProjectName;
command.ExecuteNonQuery();
objectCount++;
}
transaction.Commit();
}
}
return objectCount;
}
//this will get the value and update the hi-lo value repository in the datastore
public static void GenerateIdentifier(object target, NHibernate.Cfg.Configuration conf, IStatelessSession session)
{
var targetType = target.GetType();
var classMapping = conf.GetClassMapping(targetType);
var impl = session.GetSessionImplementation();
var newId = classMapping.Identifier.CreateIdentifierGenerator(impl.Factory.Dialect, classMapping.Table.Catalog, classMapping.Table.Schema,
classMapping.RootClazz).Generate(impl, target);
classMapping.IdentifierProperty.GetSetter(targetType).Set(target, newId);
}
最佳答案
您正在为每个实体创建一个新的HiLogenerator,而不是使用给定的实体。生成器还通常将自身初始化为数据库中的值,因此不需要手动查询该值:
public static Int64 TestNHSQLiteBulk(bool newDB)
{
int ProjectCount = 1000000;
var factory = Database.CreateSQLiteSessionFactory(newDB);
var classMapping = (SingleTableEntityPersister)factory.GetClassMetadata(typeof(Project));
var generator = classMapping.IdentifierGenerator;
Int64 objectCount = 0;
using (var session = factory.OpenStatelessSession())
{
var connection = session.Connection;
using (var transaction = session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
var command = connection.CreateCommand();
command.CommandText = "Insert INTO Project (ProjectId, ProjectName) Values(?,?)";
var projectIdParameter = command.CreateParameter();
projectIdParameter.ParameterName = "ProjectId";
projectIdParameter.DbType = System.Data.DbType.Int32;
var projectNameParameter = command.CreateParameter();
projectNameParameter.ParameterName = "ProjectName";
projectNameParameter.DbType = System.Data.DbType.String;
command.Parameters.Add(projectIdParameter);
command.Parameters.Add(projectNameParameter);
command.Prepare();
for (int p = 0; p < ProjectCount; p++)
{
var projectId = (long)generator.Generate(session.GetSessionImplementation(), null);
//using prepared command is almost 2x faster!
projectIdParameter.Value = projectId;
projectNameParameter.Value = "Project" + projectId;
command.ExecuteNonQuery();
objectCount++;
}
transaction.Commit();
}
}
return objectCount;
}
关于c# - 无需调用session.Insert或session.Save即可获取HiLo号码,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12477866/