简单的使用NHibernate（使用映射的代码）是非常慢 [英] Simple get using NHibernate (using mapping-by-code) is extremely slow
问题描述
所以我已经用完了为什么这么慢的想法。也许你可以帮助。
所以我试图做一个简单的记录从一个oracle db使用nHibernate映射的代码。我正在使用nuget的nHibernate verison 3.3.1.4。
这里是映射代码：
public class Person
{
public virtual PersonKey Key {get;组; }
public virtual string FirstName {get;组; }
public virtual string LastName {get;组; }
}
public class PersonKey
{
public virtual string PersonId {get;组; }
public override bool Equals（object obj）
{
if（obj == null）
return false;
var t = obj as PersonKey;
if（t == null）
return false;
if（PersonId == t.PersonId）
return true;
return false;
}
public override int GetHashCode（）
{
return（PersonId）.GetHashCode（）;
}
}
public class PersonMap：ClassMapping< Person>
{
public PersonMap（）
{
Schema（MyDB）;
表（Person）;
ComponentAsId（id => id.Key，idMapper => idMapper.Property（p => p.PersonId））;
Property（i => i.FirstName）;
Property（i => i.LastName）;
}
}
这里是创建sessionfactory并检索数据。
var mapper = new ModelMapper
var cfg = new Configuration（）;
mapper.AddMappings（Assembly.GetExecutingAssembly（）。GetExportedTypes（））;
cfg.DataBaseIntegration（c =>
{
c.ConnectionString = @User Id = user; Password = password; Data Source = MyDB;;
c.Driver< OracleClientDriver>（）;
c.Dialect< Oracle10gDialect>（）;
c.LogSqlInConsole = true;
c.LogFormattedSql = true;
c.AutoCommentSql = true;
}）;
cfg.AddMapping（mapper.CompileMappingForAllExplicitlyAddedEntities（））;
var sessionFactory = cfg.BuildSessionFactory（）;
stopwatch.Stop（）;
Console.WriteLine（构建会话工厂：{0}，stopwatch.ElapsedMilliseconds）;
stopwatch.Restart（）;
Person entity = null;
使用（var session = sessionFactory.OpenSession（））
使用（var tx = session.BeginTransaction（））
{
entity = Get（Person，new PersonKey（）{PersonId =1}）;
tx.Commit（）;
}
生成的查询如下：
SELECT person0_.PersonId as PersonId0_0_，
person0_.FirstName as FirstName0_0_，person0_.LastName as LastName0_0_，
From MyDB.Person person0_
WHERE person0_.PersonId =：p0;
：p0 ='1'
平均需要大约80-100秒访问检索到的对象。
因此，我寻找的一些明显的事情：
- personId coloumn已建立索引（它是表格的主键）。
- 数据库位于服务器上，因此要检查网络是否正在使用时间，I运行上面生成的查询使用AD0.Net（Oracleconnection + Oraclecommand）。这大约需要180ms，这包括创建连接并将记录映射到实体类。
- 通过PL / SQL开发人员查询（耗时约32ms）。
- 在生成的查询上检查查询计划（确认使用索引唯一扫描，而不是全表扫描）。
- 我针对类似大小的代码运行了上述代码
- 通过nhprof（评估），得到以下结果：
似乎查询被运行，并且结果从数据库返回非常快（根据nhprof结果），但也许它的查询的值水化到正在占用的实体时间。我在一个可以嚼了时间的损失！
我的下一步是将nHibernate源附加到解决方案，并通过它，但下载访问工作是有限的（甚至到github！）。
更新：在我的项目中获得了nHibernate源并通过它。一切都很顺利，直到程序得到这段代码：
for（count = 0; count< maxRows& ;& rs.Read（）; count ++）
它在 rs .Read（）表示执行过程占用了时间，其中rs是oracle datareader。这段代码在Loader.cs文件中的 DoQuery 函数中。
奇怪的是如果传入的查询是非参数化动态查询（例如 select ... from person where personid ='1 ' c>），执行是快速（〜1ms），但如果其参数化（例如 select ... from person where personid =：p1 ），那么它是可笑的慢。由于我想利用nHibernate的力量，我需要使用生成的参数化查询。我仍然在试图找出为什么oracle阅读器
发现问题和解决方案的博客此处由Deep Shah。
本质上，NHibernate中的参数化查询与oracle的微软驱动程序是瓶颈的原因。
他接着分享两个解决方案：
-
当前Microsoft oracle驱动程序与oracle开发的Oracle Data Client驱动程序。使用新驱动程序运行相同的查询消除了性能瓶颈。为此，您将需要在要从中进行查询的计算机上安装Oracle数据访问组件（ODAC）。
-
参数通过NHibernate在查询VARCHAR列时使用query.SetAnsiString而不是query.SetParameter。
我已经验证了这两个解决方案，他们都工作。解决方案一是我去的，因为我使用映射的代码，我离开的查询生成nHibernate。
So i've run out of ideas as to why this is so slow. Maybe you can help. So I am trying to do a simple get on record from an oracle db using nHibernate mapping by code. I'm using nHibernate verison 3.3.1.4 from nuget.
Here is the mapping code:
public class Person
{
public virtual PersonKey Key { get; set; }
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }
}
public class PersonKey
{
public virtual string PersonId { get; set; }
public override bool Equals(object obj)
{
if (obj == null)
return false;
var t = obj as PersonKey;
if (t == null)
return false;
if (PersonId == t.PersonId)
return true;
return false;
}
public override int GetHashCode()
{
return (PersonId).GetHashCode();
}
}
public class PersonMap : ClassMapping<Person>
{
public PersonMap()
{
Schema("MyDB");
Table("Person");
ComponentAsId(id => id.Key, idMapper => idMapper.Property(p => p.PersonId));
Property(i => i.FirstName);
Property(i => i.LastName);
}
}
Here is the code to create the sessionfactory and retrieve the data.
var mapper = new ModelMapper();
var cfg = new Configuration();
mapper.AddMappings(Assembly.GetExecutingAssembly().GetExportedTypes());
cfg.DataBaseIntegration(c =>
{
c.ConnectionString = @"User Id=user;Password=password;Data Source=MyDB;";
c.Driver<OracleClientDriver>();
c.Dialect<Oracle10gDialect>();
c.LogSqlInConsole = true;
c.LogFormattedSql = true;
c.AutoCommentSql = true;
});
cfg.AddMapping(mapper.CompileMappingForAllExplicitlyAddedEntities());
var sessionFactory = cfg.BuildSessionFactory();
stopwatch.Stop();
Console.WriteLine("Building session factory: {0}", stopwatch.ElapsedMilliseconds);
stopwatch.Restart();
Person entity = null;
using (var session = sessionFactory.OpenSession())
using(var tx = session.BeginTransaction())
{
entity = (Person) session.Get("Person", new PersonKey(){PersonId = "1"});
tx.Commit();
}
The query generated is as follows:
SELECT person0_.PersonId as PersonId0_0_,
person0_.FirstName as FirstName0_0_, person0_.LastName as LastName0_0_,
FROM MyDB.Person person0_
WHERE person0_.PersonId=:p0;
:p0 = '1'
It takes roughly 80-100 seconds on average to get access to the retrieved object. This includes creating the session as well.
So some obvious things I looked for:
- The personId coloumn is indexed (it is the primary key of the table).
- The database is on a server, so to check it is not the network eating the time, I ran the above generated query using AD0.Net (Oracleconnection + Oraclecommand). This roughly takes 180ms this includes creating the connection and mapping the record to entity class.
- Ran the query via PL/SQL developer (took about 32ms).
- Check the query plan on the generated query (confirmed the use of the index unique scan and not a full table scan).
- I've run the above code against a similar sized sql 2012 db on my local and, it's crazy fast incomparison, around 180ms via the NHibernate code.
- Ran nhprof (evaluation) and got the following results:
It seems like the query is run and the results are returned very fast from the database (according to nhprof results), but maybe its the hydration of the query's values into the entity that is taking up the time. I am at a loss at what could be chewing up the time!
My next move is to attach the nHibernate source to the solution and step through it, but download access at work is limited (even to github!). Till then any ideas?
Cheers.
Update: So I've got the nHibernate source in my project and stepped through it. Everything is moving along nicely until the program gets to this piece of code:
for (count = 0; count < maxRows && rs.Read(); count++)
It is on rs.Read() that the execution eats up the time, where rs is the oracle datareader. This piece of code is in the DoQuery function in the Loader.cs file.
The odd thing is if the query passed in is a non-parameterised dynamic query (e.g. select ... from person where personid = '1'), the execution is lighting fast (~ 1ms), but if its parameterised (e.g. select ... from person where personid = :p1), then it's ridiculously slow. Since I want to harness the power of nHibernate, I need to use the generated parameterised queries. I am still trying to figure out why the oracle reader
Found the issue and solution blogged here by Deep Shah.
Essentially, parameterized queries in NHibernate with the microsoft driver for oracle was the reason for the bottleneck.
He goes on to share two solutions:
Replace the current Microsoft oracle driver with an oracle developed Oracle Data Client Driver. Running the same query using new driver eliminates the performance bottelneck. For this to work you will need to have Oracle Data Access Components (ODAC) installed on the machine from which you intend to fire the query.
When setting a parameter on the query via NHibernate use "query.SetAnsiString" instead of "query.SetParameter" when querying against VARCHAR columns.
I have verified both solutions and they both work. Solution one is the one I went with, since I am using mapping-by-code and I am leaving the query generation to nHibernate.
这篇关于简单的使用NHibernate（使用映射的代码）是非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
