Very slow update with nHibernate with a SQLite database

Question

I'm using nHibernate with one session per method. I'm using Castle Dynamic Proxy to open a session and a transaction before executing the method and I commit the transaction and close the session right after the execution of this method.

I'm using a SQLite database and if set cascade to SaveUpdate or None doesn't change anything. nHIbernate is configured with Fluent nHibernate with the default configuration.

When I flush the session, it takes more than 2 seconds.

public void AddNewChild(LightPatientDto patient, LightPatientDto child)
{
    var ePatient = this.Session.Load<Patient>(patient.Id);
    var eChild = this.Session.Load<Patient>(child.Id);

    switch (ePatient.Gender)
    {
        case Gender.Male:
            eChild.Father = ePatient;
            break;
        case Gender.Female:
            eChild.Mother = ePatient;
            break;
        default:
            Assert.FailOnEnumeration(eChild.Gender);
            break;
    }

    this.Session.Update(eChild);
    this.Session.Flush(); // <== takes more than 2 seconds
}

NHibernate profiler gives me this SQL is executed in 2008 ms. When I copy paste this SQL using a SQLite administration tool, it's executed in 90 ms.

UPDATE Patient
SET    BirthDate = '1964-05-06T00:00:00.00' /* @p0 */,
       Fee = 0 /* @p1 */,
       Height = 0 /* @p2 */,
       InscriptionDate = '2007-05-21T00:00:00.00' /* @p3 */,
       PlaceOfBirth = 'xxxxxx' /* @p4 */,
       PrivateMail = '' /* @p5 */,
       PrivateMobile = NULL /* @p6 */,
       PrivatePhone = '0496/xx.xx.xx' /* @p7 */,
       Reason = 'diabète' /* @p8 */,
       Father_id = 2 /* @p9 */,
       Insurance_id = 1 /* @p10 */,
       Mother_id = NULL /* @p11 */,
       Practice_id = 1 /* @p12 */,
       Profession_id = NULL /* @p13 */,
       Reputation_id = 1 /* @p14 */
WHERE  Person_id = 3 /* @p15 */

How can I optimise the execution time?

EDIT 1

As @csanchez suggested, I've succeeded to add Dynamic Update for my entity. Now when I'm looking the SQL into NH Profiler, I've got this:

UPDATE Patient
SET    Father_id = 2 /* @p0 */
WHERE  Person_id = 21 /* @p1 */

Which is a lot optimised! But, the execution time is ... 1852 ms o_O

In the debugger, I see that it is committing the transaction that takes time... And I have no clue why it is so slow...

EDIT 2

Each patient has medical pictures that are stored into a table as bytes arrays. Here's the SQL for the table creation (done by Fluent nHibernate)

CREATE TABLE Picture (
  Id               integer PRIMARY KEY AUTOINCREMENT,
  Bitmap           blob,
  Creation         datetime,
  LastUpdate       datetime,
  Notes            text,
  IsImported       bool,
  Tag_id           bigint,
  Patient_id       bigint,
  ThumbnailBitmap  blob,
  /* Foreign keys */
  FOREIGN KEY (Patient_id)
    REFERENCES Patient(), 
  FOREIGN KEY (Tag_id)
    REFERENCES "Tag"()
);

If I drop the data with DELETE Picture and restart the application, the update is light speed fast.

It seems like nHibernate try to be smart and does something that slows down everything. But event if I execute a pure SQL query with this code, the update still takes more than one second:

using (var tx = this.Session.BeginTransaction())
{   
    var sql = "UPDATE Patient SET Father_id = 2 WHERE Person_id = 21";     
    var query = this.Session.CreateSQLQuery(sql);
    query.ExecuteUpdate();

    tx.Commit();
}

csanchez csanchez · Accepted Answer · 2012-11-07T13:08:06

If the only thing you update is the Parent or the Mother you could use dynamic-update="true", in the mapping file of the Patient.

Very slow update with nHibernate with a SQLite database

3 Answers