ORM (esp. NHibernate) performance for complex queries

Question

Hi,

My company is in the process of rewriting an existing application from scratch. This application, among other tasks, performs complex SQL queries against order and invoice data to produce sales reports. The queries are built dynamically depending on which criteria are selected by the user, so they can be pretty complex if many criteria are selected. Currently, performance is decent, but not great.

Now, for the new version, we would like to use an ORM, probably NHibernate because it's apparently the only one that supports Oracle Lite (the application uses either Oracle or Oracle Lite, depending on whether it's running in connected or disconnected mode). But I'm worried about the performance of queries generated by NHibernate. I've worked with other ORMs before (Linq to SQL, Entity Framework), but the queries were pretty simple, so there was no performance issue.

So, before I take a decision about using an ORM or staying with plain SQL, I'd like to know how well these tools handle scenarios such as outer joins, subqueries, etc... Do you think an ORM (especially NHibernate) is suitable for use in the reporting scenario described above ? Should I worry about performance for complex queries ?

Any feedback would be greatly appreciated

Answer 1

NHibernate has a bit of a learning curve, but it is well worth learning as any time spent will pay off many times over. I would recommend NHibernate in Action book for learning as it is an excellent resource and covers everything your question asks and a whole lot more.

NHibernate performance can be tuned (see links below) and NHibernate has robust caching mechanism.

http://www.codeproject.com/KB/database/NHibernate%5FPerf.aspx https://www.hibernate.org/hib%5Fdocs/nhibernate/html/performance.html

Ultimately the performance will be determined by who writes the queries as it is in SQL.

Answer 2

If you are looking for ORM with comprehensive and efficient LINQ translator, I recommend you to try Entity Framework or DataObjects.Net. I'm not sure about Oracle Lite support. NHibernate's LINQ translator is not fully finished yet.

Anyway the best thing you can do now is to download leading ORM tools and test them on tricky queries with grouping, joining, subqueries etc.

Answer 3

See this chart. There is no DataObjects.Net for now, but its results in comparison to EF and NHibernate are shown here.

LINQ test code for EF is here; versions for other tools can be found in the same folder. All these .cs files are generated by a single T4 template, so tests are fully identical. Model used there is Northwind.

Few more links:

• LINQ test description
• FAQs.

Answer 4

Hi Thomas,

were you able to successfully used NHibernate with Oracle Lite,If yes can you tell me what do we need to mention in hibernate.cfg.xml , I mean which dialect and how do we connect to it. Thanks.

Dewang

---

EDIT by Thomas Levesque

I didn't know where to answer your question, so I just edited your post... hope you don't mind

Here's the app.config for my test project :

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate"/>
  </configSections>
  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="dialect">NHibernate.Dialect.OracleLiteDialect</property>
      <property name="connection.driver_class">NHibernate.Driver.OdbcDriver</property>
      <property name="connection.connection_string">dsn=TheDSN;uid=TheUserId;pwd=ThePassword</property>
      <property name="proxyfactory.factory_class">NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
      <property name="show_sql">true</property>
    </session-factory>
  </hibernate-configuration>
</configuration>

Note : to connect to the local Oracle Lite database :

• the DSN is usually built as <Oracle lite user name>_<Database name>
• the user id is SYSTEM
• the password is the actual password of the Oracle Lite user

For instance, if your OL user name is SCOTT, your password is TIGER, and the database name is FOO, the Oracle Lite connection string is : dsn=SCOTT_FOO;uid=SYSTEM;pwd=TIGER

Hope this helps