Hi Joseph,
> 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.
care to share the name of this community member? :)
> 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 indeed something that has been reported about entity
framework as well, and is the result of not having the target field
precision/scale/length in the mapping information. A trade off, but which
can be solved, as Diego has described in his post, although this will
require more code. I don't know if NH3 will have an (optional) way to define
these attributes in the mapping files.
> 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.
In fact, it DOES affect performance, but in a good way: SqlCommand
wraps every CommandType Text (ad-hoc queries) in EXEC sp_executeSQL...
calls, because that way the compiled execution plan can be cached and
re-used. Not having that will result in non-cached plans which makes things
slower. So having that wrapping is actually better :)
Other DBs don't wrap it in a proc call, though this is how SQL
server works. I'm surprised your high profile sql server community member
didn't know this.
> 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.
care to share one as an example? I.o.w. I think it's more of an edge
case when you run into these limitations, with any o/r mapper btw. Often
these queries are using temp tables, cursors etc.
> As such you end up using workarounds like correlated subqueries, which
aren't
> always stable.
what does 'not stable' mean, exactly? Correlated subqueries are
either correct, or not, but 'unstable' is IMHO a term not used in
combination of correlated subqueries.
> 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.
An example would be great.
> 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.
'starting with classes' is not where you start, you start with
entity definitions, and create classes from them, or create tables from them
and create the opposite part (table in case of class and class in case of
table) and map them. Trying to cram class hierarchies into a database only
works if the relational model indeed makes as much sense as the class model,
which it usually does if both share the same theoretical base: the abstract
entity model.
code generation is simply automated typing. You might like your
logitech stock, but in general, if a machine can do the typing, why do it
yourself?
> 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.
earlier in your post you mentioned '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'. This
is not matching what you say above with respect to changing something in a
model and re-generate code, as changing the model is defining how you want
the model to BE, how it logically relates to each other. You then want code
to WORK with it in the functionality you have to provide to the application
user, and apply additional behavior to the model to keep things neatly OO.
> 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 think that's a load of crap, as this is more a point of how your
logic which consumes the nhibernate model and the database is written. Mind
you: a slow, sloppy algorithm in Business logic code can ruin everything,
and it will look like your data-layer is slow because it executes all those
queries.
Look where the large datasets are consumed, and look into whether
you can chop things up in smaller packages. For example, if you have code
which pulls 1000 rows into memory for a batch operation, perhaps you should
chop that up in 10 batches of 100 rows, or formulate an update operation in
an update query without fetching the data. That will free up memory, won't
fetch a lot of rows in 1 go (so connections, read locks etc. are taking less
time/resources at any given moment).
FB
>
> 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.
--
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.