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

Wait... you are putting nearly 7k items in a dropdown list? Am I understanding that correctly?

If so, would it be possible to use dependent dropdown lists with ajax or some similar design?

If this is on the web, you are likely looking at a relatively large page that needs to be transmitted down to a client computer, so optimizing the NH query may be a premature optimization...

Answer 2

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.

Answer 3

7k entries on a dropdown list is bad for the user experience. Since you are already ordering by Description i assume the your users already know (at least partially) what they want to select. So giving a full list is actually hindering the user.

Since you are asking what an autocompleter is

imagine an input field where the user types a number of characters. When the user types what he wants, then the query will fire. This string parameter will be used to further limit down the size of the result set.

so your query implementation is something like this pseudocode:

//passedParameter => "%foo%"
var temp = from x in ActiveRecordLinq.AsQueryable<Circuits>()
              where x.User_Created == false
                and x.Description like passedParameter
              orderby x.Description
              select x;

The actual implementation on both the query as well as if you decide to implement '%foo%' or 'foo%' etc it's your decision.

The user experience will be reduced to writing 'foo' in the input field and the results will only fetch the relevant Circuits on which the user will select what he wants. If the result set is still too large the user can add 'b' to the already typed 'foo' making a 'foob' where again the query fires again returning an even more limited result set.

When you type at google and it gives you suggestions on the fly its an autocompleter implementation