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:
Which I know is total rubbish. I had thought of the following points:
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.
answered Mar 24, 2011 at 08:16 AM
Grant Fritchey ♦♦
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!
answered Mar 24, 2011 at 10:15 AM
Kev Riley ♦♦
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...
answered Mar 24, 2011 at 07:05 AM
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.
answered Mar 24, 2011 at 07:33 AM
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.
answered Mar 24, 2011 at 10:45 AM