3

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
Improve this question
3
  • 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
    Commented Mar 16, 2012 at 10:52
  • 1) [howto enable logging of sql][1] [1]: stackoverflow.com/q/474659/671619
    – Firo
    Commented Mar 16, 2012 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
    Commented Mar 16, 2012 at 12:06

2 Answers 2

Reset to default
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.

Improve this answer
2
  • Which level do you put the <logger> 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
    Commented Mar 16, 2012 at 14:54
  • @user1122909: first, you need to define this section by adding <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/> inside the <configSections> element. Then, you need to add the <log4net> section where you will define log appenders (file, e-mail, event log), root logger level, and finally add the <logger> 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 for details.
    – vgru
    Commented Mar 16, 2012 at 15:58
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

Improve this answer
2
  • So if you return an IQueryable<T> 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<T> method. Is this something I need to code up myself?
    – NZJames
    Commented Mar 16, 2012 at 11:50
  • As I said, this is an extension method. Check this SO thread stackoverflow.com/questions/4666665/…
    – Robert Giesecke
    Commented Mar 16, 2012 at 15:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.