I just started doing real-world performance testing on my Fluent NHibernate / SQLite project, and I am having serious delays when I execute a database transaction. Seriously, I mean it takes 20 - 30 seconds to lock 30K of data!
This delay seems to get worse as the database grows. When the SQLite DB file is empty, transactions occur almost instantly, but when it grows to 10 megabytes, I see these huge delays.
The database has 16 tables, an average of 10 columns.
One possible problem is that I keep a dozen members IList<float>, but they usually only have 200 elements. But this is the latest addition to Fluent NHibernate's automatic release, in which each float is stored on a single row in the table, so this may be a potential problem.
Any suggestions on how to track this? I suspect that SQLite is the culprit, but maybe this is NHibernate?
I have no experience with profilers, but I am thinking about getting it. I know NHibernate Profiler - any recommendations for profilers that work well with SQLite?
Edit
Some testing shows that this is not the size of the database, which causes a slowdown - it depends on how many Saves I have made since I started my program. The hold time for the first save is about 300 ms and goes over 1000 ms to the 50th save.
I keep one session open all the time - maybe I need explicit Flush logic?
In addition, I downloaded the NHibernate Profiler. He gave me a warning about the "large number of individual letters" for my IList members. The warning description suggests enabling batch processing, but SQLite does not support this, as far as I can tell.
Multiquery support is also mentioned, so I will read about it.
/ Edit
, - SaveOrUpdate Commit, .
public static void SaveMeasurement(object measurement)
{
var session = GetSession();
using (var transaction = session.BeginTransaction())
{
session.Save(measurement);
transaction.Commit();
}
}