WayneS (4/1/2011)
I'd like to start a discussion for how ORM tools (Entity-Framework, nHibernate, etc.) should perform SQL actions.I'm specifically interested in the use of stored procedures.
1. Should ORM tools be allowed to create/run their own generated SQL?
2. Should stored procedure usage be enforced?
3. Should ORM tools be allowed to create/run only simple statements against tables, and use stored procedures for others?
I'd prefer this to be fact-based, not just "because I'm the DBA, and I say so" - let's get to a state where an informed decision can be made.
(Yep, work is doing stuff with an ORM tool, and this is one of the decisions that needs to be made.)
Thanks!
The answer to each of the three questions: it depends. With a tendency to #2 only.
My personal reason:
I've been called to have a look at an extremely poor performing app that just had been developed (I ranted about it a while ago). A profiler trace showed a massive amount of single select/insert/update calls. Plain RBAR. And, even worse, based on the way NHibernate was configured, almost every single statement resulted in a special execution plan.
The minimum level you should insist in: never, ever let NHibernate create tables. The "R" in ORM definitely does not stand for any affinity to a "Relational" data model.
You need to define the layer where the business logic will reside. Either do it all at the ORM layer or at the DB. Obviously, I prefer the latter. That's mainly because I could demonstrate that the way NH communicated with the DB lead to a massive performance decrease. I demonstrated the difference by rewriting a "process" that returned some aggregated data and took 6min before using NH against the source tables and < 10sec when calling a parameterized iTVF.
But that most probably had more to do with the way NH has been used than with the NH capabilites in general.
I recommend to google for "scarydba nhibernate". Obviously, Grant provided several excellent posts well worth reading including additional links (as well as covering the reason for the effect described above).