ORM (esp. NHibernate) performance for complex queries

Question

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

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.