11

When looking at our queries from NHibernate it's clear that the column alias is not consistent, that a problem for performance when the sql server execution plan seeing the same query as different query's because of the inconsistent column alias.

An example could be something like this:

SELECT this_.Id as Id44_0_ FROM dbo.[Foos] this_

SELECT this_.Id as Id43_0_ FROM dbo.[Foos] this_

Is there any way we can make consistent column alias?

We are using Fluent NHibernate with Auto mapping

Lehto
  • 257
  • 1
  • 14
  • 3
    Where is the code that shows this happening? – Diego Mijelshon Feb 20 '12 at 15:33
  • NHibernate used these kind of alias with every query you make, so what code will you like to see? – Lehto Feb 21 '12 at 14:34
  • The two queries that result in those SQL statements. – Diego Mijelshon Feb 22 '12 at 00:22
  • @Lehto - What Diego is saying is that you must have two totally different pieces of code that generates your two `select` statements and he would like you to post them... Is this the case? Remember you may need to help us to help you. – Rippo Feb 22 '12 at 05:23
  • No, that's the problem, the same piece of code makes two different sql queries. The problem is that NHibernate gives all your tables a "uniqueInteger", see https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Mapping/Table.cs and when your change some of your model the "uniqueInteger" will change, because NHibernate reads the tables in a different order. – Lehto Feb 22 '12 at 08:15
  • If you change your model then wouldn't you expect your query plans to change as well? A change in model means a rebuild and deploy – Rippo Feb 22 '12 at 14:30
  • Yes exactly, that's is why I need a way to make the alias consistent. – Lehto Feb 28 '12 at 09:58
  • Please post the Fluent mapping for the class/table in question, maybe I can figure it out. – Tamas Ionut Mar 05 '12 at 20:30
  • Without sample of your code is difficult to theorise i would check ur mapping and code where hou fetch the data, better post them for us to scratch our heads – cpoDesign Mar 11 '12 at 20:22
  • If you've removed a column from your mapping, NHibernate and Fluent NHibernate won't know to generate code for that column. You should post the code that generated your first `SELECT` and the modification that generated your second `SELECT`. – Joseph Yaduvanshi Mar 23 '12 at 17:23

2 Answers2

2

Optionally by setting projection you can get a custom name as an alias in the query as follows

Projections.Property("candidate.Name"), "CandidateName");

How to use NHibernate Projections to retrieve a collection

Community
  • 1
  • 1
Anand
  • 717
  • 6
  • 20
0

I never seen same NH query to generate different SQL while your application is running. I think you don't need to worry about performance because of this. (Except of the case when you need to start and stop you app frequently.)

Any changes you need to make SQL queries consistent all the time (even after you data model changes) are painful, not absolutely good solutions and I think not acceptable for you.

Andriy Buday
  • 1,959
  • 1
  • 17
  • 40