NHibernate SQL查询慢 [英] NHibernate SQL query slow

问题描述

我正在使用LINQ to NH来获取有关应用启动的大量数据.我专门添加了ToList()来强制立即执行查询:

I am using LINQ to NH to get a bunch of data on app startup. I specifically added ToList() to force immediate query execution:

Group group = GetGroup();
Log.Info("started");
var list = Session.Linq<Data>()
    .Where(p => p.Group.Id == group.Id)
    .OrderByDescending(p => p.Stamp.Counter) /* Stamp is composite mapping */
    .Select(p => new
    {
        Counter = p.Stamp.Counter,
        Status = p.Status,
    })
    .Take(4000)
    .ToList();
Log.Info("done");

检查DEBUG日志以查找NHibernate.SQL记录器，将得到以下SQL，这与预期的一样(当我开始监视时，此查询将在SQL Profiler中弹出):

Checking the DEBUG log for NHibernate.SQL logger gives the following SQL, as expected (and this same query pops out in SQL Profiler, when I start monitoring):

SELECT top 4000 this_.Counter as y0_, this_.Status as y1_
FROM [Data] this_ 
LEFT OUTER JOIN [Group] group1_ ON this_.Group_id=group1_.Id
WHERE group1_.Id = @p0 
ORDER BY this_.Counter desc; @p0 = 1

问题是，从我的应用程序调用该查询需要2分钟才能完成，而在SSMS中执行该查询需要0.5秒！实际上，在应用程序等待查询完成时，我可以在SSMS中执行它并立即获得结果.

The problem is that this query takes 2 minutes to complete when invoked from my app, compared to 0.5s when executed in SSMS! Actually, while the app is waiting for the query to complete, I can execute it in SSMS and get results instantly.

您认为这种差异来自何处?

Where do you think this difference comes from?

推荐答案

由于您的应用程序信息不多，我只能猜测.

Sinces there is not much information about your application, I only can guess.

NH的性能问题通常是由刷新缓存引起的.每次查询前都会刷新缓存.当会话中有很多实体时，可能要花费很多时间.请尝试以下操作:

Performance problems with NH typically occur caused by flushing the cache. The cache is flushed before each query. When there are lots of entities in the session, it may take quite a lot of time. Try the following:

Log.Info("Flushing");
Session.Flush();
Session.FlushMode = FlushMode.Never;

Log.Info("Query");
var list = Session.Linq<Data>()
    //...
Log.Info("Done");
// for production code, this belongs into a finally block
Session.FlushMode = FlushMode.Auto; 

如果它实际上是刷新问题，则需要在事务中的某些点上手动刷新.关闭自动冲洗功能时要小心.它可能会导致难看的副作用.它是非常特定于您的交易的，我不能说您如何正确地实施它.您也可以使用StatelessSession，但是对我而言，它从未起作用(它有一些限制).您还可以清除会话，这也要求您完全知道自己在做什么.

If it actually is a flushing problem, you need to flush manually on certain points in the transaction. Be careful when turning off auto flush. It may cause ugly side effects. It is very specific to your transaction and I can't say you how to implement it the right way. You could also use a StatelessSession, but for me it never worked (it has some limits). You may also clear the session, which also requires that you exactly know what you are doing.

如果没有冲洗问题，将很难跟踪.使用Profiler来查看是否确实花费时间在SQL Server查询中.它甚至可能是SQL Server上的缓存问题.在这种情况下，第一次执行查询需要花费几分钟，而第二次只需花费几秒钟.创建适当的索引可能会有所帮助.在这里，我不再猜测...

If it is no flushing problem, it gets difficult to track. Use Profiler to see if it actually takes the time in the SQL server query. It may even be a caching problem on SQL server. In this case it takes minutes the first time you execute the query, but only seconds the second time. Creating proper indexes may help. Here I stop guessing...

这篇关于NHibernate SQL查询慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！