Commit Is VERY Slow In My NHibernate / SQLite Project

I've just started doing some real-world performance testing on my Fluent NHibernate / SQLite project, and am experiencing some serious delays when when I Commit to the database. B

Solution 1:

Your transaction should be around the whole operation, not each save.

You would also then benefit from enabling ado.net batching: 1000

Solution 2:

I think using session.evict() only covers the symptom. You haven't posted the GetSession()-Method but the comment above and the remark that session.clear() breaks your lazy loading makes me guess you are using one session for the whole application.
This is extremely inefficient and will slow down your app the longer it runs. Creating a new session on the other hand is really cheap and will provide you with a clean and fast session only handling the objects you want to.
IMO you should think about declarative transaction management. Personally i prefer Springs-TX-Management but there are also other clever solutions that aren't that method-based like for example castle's ActiveRecord.

Solution 3:

You say that you have a List of measurement objects, but you are saving them 1 at a time. This would be fine, if all of the saves were in 1 transaction, but you have each save wrapped in a transaction. This will hurt your performance no matter how large the DB is.

Solution 4:

Are you using autoincrement primary keys? This will cause NHibernate to perform a select after each insert to populate the primary key property on the object. I'm not sure how it does it for SQLite but your statement that the problem gets worse as the database grows points to this as a probable root cause.

Your plan to use a profiler is the best course of action. The NHibernate Profiler is excellent and has a trial period that will allow you to use it to troubleshoot this problem.

Solution 5:

Nhibernate is never going to be fast.

Some older versions of nhibernate aggressively close the session to the database. This causes it to open/close the sqlite database a LOT (which is very slow). I believe newer versions include an option to keep the nhibernate session open longer. If you're using a memory database it causes everything to be lost as soon as it closes.

Problem using SQLite :memory: with NHibernate