使用NHibernate（使用映射按代码）简单的GET非常缓慢 [英] Simple get using NHibernate (using mapping-by-code) is extremely slow

问题描述

所以，我为什么这是如此缓慢江郎才尽。也许你可以提供帮助。
所以我想从Oracle做记录一个简单的获取数据库使用NHibernate映射通过代码。我使用NHibernate的verison 3.3.1.4从的NuGet

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.

下面是映射代码：

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);
    }
}

下面是创建SessionFactory并检索代码。数据

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();
        }

生成的查询如下：

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秒获得访问所检索的对象。这包括创建会话以及

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:

• 为PERSONID coloumn被索引（它是表的主键）。


• 数据库是在服务器上，所以要检查是不是网络进食的时候，我用跑AD0.Net（+的OracleConnection的OracleCommand）上面生成的查询。这大致需要180ms的，这包括创建连接和记录映射到实体类。


• 通过PL / SQL Developer中运行查询（花了大约为32ms）。


• 检查查询计划在生成的查询（确认使用索引扫描独特的，而不是一个全表扫描）。


• 我已经运行对一个类似大小上面的代码SQL2012分贝在我的本地和，它是通过NHibernate的代码快疯了incomparison，围绕180ms的


• nhprof（评估）然，得到以下结果：

• 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:

好像运行查询，结果被从数据库（根据nhprof结果）的返回速度非常快，但也许查询的价值观及其水化成占用了实体时间。我是在什么可以嚼了时间的损失！

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!

我的下一步行动是NHibernate的源连接到解决方案，并通过它的一步，但下载的访问在工作是有限的（甚至是github上！）。在那之前的任何想法。

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?

干杯

更新：所以我得到了NHibernate的源代码在我的项目，并通过它加强。一切都一直很好，直到程序获取移动到这段代码：

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++)              

这是 RS .Read（）的执行吃掉的时候，其中RS是oracle的DataReader。这段代码是在Loader.cs文件中的 DoQuery 功能。

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.

奇怪的是如果传入的查询是一种非参数化动态查询（如选择...从一个人那里PERSONID ='1 ），执行点亮速度快（1毫秒〜），但如果它的参数设置（如从一个人选择...其中PERSONID =：P1 ），那么它的速度慢得离谱。因为我想利用NHibernate的的力量，我需要使用生成的参数化查询。我仍然试图找出为什么甲骨文读者

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.

从本质上讲，在NHibernate的参数化查询与Oracle的Microsoft驱动程序是瓶颈的原因。

Essentially, parameterized queries in NHibernate with the microsoft driver for oracle was the reason for the bottleneck.

他接着提出两个解决方案：

He goes on to share two solutions:

1. 替换与Oracle开发的Oracle数据客户端驱动程序当前的Microsoft Oracle驱动程序。运行使用新的驱动程序相同的查询消除了性能bottelneck。对于这个工作，你需要有Oracle数据访问组件（ODAC）安装了您打算解雇查询机器上。

1. 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.

在设置对VARCHAR列的查询时通过NHibernate的查询参数使用query.SetAnsiString而不是query.SetParameter。

When setting a parameter on the query via NHibernate use "query.SetAnsiString" instead of "query.SetParameter" when querying against VARCHAR columns.

我已经验证了这两种解决方案，他们都工作。解决方法一就是我跟去了，因为我使用的映射，通过代码，我离开查询生成NHibernate的。

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（使用映射按代码）简单的GET非常缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！