April 1, 2011 at 1:36 pm
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!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 1, 2011 at 2:23 pm
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).
April 1, 2011 at 2:49 pm
LutzM (4/1/2011)
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).
I didn't remember Grant doing these - they are indeed excellent reading. For anyone coming along, that link is here[/url].
Now, to find out some information on EF and SQL.
Looking forward to whatever else pops up here!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 3, 2011 at 7:55 pm
My opinion is that Grant and the fellow that Grant cited are pretty much spot on and it all boils down to the following; Every tool has a proper purpose for thoughtful Developers. If the tool is abused or misunderstood, especially tools like ORMs which write code, very bad things can happen.
In the right hands, ORM's are very powerful, very efficient, Rapid Application Development tools IMHO. I may be the wrong person to ask about ORMs, though, because I've only met one Developer who had the "right hands". All of the rest either didn't understand the tool or flat didn't give a rat's patooti as to what it could do to a database and database server. One fellow wrote what he called an "aggressive GET" using nHibernate. It pinned two of the four CPU's on a otherwise quiet Development Server to the wall for 7 minutes while the program loaded "in the background". The HQL that was produced had ~90 joins (some of them self-joins) with nary a SARGable predicate in sight and was capped by a DISTINCT to suppress the effect of the many-to-many joins it created. When I identified the problem to the "developer", his response was "the server is running too slow". When I suggested rewriting it as a "Divide'n'Conquer" stored procedure (I'd already done that and had gotten it down to less than 3 seconds), his response was that his code "couldn't use a stored procedure". Like I said..."In the right hands...". 😉
My recommendation is that if you bring an ORM inhouse, you also need to bring an expert inhouse for several days to teach your Developers the right and wrong ways to use it. Otherwise, you'll have similiar nightmares such as those I've had because the people using the ORMs just didn't understand and didn't have enough knowledge to even care.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2011 at 8:11 pm
Oh yeah... I almost forgot. If you're going to use an ORM so the "developers" don't have to work with the DBA and don't really have to understand anything about SQL or databases or so the "developers" can "design" databases via the ORM, then don't bring it inhouse. You will pay dearly for such a decision.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2011 at 4:54 am
Jeff Moden (4/3/2011)
Oh yeah... I almost forgot. If you're going to use an ORM so the "developers" don't have to work with the DBA and don't really have to understand anything about SQL or databases or so the "developers" can "design" databases via the ORM, then don't bring it inhouse. You will pay dearly for such a decision.
This is precisely why I've seen ORMs adopted by several development teams and it resulted in exactly the kind of things you would expect.
I firmly and honestly believe that ORM tools are wonderful and excellent when well used. I believe the same thing about firearms, excellent tools when properly used. But placed in untrained hands, improperly supervised, horrific results can come about. I've worked with four teams that decided to implement an ORM framework. Three of the four decided that the best way to do it was to eliminate any concept of database design from the approach. Only one of these three teams, as I write this today, actually has delivered their project. The other two are YEARS behind schedule, despite eliminating, what I was told, was the slowest part of the development cycle, database design. The one of the three that went into production is having lots and lots of performance problems (last time I looked, I left the company). The other two teams were having even more horrific problems. They hit everything bad, the N+1 problem, the inability to pull data for reports, excessive execution plans due to poorly laid out code, really, really bad tsql... the list goes on & on.
That other group, the one that worked with the database developers to come up with a data model and use the ORM to actually MAP (which is what the "M" stands for) between the object and relational, they delivered the project on time, and it's very functional. They've had updates without issue. It works.
In short, it's not ORM, it is how ORM is used.
"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
April 4, 2011 at 4:57 am
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
April 4, 2011 at 7:49 am
Grant Fritchey (4/4/2011)
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.
I'll ditto that and the previous post Grant posted. Item 2 turns out to be the real pisser, though, because a lot of teams adopt ORMs to eliminate the need for communication between the Dev Team and the DB Team because most folks consider that area to be one of the "slow" spots in Development. I can't stress what Grant said in Item 2 enough. Communication between the two teams is paramount with or without an ORM.
I'll add one more thing that left out... for some reason, people who adopt the use of an ORM believe that the ORM will always be correct and they stop Unit Testing to "save time". ORMs are NOT a reason to stop good and proper Unit Testing prior to a release unless you really enjoy the proverbial black-eye your Dev Team will get either during QA and UAT or you really enjoy massive amounts of rework.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2011 at 8:29 am
Thank you Jeff and Grant. These are excellent responses - and to be honest, a little bit differently from what I expected. Basically, from what I'm seeing, the basic CRUD operations should be safe, but all generated T-SQL code needs to be examined and validated. This seems like a good compromise over all data access being done through stored procedures.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 4, 2011 at 12:23 pm
The biggest problem I've heard about is the lack of additional tuning done with the ORM tools. Implicit conversions with varchar -> nvarchar (and vice versa) can be an issue, and blow your indexes out of the water.
There are also issues with no tuning SQL, or perhaps not re-examining the way the objects are stored and cleaning them up. The ORM might do things like drop LOB data in your table, but you might want a vertical partition to allow for online operations on a busy table. This is in addition to cleaning up the ORM calls and substituting in stored procs in places
April 5, 2011 at 6:37 am
I've worked with a shop that did a lot in Linq as an ORM tool. It had the advantage of being a good RAD tool, but it meant that I couldn't do the kind of performance tuning that I'm used to, since I couldn't control the actual queries.
In my experience, and that's limited in this case, it's one of those things that doesn't solve what it's meant to. It works beatifully for people who really don't need it, and creates hidden problems for the very people it's meant to help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 6, 2011 at 6:20 am
How should ORM tools perform SQL actions?
They shouldn't be allowed near a SQL Server, at least not a production one. There, I said it! 😀
There really are lots of reasons to avoid these for any app that needs (or may need) to scale or have good concurrency.
What about sproc enforcement? Well, at least with LINQ to SQL you introduce some restrictions with what you can do in the sproc, such as use temporary tables. I wouldn't be surprised if others don't have limitations about what types of metadata and operations they can interogate or accept in sprocs.
The businessman in me WANTs everyone in the world to use ORMs for SQL Server development. I get paid good money to clean up the messes they can leave behind. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 6, 2011 at 11:59 am
Well, Wayne asked me to drop in with another viewpoint just to try to get as many as possible.
ORM = Incredibly annoying to inherit. ORM is (personal experience opinion) the shortcut devs take to proper database designing and coding... and I'm all for it for an application that can fit into MS Access instead, or never really needed a DB Dev in the first place and you just needed a datastore and had MS SQL handy. CRUD procs are quick and easy to generate and the ORM has little reason to need to do that. Anything else is usually a major performance fail.
Inherit a project that's been in ORM for a year and you inherit a disaster area. Especially if you're not given time and resources to cleanup the mess. Yes, I tend to consult at a lot of shops that never had a strong jack of all trades on staff.
However, I'll defer to the comments above that it *can* be done right. I don't know ORM very well (and I probably should, there's just so much time in a day). I just know the utter crap I've inherited that they've built and either have to live with or workaround. It's like anything else, where do you want to spend the time? In the beginning, or at the end? Either way, you're going to be spending time designing properly.
Oh, yeah, one other thing I've heard: It's great for 'prototyping'. Have you ever seen a prototype get a complete re-architecture between the prototype and the beginning development stage, or do you always "work with what you've got so far"?
I've never seen ORM work well. At best, it's semi-invisible on apps I never have to care about the optimization on. At worst I've made CIO's cry when I tell them it's time to rip out ORM and start doing it the right way, because they're too entrenched in the model and everything's too tightly coupled. Your webscreen should not dictate my table design.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 6, 2011 at 7:24 pm
Your webscreen should not dictate my table design.
I LOVE that line!! I think it will be my new tag line when I hit clients that use ORMs. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 11, 2011 at 1:20 pm
In addition to the "right tool in the right hands" comment I should like to add that you should make sure that you are using the right tool for the right reason.
If the tool is being used to get around a break down in the relationship between the DBAs and the development community then things have gone badly wrong and will get worse fast. DBAs and the development community have to work together. Remember, the competition are the companies who compete with your company, not your internal structures.
The competition will love it if all they've got to do is watch you fight amongst yourselves so all they have to do is go in and bayonet the wounded!
nHibernate can use stored procs just as it can use direct table/view access. The whole point of these tools is to separate out the needs of the object layer from the needs of the relational layer. Obviously there is some cost to doing this because nothing is for free but when developed in sympathy with each paradigm a great deal can be achieved.
ORM tools seem to be good at prototyping and greenfield development but are the polish on the poo of a brown field development.
As applications become ever more open it is more vital than ever that the data layer is secure.
This Sunday there was an article in the papers about 19 companies being shut down in the UK due to data protection violations. These weren't bugs, these were violations of the data protection act. Every piece of data needs to be categorised to determine what security access is appropriate and to what roles. Again, getting the ORM tool set up appropriately is extremely important so you don't accidentally expose your data.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply