CODEWITHSUNDEEP
performancenhibernatefluent-nhibernate
Prabu
Prabu

Reputation: 4187

Fluent nHibernate query on Oracle is extremely slow

I'm new to nHibernate and am having some really slow results from a simple select query. Maybe I'm missing something obvious. The situation as follow:

Here's my fluent nHibernate code:

public class Person
{
    public virtual string PersonId { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
}

public class PersonMap : ClassMap<Person>
{
    public PersonMap()
    {
        Schema("MyTestDB");
        Table("Person");
        Id(i => i.PersonId);
        Map(i => i.FirstName);
        Map(i => i.LastName);
    }
}

Here is the code that is suppose to retrieve the actual data:

var sessionFactory = Fluently.Configure().Database(OracleClientConfiguration.Oracle10.ConnectionString(@"User Id=tester;Password=tester99!;Data Source=MyTestDB;").ShowSql()).Mappings(m => m.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly())).BuildSessionFactory();                   

    using (var session = sessionFactory.OpenSession())
    {           
        using (session.BeginTransaction())
        {
            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();

            var person = session.QueryOver<Person>()
                                .Where(p => p.PersonId == "1").SingleOrDefault();

            stopWatch.Stop();

            var ts = stopWatch.Elapsed;
            var time = string.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds/10);

            Console.WriteLine("Retrieved object: Person, Id: {0}, First Name: {1}, Last Name: {2} in [{3}]", person.PersonId, person.FirstName, person.LastName, time);
        }
    }

The PersonId column is indexed and is the primary key.

My attempts to figure this out so far has been to run the same sql generated by nHibernate with ADO.Net. The query ran extremely fast (the stopwatch gets a elapsed time of 0).

Using plsql developer to run the same query on the database gave the same fast results. This suggests to me think it is not the query nor the database.

How can I debug this further? Will nHibernate profiler help with this (I don't have this available to me at the moment)?

Any ideas guys?

Upvotes: 0

Views: 1305

Answers (3)

Prabu
Prabu

Reputation: 4187

The problem ended up being that I didn't specify the datatype for the primary key column, which turned out to be varchar (non-unicode). Turns out you need to specify the datatype for non-unicode columns, as fluent assumes string maps to uni code.

This is how to set the custom type in fluent notation:

 Map(x=>x.PersonId).CustomType("AnsiString");

Upvotes: 0

Paul Turner
Paul Turner

Reputation: 39615

Firstly, you should try capturing a few more points in time throughout your programs execution. You've assumed that it's the NHibernate component, but without more data-points that's going to be hard to prove, especially when your initial test comes back with 0.

Secondly, the big cost in your NHibernate scenario is the call to BuildSessionFactory(). NHibernate is optimized to have cheap session construction, so it expects you to create this factory once and re-use it throughout the lifetime of your program. If you put trace points around this event, you may find your "expense".

Upvotes: 1

Baz1nga
Baz1nga

Reputation: 15579

If I had to take a wild guess here, the issue is not with nhibernate querying the database I think it is the initial cost you are paying to build the sessionFactory and/or session and that is why you are seeing the unusual latency.

Why dont you use Jetbrains dotTrace and see where is the actual performance hit, if its in running the query or something else. Just run a sampling query and you will be able to get the timings with the exact number of calls to each function.

P.S: I have no association with jetbrains just a happy customer recommending the product.

Upvotes: 0

Related Questions