How to optimize this Nhibernate Query (835ms)

Question

I have this query

var temp = from x in ActiveRecordLinq.AsQueryable<Circuits>()
                       where x.User_Created == false
                       orderby x.Description
                       select x;

From NHibernate Profiler
Query duration
-Database only: 7ms
-Total: 835ms

The query generated:

SELECT   this_.Circuit_ID     as Circuit1_35_0_,
     this_.[Description]  as column2_35_0_,
         this_.[User_Created] as column3_35_0_
FROM     dbo.Circuit this_
WHERE    this_.[User_Created] = 0 /* @p0 */
ORDER BY this_.[Description] asc

It seems like a pretty straightforward query. It returns 6821 rows. All I'm using this for is to populate a dropdownlist.

Thanks in advance

Answer 1

Ok, if you insist on the 7k (I REALLY believe you should stop to rethink your design... but...), you could try doing an HQL query to just select the fields you need from the object, instead of querying for the objects themselves.

With the query you have written, nHibernate is loading the data from the database which occurs pretty quickly as you have noted. But THEN based on the Linq query you have written it is initializing, populating and returning 7k Circuit objects. which is probably taking it a while...

And since you aren't actually using the "object" in any meaningful way in this case (just a text and value pair for the dropdown) you really don't need nHibernate to build the objects.

Try changing your code to just return the text/value pair with HQL or LinqToNHibernate.

the HQL would look something like this:

select c.description, c.id from Circuit c
where c.ordercreated = false
orderby c.description

I know you can do this with LinqToNhibernate as well, I just don't have any quick examples at hand.