CODEWITHSUNDEEP
c#nhibernate
JiBéDoublevé
JiBéDoublevé

Reputation: 4269

Very slow update with nHibernate with a SQLite database

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();
}

Upvotes: 2

Views: 1296

Answers (3)

csanchez
csanchez

Reputation: 121

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.

Upvotes: 1

Nikola Radosavljević
Nikola Radosavljević

Reputation: 6911

You should not flush your session every time you make a change to data. You should let NHibernate buffer updates and flush bulk changes. Otherwise, they would have made flush implicit operation and not have you worry about it.

Upvotes: 0

Steve Py
Steve Py

Reputation: 34698

Is this performance hit just with the first commit to the DB. SQLite may have some spin-up time.

You say "session per method" yet it looks like you're using a module-level session variable. The more that is done with a Session instance, the slower it will become due to caching. If this is going to be more of a batch operation, consider the StatelessSession.

Also check the logging configuration for NHibernate + Log4Net. The more detailed the logging, the slower NH will respond.

Upvotes: 0

Related Questions