NHibernate.ISession.Flush() is taking a long time after a Delete()-Operation

Question

In my application I would like to clear/empty a table (which is the only one) in my SQLite-DB. I am programming in C#. _session is of type NHibernate.ISession. Here is my code:

string queryFmt = "FROM {0}"; string query = String.Format(queryFmt, typeName); _session.Delete(query);
_session.Flush();

My example-DB consists of over 5000 entries (the s3db-file is about 750KB big). The Flush()-Method needs more than 6 minutes. (When I execute the delete-operation in the SQLite Administrator it takes less than a second.)

How can I empty the table faster?

Answer 1

If you set nhib to show_sql=true in the config, what sql is output?

---

After your response, you can see that nhib is taking so long because it's not bulk deleting the rows.

Ayende has a post explaining it here.

Answer 2

Use an ExecuteUpdate on a hql query Here's an example:

using(var session = sessionFactory.OpenSession())
{

    String hqlDelete = string.Format("delete {0} t",typename);
    int deletedEntities = session.CreateQuery( hqlDelete ).ExecuteUpdate();
    session.Close();
}