x

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! :)
more ▼

asked Mar 24 '11 at 06:46 AM in Default

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

um ... "It's been proved that stored procedures are no quicker than stored procedures..." ?
Mar 24 '11 at 07:03 AM Fatherjack ♦♦
@Fatherjack - edited, ta.
Mar 24 '11 at 07:23 AM Matt Whitfield ♦♦
I think you covered the most of it! :)
Mar 24 '11 at 07:38 AM Håkan Winther
@Håkan Winther - Thanks :)
Mar 24 '11 at 07:42 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first
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.
more ▼

answered Mar 24 '11 at 08:16 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

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...
Mar 24 '11 at 03:05 PM Matt Whitfield ♦♦
@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.
Mar 25 '11 at 08:06 AM Grant Fritchey ♦♦
:) 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...
Mar 25 '11 at 09:27 AM Matt Whitfield ♦♦
Oh jeez! I actually didn't know that. i've been working with EntityFramework and nHibernate where there is a lot more power.
Mar 25 '11 at 09:43 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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!
more ▼

answered Mar 24 '11 at 10:15 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

It's all good stuff! Thank you sir :)
Mar 24 '11 at 03:06 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
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...
more ▼

answered Mar 24 '11 at 07:05 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 73 77 107

I can certainly do that. But I want to have both angles covered...
Mar 24 '11 at 07:23 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Mar 24 '11 at 07:33 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

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?
Mar 24 '11 at 07:37 AM Matt Whitfield ♦♦

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).
Mar 24 '11 at 07:44 AM WilliamD

@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.
Mar 24 '11 at 08:04 AM WilliamD
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...
Mar 24 '11 at 03:01 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

If you really want to watch the fur fly, say that object-relational mapping is the Vietnam War of computer science. 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). 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 (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.
more ▼

answered Mar 24 '11 at 10:45 AM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

Sounds like a reasonable solution, definitely. Thanks for the input :)
Mar 24 '11 at 03:13 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x402
x241
x4

asked: Mar 24 '11 at 06:46 AM

Seen: 2759 times

Last Updated: Mar 24 '11 at 07:23 AM