questions about ORM mappers like nhibernate etc.

Question

Hi,

Few questions on ORM mappers like nhibernate (for .net/c# environment).

1. When queries are run against a sqlserver database, does it use parameter sizes internally?

   paramaters.Add("@column1", SqlDataType.Int, 4)

2. Do they all use reflection at runtime? i.e. hand coding is always a tad faster?

3. does it support temp tables, table variables?

Answer 1

nhiberate uses an optimised form of reflection that creates proxy objects on startup that perform better than plain reflection, as it only incurs a one time cost. You have the option of disabling this feature as well which has nhibernate behave in a more typical manner, with the permanent use of reflection.

This feature is set with the following key:

<add key="hibernate.use_reflection_optimizer" value="true" />

Nhibernate can be used with variable table names. See this SO thread for a good solution.

Answer 2

1. NHibernate and SubSonic, LinqToSql, EF and I think most of the other ones use parameterized sql.

2. Most ORM's use some sort of reflection, there are some that generate all the code and SQL Query for you at design time so they don't have to use reflection code it might work a bit faster but it makes you domain a real mess and you have to use their application to regenerate all your code.

3. I am almost sure that they all don't support that but most have a way that you can use SP's and Views to do this.

You can check this out NHibernate Screencast Series http://www.summerofnhibernate.com/

Answer 3

ORM World is powerfull and full featured one, I think today obstacles are peoples theirself, this to say that using ORM's needs to take changes in mind, in the way of thinkng applications, architectures and patterns.

To answer you questions:

1. Query execution depends on more factors, it's possible to fully customize the engine/environment to take advantages of various features like, deffered execution, future queries (queries executed in a future moment), multiple queries, and last but not least, session management involves in this area:

   1. Deferred execution is based on concepts like lazy-loading and lazy execution, this means that queries are executed against the database just qhen you perform some actions, like accessing to methods of the NHibernate Session like ToList(), another example of deferred execution is with using of LinqToNhibernate, where just when you access to certain objects, queries are executed

   2. Future queries are as I said beforre queries executed in a future moment, Ayende speaks well about that

   3. Multiple queries are queries that can be "packed" together and executed in one time avoiding multiple roundtrips to the DB, and this could be a very cool feature

   4. Session Management, this is another chapter to mention... but take in mind that if you manage well your session, or better, let NHibernate engine to manage well the session, sometime it's not needed to go to the DN to obtain data

in all the cases, tools like NHibernate generates queries for you, and parametrized queries are managed well with parameters even depending on the underlying DB engine and conseguently DB Dialect you choose!

1. It's clear that frameworks like NHibernate, most of the time, use reflection at runtime, but it's needed to mention the multiple Reflection optimization are used, see for example Dynamic Proxies... It's clear that somethime, or maybe all the time direct code could be faster, but just in the unit, in the big picture this could involve in more mistakes and bottlenecks

2. Talking about NHibernate, or better saying, It's usefull to understand what you mean when talk about Temp Tables and temp data.. In terms as are, NHibernate, as I know, doesn't support natively Temp Tables, in the sense of Runtime tables, but this could be done because NHibernate permit to create object mapping at runtime, so a mechanism of Temp data could be implemented using that api

I hope I provided an usefull answer! ...and oops, sorry for my bad english!