Database table (PostgreSQL) containing 10000 rows:
CREATE TABLE test
(
id bigserial NOT NULL,
text text,
CONSTRAINT test_pkey PRIMARY KEY (id)
)
Classes:
public class Test
{
public virtual int ID { get; set; }
public virtual string Text { get; set; }
}
public class TestMap : ClassMap<Test>
{
public TestMap()
{
Table("test");
Id(x => x.ID, "id");
Map(x => x.Text, "text");
}
}
Here is how I query database:
using (ISession session = SessionBuilder.OpenSession())
{
Stopwatch s = new Stopwatch();
s.Start();
var result = session.QueryOver<Test>().Where(test => test.Text == "aaa").List();
s.Stop();
Console.WriteLine(s.ElapsedMilliseconds); // >150ms
s.Restart();
var result2 = session.QueryOver<Test>().Where(test => test.Text == "bbb").List();
s.Stop();
Console.WriteLine(s.ElapsedMilliseconds); // ~4ms
}
Why the first query takes so long? Is there a way to speed it up?
Quite likely to be caching. You have no indexes that can help with the query, so a full table scan is the only way to get either answer. The first one will fill up buffers and caches, either by the DB (possibly won't for a table scan) but certainly the O/S will cache all or some disk pages. The second query will benefit from those pages being cached at some level.
Craig's suggestion is also a subtle spin on caching & priming - the first time you do anything tends to incur a bit of a hit and you need to find out what. In web apps you can do a bit of priming before the app accepts requests. If this is more of a batch thing where you can't disguise the warm-up then you may just have to live with it or ditch nHibernate.
Take his advice & measure what's going on in Hibernate too.
There's usually overhead with making a connection to a database in which case you will not see with other queries due to connection pooling. Based on the timings you have there, I would say that's the problem you are seeing.
Also, make sure that you are only making your session factory only once and caching it and then opening sessions from that. I don't think you are running into that based on your timing, but with a small enough configuration it could be that as well.
Possible solution 1:
I've been looking at performance issues a few years ago and the problem I found was the initialization of the XML serializer used by NHibernate. I've reported a bug and a fix for it on Jira:
https://nhibernate.jira.com/browse/NH-2958
try my fix on your nhibernate source and see if the problem is resolved. the issue will be fixed in the next release of nhibernate according to the jira system.
Possible solution 2:
if solution 1 isn't working, try setting dynamic updates and dynamic inserts to true in your mapping for each classmapping. NHibernate creates all possible queries when compiling your mappings.
