How should ORM tools perform SQL actions?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server 2008 - General » How should ORM tools perform SQL actions?

Add to briefcase

18 posts, Page 1 of 2

1 2 »»

How should ORM tools perform SQL actions?

Expand / Collapse

Author

Message

Posted Friday, April 01, 2011 1:36 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370, Visits: 8,235

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1087686

Posted Friday, April 01, 2011 2:23 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #1087709

Posted Friday, April 01, 2011 2:49 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370, Visits: 8,235

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.

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1087714

Posted Sunday, April 03, 2011 7:55 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

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...". Wink

Wink

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

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1087889

Posted Sunday, April 03, 2011 8:11 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

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

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1087891

Posted Monday, April 04, 2011 4:54 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:44 PM
Points: 13,381, Visits: 25,173

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1087971

Posted Monday, April 04, 2011 4:57 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:44 PM
Points: 13,381, Visits: 25,173

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1087972

Posted Monday, April 04, 2011 7:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

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

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1088053

Posted Monday, April 04, 2011 8:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370, Visits: 8,235

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1088095

Steve Jones - SSC Editor

Posted Monday, April 04, 2011 12:23 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433, Visits: 13,745

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

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1088221

« Prev Topic | Next Topic »

Add to briefcase

18 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse