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!
1. Yes, but, you have to validate that they are doing the ORM code correctly. It is possible to screw this up, horribly.
2. No, but, you need to have tight communication with the Dev team so that you can test things as they are released and if you find certain areas or certain queries where the ORM tool just can't generate a sufficiently performant query, the Dev team will be able to (and is required to) support stored procedures. Most ORM tools can work with procs just fine.
3. Yes. As a matter of fact, this mixed approach is best. Use the strengths of the tools at your disposal.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning