相比于硬codeD SQL查询NHibernate的查询速度极慢 [英] NHibernate query extremely slow compared to hard coded SQL query

问题描述

我重新写一些我的旧的NHibernate code更数据库无关，并使用NHibernate的查询，而不是硬codeD SELECT 陈述或者数据库视图。我只能和一个正在被重新写入后慢得令人难以置信。 SQL查询是这样：

I'm re-writing some of my old NHibernate code to be more database agnostic and use NHibernate queries rather than hard coded SELECT statements or database views. I'm stuck with one that's incredibly slow after being re-written. The SQL query is as such:

 SELECT
    r.recipeingredientid AS id,
    r.ingredientid,
    r.recipeid,
    r.qty,
    r.unit,
    i.conversiontype,
    i.unitweight,
    f.unittype,
    f.formamount,
    f.formunit
   FROM recipeingredients r
   INNER JOIN shoppingingredients i USING (ingredientid)
   LEFT JOIN ingredientforms f USING (ingredientformid)

所以，这是一个pretty的一对夫妇基本的查询连接，它选择了一些列的每个表。该查询恰好返回约40万行，有大约5秒的执行时间。我的第一次尝试EX preSS它作为一个NHibernate查询是这样：

So, it's a pretty basic query with a couple JOINs that selects a few columns from each table. This query happens to return about 400,000 rows and has roughly a 5 second execution time. My first attempt to express it as an NHibernate query was as such:

var timer = new System.Diagnostics.Stopwatch();
timer.Start();
var recIngs = session.QueryOver<Models.RecipeIngredients>()
   .Fetch(prop => prop.Ingredient).Eager()
   .Fetch(prop => prop.IngredientForm).Eager()
   .List();
timer.Stop();

这code ++工程，并生成所需的SQL，但它需要120,264ms运行。在此之后，通过 recIngs 我环路和填充名单，其中，T＆GT; 收集，这需要不到一秒钟。因此，NHibernate的东西是做的的非常的慢！我有一种感觉这简直就是我的构建模型类的实例的每一行的开销。但是，在我的情况，我只使用每个表中几个属性，所以也许我可以优化这一点。

This code works and generates the desired SQL, however it takes 120,264ms to run. After that, I loop through recIngs and populate a List<T> collection, which takes under a second. So, something NHibernate is doing is extremely slow! I have a feeling this is simply the overhead of constructing instances of my model classes for each row. However, in my case, I'm only using a couple properties from each table, so maybe I can optimize this.

我想的第一件事情是这样的：

The first thing I tried was this:

IngredientForms joinForm = null;
Ingredients joinIng = null;
var recIngs = session.QueryOver<Models.RecipeIngredients>()
   .JoinAlias(r => r.IngredientForm, () => joinForm)
   .JoinAlias(r => r.Ingredient, () => joinIng)
   .Select(r => joinForm.FormDisplayName)
   .List<String>();

在这里，我只是抓住从我的JOIN起来一个表的单个值。在SQL code是一次正确的，这一次，它的只有的抓住了 FormDisplayName 列选择子句。此调用需要2498ms运行。我想我们要发生什么!!

Here, I just grab a single value from one of my JOIN'ed tables. The SQL code is once again correct and this time it only grabs the FormDisplayName column in the select clause. This call takes 2498ms to run. I think we're on to something!!

不过，我当然需要返回几个不同的列，不只是一个。这就是事情变得棘手。我的第一次尝试是一个匿名类型：

However, I of course need to return several different columns, not just one. Here's where things get tricky. My first attempt is an anonymous type:

.Select(r => new { DisplayName = joinForm.FormDisplayName, IngName = joinIng.DisplayName })

在理想情况下，这应该返回匿名类型同时具有显示名称和 IngName 属性的集合。然而，这导致异常的NHibernate的：

Ideally, this should return a collection of anonymous types with both a DisplayName and an IngName property. However, this causes an exception in NHibernate:

对象引用未设置到对象的实例。

Object reference not set to an instance of an object.

此外，的.List（）正在试图返回 RecipeIngredients 的列表，而不是匿名类型。我也试过的.List＆LT;对象＆gt;（）无济于事。嗯。好吧，也许我可以创建一个新的类型，并返回这些集合：

Plus, .List() is trying to return a list of RecipeIngredients, not anonymous types. I also tried .List<Object>() to no avail. Hmm. Well, perhaps I can create a new type and return a collection of those:

.Select(r => new TestType(r))

在 TestType 结构将取 RecipeIngredients 对象做什么。但是，当我这样做，NHibernate的抛出以下异常：

The TestType construction would take a RecipeIngredients object and do whatever. However, when I do this, NHibernate throws the following exception:

类型的未处理的异常'NHibernate.MappingException发生   在NHibernate.dll

An unhandled exception of type 'NHibernate.MappingException' occurred in NHibernate.dll

其它信息：没有持留为：KitchenPC.Modeler.TestType

Additional information: No persister for: KitchenPC.Modeler.TestType

我想NHibernate的要生成一个模型匹配的模式 RecipeIngredients 。

I guess NHibernate wants to generate a model matching the schema of RecipeIngredients.

我怎么做我想要做什么？似乎。选择（）仅可用于选择一列的列表。有没有办法用它来选择多列？

How can I do what I'm trying to do? It seems that .Select() can only be used for selecting a list of a single column. Is there a way to use it to select multiple columns?

也许一种方式是创建与我的确切模式的典范，但是我认为这将最终被就如同当初的尝试一样慢。

Perhaps one way would be to create a model with my exact schema, however I think that would end up being just as slow as the original attempt.

有没有什么办法可以从服务器返回这么多的数据，而无需大量的开销，没有硬编码的SQL字符串到程序，或根据在数据库中的查看 ？我想保持我的code完全数据库无关。谢谢！

Is there any way to return this much data from the server without the massive overhead, without hard coding a SQL string into the program or depending on a VIEW in the database? I'd like to keep my code completely database agnostic. Thanks!

推荐答案

在 QueryOver 语法转换所选列到的人工的对象（DTO）的是一个有点不同。在这里看到：

The QueryOver syntax for conversion of selected columns into artificial object (DTO) is a bit different. See here:

• 16.6。预测的更多细节，并很好的例子。

• 16.6. Projections for more details and nice example.

的草稿可能是这样的，首先DTO

A draft of it could be like this, first the DTO

public class TestTypeDTO // the DTO 
{
    public string PropertyStr1 { get; set; }
    ...
    public int    PropertyNum1 { get; set; }
    ...
}

这是使用的例子

And this is an example of the usage

// DTO marker
TestTypeDTO dto = null;

// the query you need
var recIngs = session.QueryOver<Models.RecipeIngredients>()
   .JoinAlias(r => r.IngredientForm, () => joinForm)
   .JoinAlias(r => r.Ingredient, () => joinIng)

    // place for projections
   .SelectList(list => list
     // this set is an example of string and int
     .Select(x => joinForm.FormDisplayName)
         .WithAlias(() => dto.PropertyStr1)  // this WithAlias is essential
     .Select(x => joinIng.Weight)            // it will help the below transformer
         .WithAlias(() => dto.PropertyNum1)) // with conversion
     ...
   .TransformUsing(Transformers.AliasToBean<TestTypeDTO>())
   .List<TestTypeDTO>();

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