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