Which one have better performance?

Question

I write same query with two approach by using NHibernate:

1- by using HQL like 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- by using ICriteria and SetProjections like 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 that which one has better performance? why?

Answer 1

I think the best way to get a metric of which is better would be as was stated here. Go download nhProf and profile it.

http://nhprof.com/

If you want more detail take the sql that is generated and THEN run it through SQL Server profiler to get an even better idea of what it is doing.

But honestly, if you have any quantity of data in your database doing a LIKE query will give you horrible HORRIBLE results.

I would strongly recommend that you set up Full Text indexing in SQL Server and then use this:

http://nhforge.org/blogs/nhibernate/archive/2009/03/13/registering-freetext-or-contains-functions-into-a-nhibernate-dialect.aspx

to register the freetext and contains functions in nHibernate.

another great example to integrate with ICriteria queries is here:

http://xlib.wordpress.com/2009/12/04/integrating-freetext-search-in-nhibernate-detached-criteria/

Alternatively you can use Lucene.NET to do the full text indexing as well.

Answer 2

There is no significant intrinsic performance difference between HQL and Criteria. They're just different APIs to express a query that in the end will be translated to SQL, that's it.

The criteria (no pun intended) to pick one API over the other depends on the usage context. For example, in your particular case, I'd go with Criteria. Building 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 the propertyValue to be a IQuery parameter...