title: NHibernate query extremely slow compared to hard coded SQL query
body: <p>I'm re-writing some of my old NHibernate code to be more database agnostic and use NHibernate queries rather than hard coded <code>SELECT</code> statements or database views.  I'm stuck with one that's incredibly slow after being re-written.  The SQL query is as such:</p>
  
  <pre><code> 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)
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>var timer = new System.Diagnostics.Stopwatch();
  timer.Start();
  var recIngs = session.QueryOver&lt;Models.RecipeIngredients&gt;()
     .Fetch(prop =&gt; prop.Ingredient).Eager()
     .Fetch(prop =&gt; prop.IngredientForm).Eager()
     .List();
  timer.Stop();
  </code></pre>
  
  <p>This code works and generates the desired SQL, however it takes 120,264ms to run.  After that, I loop through <code>recIngs</code> and populate a <code>List&lt;T&gt;</code> collection, which takes under a second.  So, something NHibernate is doing is <em>extremely</em> 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.</p>
  
  <p>The first thing I tried was this:</p>
  
  <pre><code>IngredientForms joinForm = null;
  Ingredients joinIng = null;
  var recIngs = session.QueryOver&lt;Models.RecipeIngredients&gt;()
     .JoinAlias(r =&gt; r.IngredientForm, () =&gt; joinForm)
     .JoinAlias(r =&gt; r.Ingredient, () =&gt; joinIng)
     .Select(r =&gt; joinForm.FormDisplayName)
     .List&lt;String&gt;();
  </code></pre>
  
  <p>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 <em>only</em> grabs the <code>FormDisplayName</code> column in the select clause.  This call takes 2498ms to run.  I think we're on to something!!</p>
  
  <p>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:</p>
  
  <pre><code>.Select(r =&gt; new { DisplayName = joinForm.FormDisplayName, IngName = joinIng.DisplayName })
  </code></pre>
  
  <p>Ideally, this should return a collection of anonymous types with both a <code>DisplayName</code> and an <code>IngName</code> property.  However, this causes an exception in NHibernate:</p>
  
  <blockquote>
    <p>Object reference not set to an instance of an object.</p>
  </blockquote>
  
  <p>Plus, <code>.List()</code> is trying to return a list of <code>RecipeIngredients</code>, not anonymous types.  I also tried <code>.List&lt;Object&gt;()</code> to no avail.  Hmm.  Well, perhaps I can create a new type and return a collection of those:</p>
  
  <pre><code>.Select(r =&gt; new TestType(r))
  </code></pre>
  
  <p>The <code>TestType</code> construction would take a <code>RecipeIngredients</code> object and do whatever.  However, when I do this, NHibernate throws the following exception:</p>
  
  <blockquote>
    <p>An unhandled exception of type 'NHibernate.MappingException' occurred
    in NHibernate.dll</p>
    
    <p>Additional information: No persister for: KitchenPC.Modeler.TestType</p>
  </blockquote>
  
  <p>I guess NHibernate wants to generate a model matching the schema of <code>RecipeIngredients</code>.</p>
  
  <p>How can I do what I'm trying to do?  It seems that <code>.Select()</code> can only be used for selecting a list of a single column.  Is there a way to use it to select multiple columns?</p>
  
  <p>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.</p>
  
  <p>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 <code>VIEW</code> in the database?  I'd like to keep my code completely database agnostic.  Thanks!</p>