Commit is VERY slow in my NHibernate / SQLite project
- by Tom Bushell
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. By serious, I mean taking 20 - 30 seconds to Commit 30 K of data!
This delay seems to get worse as the database grows. When the SQLite DB file is empty, commits happen almost instantly, but when it grows to 10 Meg, I see these huge delays.
The database has 16 tables, averaging 10 columns each.
One possible problem is that I'm storing a dozen or so IList members, but they are typically only 200 elements long. But this is a recent addition to Fluent NHibernate automapping, which stores each float in a single table row, so maybe that's a potential problem.
Any suggestions on how to track this down? I suspect SQLite is the culprit, but maybe it's NHibernate?
I don't have any experience with profilers, but am thinking of getting one. I'm aware of NHibernate Profiler - any recommendations for profilers that work well with SQLite?
Here's the method that saves the data - it's just a SaveOrUpdate call and a Commit, if you ignore all the error handling and debug logging.
public static void SaveMeasurement(object measurement)
{
Debug.WriteLine("\r\n---SaveMeasurement---");
// Get the application's database session
var session = GetSession();
using (var transaction = session.BeginTransaction())
{
try
{
session.SaveOrUpdate(measurement);
}
catch (Exception e)
{
throw new ApplicationException(
"\r\n SaveMeasurement->SaveOrUpdate failed\r\n\r\n", e);
}
try
{
Debug.WriteLine("\r\n---Commit---");
transaction.Commit();
Debug.WriteLine("\r\n---Commit Complete---");
}
catch (Exception e)
{
throw new ApplicationException(
"\r\n SaveMeasurement->Commit failed\r\n\r\n", e);
}
}
}
