Our company recently hired an outside consulting group to do an assessment of our project. Part of this included a database performance and tuning analysis by a very prominent member of the MS SQL Server community. His report had a lot of great stuff in it. But it also included some critical points surrounding the usage of NHibernate.
First of all, our project is a high volume OLTP system which includes a number of legacy models and newer domain models. The more recently rebuilt components use NHibernate to give us sufficient abstraction from the database models, but still give us the power to properly structure queries. I decided to enumerate many of the points that were brought up in the report and from discussions with the consulting group to see what the community thought. Essentially I’m looking for more ammunition. 1. NHibernate generates ad-hoc queries which result in unnecessary memory pressure on the procedure cache by incorrectly setting the parameter sizes. Instead of setting the parameter size to the size of the mapped column, it’s set to the size of the value. This is a pretty valid argument, and I still consider it a bug in NHibernate. But you can work around this by setting the prepare_sql configuration option. I don’t like this, and DBAs don’t like this. So I’ve actually sub-classed the Sql client driver to make sure it always sets the proper parameter size when generating the command. So I believe this argument is not worth any weight. 2. NHibernate generates Sql statements and wraps them in “EXEC ExecuteSQL N’Select…” statements. This apparently masks similar queries from most analysis tools, making harder for the DBA to do performance analysis and tuning. This is valid, but I think it’s fairly nit-picky. It doesn’t directly affect performance, and I think it’s just a DBA not likely the format of the query NHibernate generates. 3. “The biggest problem with NHibernate is the workarounds to achieve scaling and performance goals often require awkward and unstable SQL constructions.” This is probably the most valid argument. There are simply some SQL statements that NHibernate cannot generate from criteria, HQL, or LINQ. As such you end up using workarounds like correlated subqueries, which aren’t always stable. The programmer ends up trying to tell SQL Server how to retrieve a result set, instead of telling SQL Server what the result set should look like and have it do the optimizations. However, in the cases where complex queries are required, there is nothing stopping you from dropping down to the stored procedure level and then mapping the result set back to an entity. I don’t think they realize how trivial this is to do. 4. The final argument was more development and ALM centric. This group is a big fan of template driven code generation for data access layers. I think this works when you’re focusing on building database schema first, or working with a legacy database model. But when doing model or domain driven design, where you might start with classes and work out from there, I think NHibernate is much better fit. I also just don’t like code generation. But I can see the appeal from a perspective of refactoring. If you need to change your persistence strategy, just push and button and regenerate the code. Whether this is a good thing or not… I’m not sure. Finally I’d like to quote the report: "Note that NHibernate is a good system for small-scale database applications due to its ability to rapidly abstract the data for the application. This was an excellent choice for the company in its early years but the application has clearly grown beyond what NHibernate can reasonably support." I would appreciate everyone’s thoughts, comments, or suggestions. Thanks, -- Joseph Daigle -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
