在我的NHibernate/SQLite项目中，提交速度非常慢 [英] Commit is VERY slow in my NHibernate / SQLite project

问题描述

我刚刚开始在Fluent NHibernate/SQLite项目上进行一些实际的性能测试，并且当我提交数据库时遇到了一些严重的延迟.认真地说，我的意思是花20到30秒来提交30 K数据！

I've just started doing some real-world performance testing on my Fluent NHibernate / SQLite project, and am experiencing some serious delays when when I Commit to the database. By serious, I mean taking 20 - 30 seconds to Commit 30 K of data!

随着数据库的增长，这种延迟似乎越来越严重.当SQLite DB文件为空时，提交几乎立即发生，但是当它增大到10 Meg时，我看到了这些巨大的延迟.

This delay seems to get worse as the database grows. When the SQLite DB file is empty, commits happen almost instantly, but when it grows to 10 Meg, I see these huge delays.

该数据库有16个表，每个表平均10列.

The database has 16 tables, averaging 10 columns each.

一个可能的问题是我存储了十几个IList<float>成员，但是它们通常只有200个元素.但这是Fluent NHibernate自动映射的新增功能，它将每个float都存储在单个表行中，因此这可能是一个潜在的问题.

One possible problem is that I'm storing a dozen or so IList<float> members, but they are typically only 200 elements long. But this is a recent addition to Fluent NHibernate automapping, which stores each float in a single table row, so maybe that's a potential problem.

有关如何跟踪此问题的任何建议?我怀疑是SQLite的罪魁祸首，但也许是NHibernate吗?

Any suggestions on how to track this down? I suspect SQLite is the culprit, but maybe it's NHibernate?

我在Profiler方面没有任何经验，但是我正在考虑找一个.我知道 NHibernate Profiler -有关与SQLite配合使用的Profiler的任何建议吗?

I don't have any experience with profilers, but am thinking of getting one. I'm aware of NHibernate Profiler - any recommendations for profilers that work well with SQLite?

修改

更多测试表明，导致速度下降的不是数据库大小，而是取决于自启动程序以来执行的保存次数.第一次保存的提交时间约为300毫秒，到第50次保存时的提交时间超过了1000毫秒.

Some more testing indicates that it's not the size of the database that causes the slowdown - it's dependent on how many Saves I've done since I started my program. Commit time for the first Save is about 300 ms, and goes to over 1000 ms by the 50th Save.

我一直都在打开一个会话-也许我需要一些显式的Flush逻辑?

I'm keeping a single Session open all the time - maybe I need some explicit Flush logic?

我还下载了NHibernate Profiler.它给了我有关IList成员大量个人写入"的警报.警报说明建议打开批处理，但据我所知，SQLite不支持该批处理.

Also, I downloaded the NHibernate Profiler. It gave me an alert about "Large number of individual writes" for my IList members. The alert description suggested turning on batching, but SQLite does not support that, as far as I can tell.

还提到了对多查询的支持，所以我将继续阅读.

Also mentioned Multiquery support, so I'm going to read up on that.

/编辑

这里是保存数据的方法-如果您忽略所有错误处理和调试日志记录，那么这只是一个SaveOrUpdate调用和一个Commit.

Here's the method that saves the data - it's just a SaveOrUpdate call and a Commit, if you ignore all the error handling and debug logging.

    public static void SaveMeasurement(object measurement)
    {
        // Get the application's database session
        var session = GetSession();
        using (var transaction = session.BeginTransaction())
        {
            session.Save(measurement);
            transaction.Commit();
        }
    }

推荐答案

我认为使用session.evict()仅能解决症状.您尚未发布GetSession()方法，但是上面的注释以及session.clear()破坏了延迟加载的注释使我猜测您正在整个应用程序使用一个会话.
这效率极低，并且运行时间越长，速度越慢.另一方面，创建新会话确实很便宜，并且将为您提供干净快速的会话，仅处理您要处理的对象.
IMO，您应该考虑声明式事务管理.我个人更喜欢 Springs -TX-Management，但是还有其他一些不是基于方法的聪明解决方案例如城堡的 ActiveRecord .

I think using session.evict() only covers the symptom. You haven't posted the GetSession()-Method but the comment above and the remark that session.clear() breaks your lazy loading makes me guess you are using one session for the whole application.
This is extremely inefficient and will slow down your app the longer it runs. Creating a new session on the other hand is really cheap and will provide you with a clean and fast session only handling the objects you want to.
IMO you should think about declarative transaction management. Personally i prefer Springs-TX-Management but there are also other clever solutions that aren't that method-based like for example castle's ActiveRecord.

这篇关于在我的NHibernate/SQLite项目中，提交速度非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！