question

Matt Whitfield avatar image
Matt Whitfield asked

How is best to persuade someone that stored procedures can outperform ORMs?

Ok, so where I work we have a new software director coming in, and one thing he said in a presentation to the team was: > Why are you using stored procedures? It's been proved that stored procedures are no quicker than ORMs... Which I know is total rubbish. I had thought of the following points: - Plan re-use is hugely enhanced by using Stored Procedures. Particularly when dealing with string fields, where LINQ decides it would be good to declare a variable which has the exact same length as the input string, meaning each exec with a string of differing length means a new plan - It is sometimes nigh-on impossible to force the QO into choosing the best plan when using ORMs, particularly when querying large data sets and performing aggregation. No divide and conquer = limited choice. - You can't get an ORM to produce the relevant hints when necessary (for example, NOEXPAND on an indexed view in standard edition) - It's much more painful trying to establish the actual plan used for an ORM derived query. Possible, certainly, but much more painful. - It's not possible to use MERGE in LINQ, not sure about other ORMs, but I highly doubt it - You can't do any XML operation queries using ORMs So - what have I missed? Bearing in mind that I *will* have missed something! :)
stored-proceduresperformanceorm
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

um ... "It's been proved that stored procedures are no quicker than stored procedures..." ?
0 Likes 0 ·
@Fatherjack - edited, ta.
0 Likes 0 ·
I think you covered the most of it! :)
0 Likes 0 ·
@Håkan Winther - Thanks :)
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
The deal is, you need to look at the ORM tool. Some of them are fairly reasonable about the generated code and only create parameterized queries, which are no different than stored procedures. But, some of them are very, very bad about creating well structured parameterized queries (nHibernate is notorious). Still others just generate ad hoc sql, which you know stinks. So while I'm not a fan of ORMs, I'd concentrate on the details of the ORM in question and figure out how it behaves.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

