NHibernate query is extremely slow compared to hard-coded SQL query

Question

I am rewriting part of my old NHibernate code to be a database agnostic and use NHibernate queries rather than hard-coded SELECTdatabase statements or representations. I am stuck with what is incredibly slow after being transcribed. The SQL query is as follows:

 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)

So, this is a fairly simple query with a JOINs password that selects multiple columns from each table. This query returns about 400,000 rows and takes about 5 seconds to complete. My first attempt to express this as an NHibernate request was this:

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();

SQL, 120,264ms. recIngs List<T>, . , - NHibernate ! , . , , , , , .

, , :

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 , FormDisplayName select. 2498 . , -!

, , , . , . - :

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

DisplayName IngName. NHibernate:

​​ .

, .List() RecipeIngredients, . .List<Object>() . . , :

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

TestType RecipeIngredients . , , NHibernate :

"NHibernate.MappingException" NHibernate.dll

: : KitchenPC.Modeler.TestType

, NHibernate , RecipeIngredients.

, ? , .Select() . ?

, , , , .

, SQL VIEW ? , . !

Answer 1

QueryOver (DTO) . . :

• 16.6. .

, DTO

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

// 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>();

Answer 2

, , Radim ( AliasToBean DTO Jake, object[] .

:

var recIngs = session.QueryOver<Models.RecipeIngredients>()
   .JoinAlias(r => r.IngredientForm, () => joinForm)
   .JoinAlias(r => r.Ingredient, () => joinIng)

   .Select(
      p => joinIng.IngredientId,
      p => p.Recipe.RecipeId,
      p => p.Qty,
      p => p.Unit,

      p => joinIng.ConversionType,
      p => joinIng.UnitWeight,

      p => joinForm.UnitType,
      p => joinForm.FormAmount,
      p => joinForm.FormUnit)
   .TransformUsing(IngredientGraphTransformer.Create())
   .List<IngredientBinding>();

IngredientGraphTransformer, object[] IngredientBinding, . AliasToBeanTransformer, DTO .

public class IngredientGraphTransformer : IResultTransformer
{
   public static IngredientGraphTransformer Create()
   {
      return new IngredientGraphTransformer();
   }

   IngredientGraphTransformer()
   {
   }

   public IList TransformList(IList collection)
   {
      return collection;
   }

   public object TransformTuple(object[] tuple, string[] aliases)
   {
      Guid ingId = (Guid)tuple[0];
      Guid recipeId = (Guid)tuple[1];
      Single? qty = (Single?)tuple[2];
      Units usageUnit = (Units)tuple[3];
      UnitType convType = (UnitType)tuple[4];
      Int32 unitWeight = (int)tuple[5];
      Units rawUnit = Unit.GetDefaultUnitType(convType);

      // Do a bunch of logic based on the data above

      return new IngredientBinding
      {
         RecipeId = recipeId,
         IngredientId = ingId,
         Qty = qty,
         Unit = rawUnit
      };
   }
}

, , SQL- IDataReader, , .

Answer 3

IngredientForms joinForm = null;
Ingredients joinIng = null;
var recIngs = session.QueryOver<Models.RecipeIngredients>()
   .JoinAlias(r => r.IngredientForm, () => joinForm)
   .JoinAlias(r => r.Ingredient, () => joinIng)
   .Select(r => r.column1, r => r.column2})
   .List<object[]>();

?