Who has the best performance?
I am writing the same query with 2 approaches using NHibernate:
1- using HQL
as below
public long RetrieveHQLCount<T>(string propertyName, object propertyValue)
{
using (ISession session = m_SessionFactory.OpenSession())
{
long r = Convert.ToInt64(session.CreateQuery("select count(o) from " + typeof(T).Name + " as o" + " where o." + propertyName + " like '" + propertyValue + "%'").UniqueResult());
return r;
}
}
2- using ICriteria
and SetProjections
as below
public long RetrieveCount<T>(string propertyName, object propertyValue)
{
using (ISession session = m_SessionFactory.OpenSession())
{
// Create a criteria object with the specified criteria
ICriteria criteria = session.CreateCriteria(typeof(T));
criteria.Add(Expression.InsensitiveLike(propertyName, propertyValue))
.SetProjection(Projections.Count(propertyName));
long count = Convert.ToInt64(criteria.UniqueResult());
// Set return value
return count;
}
}
Now my question is which one has the best performance? why?
a source to share
I think the best way to get the metric is better as stated here. Download nhProf and profile.
If you want more details, create the generated sql and THEN run it through the SQL Server Profiler to get an even better idea of what it does.
But honestly, if you have any amount of data in your database, doing a LIKE query will give you horrible HORRIBLE results.
I highly recommend that you set up Full Text Indexing in SQL Server and then use this:
for registering freetext and contains functions in nHibernate.
another great example for integrating with ICriteria queries:
http://xlib.wordpress.com/2009/12/04/integrating-freetext-search-in-nhibernate-detached-criteria/
Alternatively, you can use Lucene.NET for full text indexing.
a source to share
There is no significant internal performance difference between HQL and benchmarks. They are just different APIs to express a query that will eventually translate to the SQL that it is.
The criteria (no pun intended) for choosing one API over another depends on the context of use. For example, in your particular case, I would go with criteria. Constructing a query from string concatenation is quite error prone and you have to be very careful not to be vulnerable to injection attacks . At least set propertyValue
as parameter IQuery
...
a source to share