CODEWITHSUNDEEP
c#linqnhibernate
NZJames
NZJames

Reputation: 5055

LINQ to Nhibernate performance

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

Upvotes: 3

Views: 884

Answers (2)

vgru
vgru

Reputation: 51224

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.

Upvotes: 3

Robert Giesecke
Robert Giesecke

Reputation: 4314

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

Upvotes: 0

Related Questions