CODEWITHSUNDEEP
c#sqlitenhibernate
Signum
Signum

Reputation: 875

NHibernate and SQLite - very slow transaction commit

Here is the code:

using (var session = NHibernateSessionFactory.OpenSession()) // 0ms
{
    using (var transaction = session.BeginTransaction()) // 0ms
    {
        session.Save(new Test() { Text = "..." }); // ~2ms
        transaction.Commit(); // 100-150ms!!
    }
}

Log output:

...
2013-06-20 17:45:48,857 [DEBUG] [NHibernate.Impl.SessionImpl] - before transaction completion
2013-06-20 17:45:48,989 [DEBUG] [NHibernate.Transaction.AdoTransaction] - IDbTransaction Committed
...

Why does it takes so long? Same code with PostgreSQL results in ~5-10ms...

Upvotes: 0

Views: 1336

Answers (2)

Signum
Signum

Reputation: 875

Finally, I've found solution: PRAGMA synchronous=off; Just pass it into connection string:

var cfg = Fluently.Configure()
                        .Database(SQLiteConfiguration.Standard.ConnectionString("Data Source=db.sqlite;Version=3;PRAGMA synchronous=off;"))
                        .Mappings(m => m.AutoMappings.Add(CreateMappings));

More info: http://www.sqlite.org/faq.html#q19

P.S. Thank you, Sergio. Your tip was also very helpful.

Upvotes: 0

Sergio A.
Sergio A.

Reputation: 401

The time took for the transaction to commit might have nothing to do with NHibernate. Remember that the instruction is sent to the DBMS, in this case SQLite, and therefore, the processing time is spent in SQLite. Do you have other operations running in the DB concurrently? When committing a transaction SQLite has to obtain a Lock on the DB, although should be on the affected tables only, but this lock cause cause that the DBMS have to wait for other transactions to finish first. Here is a little bit more information about the commit process on SQLite. Hope this helps.

http://sqlite.org/atomiccommit.html

Upvotes: 1

Related Questions