At the moment we're using LINQ which does do only parameterized queries - but in my experience stored procs can perform a lot better than parameterized queries... A quick example - if you have an expensive CTE that you materialise multiple times, then you can stick that in a temp table and reference that instead, and replace double or triple expensive CTE with a single expensive call followed by cheap cached reads...
1 Like 1 ·
@Matt Yeah, but you can build out a giant parameterized query just like a procedure. I know these tools generally don't do that (believe me, I'm not a fan), but you can work with them. In general, I really try to work with developers and let them make decisions (even ones as weak as ORM tools) but get them to do things properly. It isn't easy, and most of the time I fail, but that's usually the goal.
0 Likes 0 ·
:) Definitely sense... I realised that I missed out some key words in my original comment - LINQ will only do **single-statement** parameterized queries. That's where my main issue with it comes from, performance wise...
0 Likes 0 ·
Oh jeez! I actually didn't know that. i've been working with EntityFramework and nHibernate where there is a lot more power.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
For me I would endeavour to demonstrate it rather than explain it in theory. All your arguments are sound but the proof of the plan is in the execution.. to butcher a well known phrase...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I can certainly do that. But I want to have both angles covered...
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
Not that I am pro-ORM, but won't the greatly improved query hashing and fingerprinting greatly help an ORM to still re-use plans? I was under the impression MS had done **alot** of work to get this sort of thing under control, even without using forced parameterisation. Don't forget the idea of a DAL inside the db - something that I like and have had success in implementation in the past. The de-coupling of GUI/Client to database schema greatly reduces administrative overhead as well as giving you flexibility. Does ORM give you that advantage too? It is something that the C# guys are looking at at work and something I see critically, so if you have any ammo, I'll gladly take some of it.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I still see a lot of separate plans for very similar LINQ queries - as I was saying above... Not sure what you are meaning about the DAL inside the DB bit, though? Can you explain more?
0 Likes 0 ·
What I mean is, no direct access to tables/views, rather offer up functions and sprocs to read and manipulate data. You control permissions on them rather than base tables/views, and can make table/schema based changes that are "invisible" to the (for want of a better word) "Data Access Layer" (DAL). Maybe that is an old-fashioned way of doing things, but I see it as making it easier as it keeps GUI code separate from DB code. If I am wrong - tell me, I would love to see the other side of things (as you are a C# coder too).
0 Likes 0 ·
@Matt Whitfield - are you seeing the plans are all with a different query hash or query plan hash? It is possible to have a lot of entries that are sort of ghost entries. They all have the same query plan hash or query hash, pointing to the true execution plan. I remember seeing a talk done by Kim Tripp (I think) where she went into the details of that and how 2008 and 2008R2 had received a lot of work to get improvements for exactly these use cases. I reckon it was due to lots of pressure to get ad-hoc queries to run faster for systems like SAP - just a guess though.
0 Likes 0 ·
I see what you're saying - and no - that sounds like a reasonable idea to me. What wouldn't be reasonable is doing the more typical use case of a DAL and turning the data into objects, which is definitely better handled in the application tier, for scale-out reasons if nothing else...
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
I would also be inclined to demonstrate it in terms of the SQL queries being run generated by whatever ORM-based toolset used, and show by how much the traffic decreases when using hand-crafted SQL (stored procedures or no...)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It depends what you mean by traffic though... Because for sure, network traffic will reduce, but even the most carefully crafted ORM call will generate a single-query solution, which typically leads to higher I/O and CPU use, and, in turn, execution time. I wonder if disk I/O and CPU use count as traffic :)
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
The biggy for me is security - WilliamD touched upon it, but I think it's worth mentioning it explicitly. You can implement a DAL/API type layer, but most ORMs rely on the fact that they require, read and write access to the underlying tables - something I would never allow in most databases. I use SPs to control and manage the security, ORM seems to dilute that. The other issue I've seen is that developers comfortable in the OO world simply map objects to entities and if they need to get data that spans multiple tables, they perform multiple database calls, rather than one to an SP that might utilise something magical called joins! Neither of which answer the question about which is quicker, just gave me an opportunity to rant!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It's all good stuff! Thank you sir :)
0 Likes 0 ·
Kevin Feasel avatar image
Kevin Feasel answered
If you really want to watch the fur fly, say that [object-relational mapping is the Vietnam War of computer science]( http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx). That's sure to raise a few eyebrows... I'm with the folks who say "it depends on the OR/M" tool. Even sticking to the Microsoft world, Entity Framework 4 is considerably better than LINQ-To-SQL (which tended not to cache query plans) or EF 3.5 (which did evil, evil things). My biggest concern with an object-relational mapper would be the direct table read/write requirements (like @Kev Riley brought up), but the second-biggest concern is doing "advanced" filtering. (#3, by the way, is lack of plan re-use, which was definitely a problem with LINQ and EF 3.5, but apparently [has improved in EF 4]( http://awanderingmind.com/2011/01/03/entity-framework-from-a-dba-perspective-part-2/)). It's cool that LINQ is bringing some set-based notions to C# and VB.Net, but "make different database calls and combine data sets together in the business layer" usually won't perform nearly as well as a similar SQL query. An alternative to using an OR/M tool might be to roll your own access layer. In our case, we have a foundation, based vaguely on [CSLA]( http://www.lhotka.net/cslanet/) (but much modified), which uses stored procedures for all operations. We also have a code generator which uses a combination of tables and views to create generated C# objects and CRUD stored procedures. It also has functionality which allows us to create objects from stored procedures (either as a plug-in to the generated table/view objects or as stand-alone data result sets) and simply run stored procedures which don't return result sets. It's all stored procedures, all the time. Performance is not quite as good as if we lovingly hand-crafted each procedure, but our developers have a killer workload, so that option flew out the window years ago. This took us quite some time to develop and get right, though.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sounds like a reasonable solution, definitely. Thanks for the input :)
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
Numerous good points have been raised here, but I think one key tool that I haven't seen mentioned is to show them the results from SQL Server Profiler. You can show them that the queries written are sometimes suboptimal and that query hints like noexpand are never used and there is no way to make them be used. More than that, show them that in the end it is still sending crafted SQL and it will not be magically faster than hand crafter SQL (in fact, it will often be slower than hand crafted SQL if the person doing the hand crafting is skilled in optimization.) Personally, I think ORMs are sometimes very useful tools and sometimes they are not. But, at least in what I have seen so far, an ORM will either match or substantially underperform a stored procedure written by someone who knows how to optimize. The only times I have seen the ORM actually perform better than a stored procedure involved major efficiency problems in the stored procedures that were then remedied. Of course, there are still many good reasons to use ORMs for certain projects, but at least from all I have seen effeciency is not one of them.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Agree totally. I am actually advocating the use of some ORM stuff, for simpler things like simple table reads... But just not as a 'for everything' solution. And also agree about the procs - I guess for a lot of people they're better off writing ORM code than using bad procs (which, let's face it, are pretty common!)...
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.