LINQ to Nhibernate performance

Question

I'm using a combination of NHibernate and LINQ to try and achieve queries, but I'm worried the following query is pulling back every single user row from the DB, giving a large DB latency, large network traffic, large memory footprint, then filtering it out far too late.

public bool CheckEmailAddressIsAvailable
               (string emailAddress, IDataRepository repository)
{
     var users = from user in repository.GetItems<User>()
                 where user.EmailAddress.Equals(
                     emailAddress,
                     System.StringComparison.CurrentCultureIgnoreCase)
                 select user;

     return !(users.Any());
}

DataRepository is a simple wrapper around NHibernate Session What I want to know is twofold

1) How can I log all NHibernate SQL that is being created so I can keep an eye on what is being run and 2) How can I run the above query to mimic the below SQL

select count(*) from [User] where EmailAddress = @emailAddress

i dont use NHibernate but you can use SQL Profiler to look at the queries your app is running. I would also try using .Count != 0 instead of .Any() if you dont think any is implemented — undefined, Mar 16 '12 at 10:52
1) [howto enable logging of sql][1] [1]: http://stackoverflow.com/q/474659/671619 — Firo, Mar 16 '12 at 12:03
Also, the version of NHibernate you're using is important to note, because v2.1 with the contrib Nhibernate.Linq sucks :( — moribvndvs, Mar 16 '12 at 12:06

score 3 · Answer 1 · answered Mar 16 '12 at 10:56

3

NHibernate uses log4net for logging, so if you are already using it, one way would be to set the NHibernate.SQL logger level to DEBUG in your app.config file:

<logger name="NHibernate.SQL">
  <level value="DEBUG"/>
</logger>

Generally, as with any other application, you can simply use SQL Profiler and log queries for a while.

Regarding your LINQ code, I am pretty sure it creates SQL query similar to yours below, but it's hard to tell without seeing the GetItems code.

If this feels slow, you probably need an index on your EmailAddress column. The good thing about SQL Profiler is that you can analyze its logs in the Database Engine Tuning Advisor and get recommendations on how to improve it.

answered Mar 16 '12 at 10:56

vgru

49,838
16
120
201

Which level do you put the tag in your app.config? Ive tried it several places and it just doesnt seem to be able to load the configuration at all when I add it in – NZJames Mar 16 '12 at 14:54
@user1122909: first, you need to define this section by adding `
` inside the `` element. Then, you need to add the `` section where you will define log appenders (file, e-mail, event log), root logger level, and finally add the `` element. That's why I wrote *if you are already using it*, otherwise it may be easier to simply start SQL Profiler. You should check the [log4net manual](http://logging.apache.org/log4net/release/manual/configuration.html) for details. – vgru Mar 16 '12 at 15:58

score 0 · Answer 2 · answered Mar 16 '12 at 10:56

0

What does GetItems return? If it is returning anything except IQueryable(<T>), then you are just doing LINQ to Objects (in-memory). And that means loading the whole set.

You have to return an IQueryable to allow DB-side filtering.

Check out the Query<T>() extension method for ISession. That will give you an IQueryable

answered Mar 16 '12 at 10:56

Robert Giesecke

4,314
21
22

So if you return an IQueryable from GetItems and use that in a LINQ expression it will do DB side filtering? Perfect! Just looked at ISession though and theres no Query method. Is this something I need to code up myself? – NZJames Mar 16 '12 at 11:50
As I said, this is an extension method. Check this SO thread http://stackoverflow.com/questions/4666665/nhibernate-where-isession-queryt-is-located – Robert Giesecke Mar 16 '12 at 15:07

LINQ to Nhibernate performance

2 Answers2