LINQ to Nhibernate performance

I'm using a mix of NHibernate and LINQ to try and find the queries, but I'm worried the following query is returning every single user row from the DB, providing high DB latency, high network traffic, high memory then filter it out 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 an NHibernate session What I want to know is twofold.

1) How can I log all the generated NHibernate SQL so that I can keep track of what is running and 2) How can I run the above query to mimic the SQL below

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

      

+3


source to share


2 answers


NHibernate uses log4net for logging, so if you are already using it, one way would be to set the log level NHibernate.SQL

to DEBUG

in your file app.config

:

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

      

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



As far as your LINQ code goes, I'm sure it creates a SQL query similar to yours below, but it's hard to tell without seeing the code GetItems

.

If that seems slow, you probably want an index on the column EmailAddress

. The good thing is that in SQL Profiler you can analyze your logs in the Engine Tuning Advisor and get recommendations for improving it.

+3


source


What does GetItems return? If it returns anything other than IQueryable (<T>), then you're just doing LINQ on the objects (in memory). This means downloading the entire set.

You must return IQueryable to enable filtering on the DB side.



Notice the Query <T () extension method for ISession. This will give you an IQueryable

0


source







All Articles