I'm using:
- NHibernate v1.2
- MySql 5.0
- MySql .net connector.
- .net framework 2.0
I have a token table (InnoDB) with two columns: id (int, pk), name (varchar, unique index).
I have very bad performance on the following HQL query:
Code:
uint? result = db.CreateQuery("select t.Id from Token t where t.Name = ?")
.SetParameter(0, "test")
.UniqueResult<uint?>();
When I perform it through a MySqlCommand then the performance is much much better:
Code:
string QUERY = "SELECT Id FROM Token WHERE Name = ?name";
IDbCommand cmd = db.Connection.CreateCommand();
cmd.CommandText = QUERY;
IDbDataParameter p = cmd.CreateParameter();
p.ParameterName = "?name";
p.Value = "test";
p.DbType = DbType.String;
cmd.Parameters.Add(p);
uint? result = (uint?)cmd.ExecuteScalar();
When performing these queries in a loop then CPU usage when using the HQL query is 100% and memory usage is excessive too. This isn't a problem with the native approach. Well.. the name isn't fixed then but random.
The documentation states that native queries are sometimes better for performance reasons but I didn't suspect that this type of HQL query would have bad performance.
Am I doing something wrong here? How can I detect what NHibernate is doing? Is it trying to do something 'smart' with the cache? I read the documentation about caching but didn't find good documented samples about this.