NHibernate query extremely slow compared to hard coded SQL query

Question

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)

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

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

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 })

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.

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

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

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

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

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.

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!

Answer 1

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

• 16.6. Projections for more details and nice example.

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

Answer 2

So, I came up with my own solution that's a bit of a mix between Radim's solution (using the AliasToBean transformer with a DTO, and Jake's solution involving selecting raw properties and converting each row to a list of object[] tuples.

My code is as follows:

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

I then implemented a new class called IngredientGraphTransformer which can convert that object[] array into a list of IngredientBinding objects, which is what I was ultimately doing with this list anyway. This is exactly how AliasToBeanTransformer is implemented, only it initializes a DTO based on a list of aliases.

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
      };
   }
}

Note, this is not as fast as doing a raw SQL query and looping through the results with an IDataReader, however it's much faster than joining in all the various models and building the full set of data.