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
source to share
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.
source to share
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
source to share