An outlet for my obsession with technology
Great OR/M resources
Today I had a conversation with a co-worker that started with my declaration of not wanting to ever have to code a stored procedure. Adamantly I know that there are places where sprocs are the solution to a problem but in today’s landscape I feel those are edge cases.
Our argument..err..I mean conversation had all the usual discussion points.
co-worker points:
- sprocs are faster
- sprocs are more secure
- sprocs provide a layer in n-tier
My points:
- the dollar cost of milliseconds of speed
truesee comments; but all queries should be parameterized hence not a valid argument.- an unnecessary abstraction with no benefit (except as a pro sproc discussion point)
- sprocs often end up not separating query from command
I also made some claims, which I completely stand by, that might have seemed outlandish and bordering on reckless. Specify I said, based on the metric that 50% of a projects code is related to data access, that I would be able to complete a project in almost 40% less hours. I also suggested that sprocs, in 99.9% of their use, are a waste of time and since time = money that his project was wasting thousands of dollars writing sprocs.
With that wonderful setup I would now like to present my compliation of required OR/M, LINQ and SQL information. AKA: For the love of god at least digest this information before telling me that all data access should be done through stored procedures!
ALT.NET 7: OR/M
http://altnetpodcast.com/episodes/7-object-relational-mapping/
In this episode Ward Bell (from IdeaBlade) and Jeremy Miller discuss Object-Relational Mapping and when to use ORM tools. Ward and Jeremy discuss the following:
- Object-Relational Mapping
- Object-first vs. Data-first Approaches
- Evolutionary Database Design
- Evolutionary Design
- Code Generation
- Persistence Ignorance
- POCO
- Domain-Driven Design
- The Vietnam of Computer Science
Hanselminutes
Rob Conery limps and learns about Domain Driven Design 36:28 12/1/2008 Hanselminutes is a weekly audio talk show with noted web developer and technologist Scott Hanselman and hosted by Carl Franklin. Scott discusses utilities and tools, gives practical how-to advice, and discusses ASP.NET or Windows issues and workarounds.
Herding Code 38
NHibernate performance with Ayende, David Penton, and Ben Scheirman 44:22 3/13/2009 While K Scott and Jon were at the Microsoft MVP Global Summit, we listened in on a late night debate on NHibernate performance between Oren Eini (a.k.a. Ayende Rahein), David Penton, and Ben Scheirman. Show Links: NHibernate – http://nhforge.org Ayende&#
Herding Code 10
Episode 10: LINQ 44:01 7/23/2008 K Scott leads us in a discussion of LINQ, including: What is it How introducing LINQ to .NET changed the framework LINQ Providers LINQ to XML LINQ to SQL – how it’s different from EF, tips and tricks, when to use it Links: LINQpad 3rd Party LINQ
NET Rocks!
Peter DaBetta and Adam Machanic on creating good SQL databases
Peter DaBetta and Adam Machanic on creating good SQL databases 1:00:07 5/18/2009 .NET Rocks! is an Internet Audio Talk Show for Microsoft .NET Developers.
.NET Rocks!
Ward Bell on ORMs
Ward Bell on ORMs. 59:24 1/12/2009 .NET Rocks! is an Internet Audio Talk Show for Microsoft .NET Developers.
Articles:
Why I do not use Stored Procedures : Jeremy D. Miller
http://codebetter.com/blogs/jeremy.miller/archive/2006/05/25/145450.aspx
http://blogs.msdn.com/adonet/archive/2008/03/27/ado-net-entity-framework-performance-comparison.aspx
- Yep. I can read. EF is slower…but we are talking milliseconds here!
I wonder if I could somehow come up with the total milliseconds a project “saved” by using nothing but sproc and extrapolate out the cost per millisecond….off the top of my head I would suspect it at least 500$ per millisecond….
NHibernate: Testing The Performance Urban Legend
http://www.iamnotmyself.com/2008/07/02/NHibernateTestingThePerformanceUrbanLegend.aspx
No related posts.
| Print article | This entry was posted by Eric Polerecky on June 8, 2009 at 12:13 pm, and is filed under LINQ, ORM, SQL. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site. |
about 1 year ago
>>> sprocs are faster
No, they are not. In SQL 6.5 they were, but the pre-compilation stuff is worked out at runtime in the same manner. There is *no* perf gain.
>>> sprocs are more secure
No, they are not. Yes you can set owner permissions, but you can do the same in any auth context from the app on down. SPs *do* provide you with one extra means to use the DB and SQL Auth to ask “who is this again”, but invariably your app will need that auth anyway so this security is lost.
>>> sprocs provide a layer in n-tier
N-tier is an app architecture, SPs are DB methods.
SPs are usually pushed by the DBAs or SQL wonks in a project because it makes their job a little more important. And that’s the core of the issue – there is no technical argument for SPs – there are about 1000 against.
about 1 year ago
Thanks rob, this post is one of those come home from work and vent quick rants.
Its rather frustrating to see other large projects spending so much time/money…especially in the public sector where the money is coming out of my (tax payer) pocket.
about 1 year ago
seems like a very one-sided post; i would guess that you failed to capture the other side of the discussion as you most likely failed to listen …
i would have to say the biggest advantages to using SPs would be execution plan retention and that ever sought after development strategy … reuse … yes i am aware that ORM provides the same within the application; however, it does not help outside of the application, i.e. reporting …
additionally, while SPs do not provide a layer in n-tier they are an interface to a tier in a n-tier architecture … as such, the db schema can be changed w/o necessarily affecting the i/o of the SP …
further, your 4th point is invalid … if that is the case it is bad programming … and the same possibility exists with out the use of SPs …
lastly, if the addition of SPs to your application increases the size of your project by 40% then you do not know how to write SPs … Pinal Dave has a great blog regarding SQL in general (http://blog.sqlauthority.com/) … you might want to read a few of his articles as well …
about 1 year ago
First let me say that, as I said in the comments, this post was just me venting about our conversation. Besides; the points you are making about SPs are the same tired points so forgive me for para-phrasing.
Execution plan retention means almost nothing in the context of milliseconds.
About reporting. Deep breath…..
In the podcast “Peter DaBetta and Adam Machanic on creating good SQL databases” Peter and Adam discuss some common database architecture problems. Mixing reporting and transactional in the same database = EPIC FAIL.
Second; How many of the queries that you write for the client facing portion of your application are applicable for reporting? 0%? 0.5%?
Lastly; whatever front end you slap on your application if SPs are the only access point the level of code required to work with the database is utterly to costly.
“additionally, while SPs do not provide a layer in n-tier they are an interface to a tier in a n-tier architecture … as such, the db schema can be changed w/o necessarily affecting the i/o of the SP …” …WHAT?
So you often change the database and don’t bubble those changes to the client?
“further, your 4th point is invalid … if that is the case it is bad programming … and the same possibility exists with out the use of SPs …”
“sprocs often end up not separating query from command”
Query command separation is not enforced by OR/Ms…that why I said often…BUT…how much business logic do you have in your DB? Quite a bit…I’ve seen…
“the addition of SPs to your application increases the size of your project by 40%” … LOL, You really twisted that around.
Can you please quote all my projects. And when I use the right tools for the job I’ll have free time while you and your team code SP’s and the brittle code that surrounds them
about 1 year ago
@mark;
I hope you know I understand where you are coming from. True; Microsoft has recommended using SPs for data access but managed code is managing more of our development (a good thing) and letting us focus on what’s important (building quality systems).
I know your old school, you’ve had SPs beat into your head for the past 10+ years but the times are changing.
I truly value your expertise and I hope you soon realize the issues that architecture creates.
Oh’ during our conversation you asked me to show you where Microsoft has recommended anything but SPs for data access. While I would never want to put words in Rob’s mouth and I completly acknowledge that his comments are not representive of Microsoft…I just want to make sure you know who he is (from comments above).
Please please please watch these videos.
http://www.asp.net/learn/mvc-videos/#MVCStorefrontStarterKit
http://blog.wekeroad.com/
http://subsonicproject.com/
http://tinyurl.com/lgbnyq
about 1 year ago
well this certainly took an awkward direction … not one of your counter-points makes any sense … again i feel you are not “truly” listening … and one who fails to listen can learn nothing from a conversation … akin to leading a horse to water but due to the blinders the choice to drink is not even available … i wish you well … happy programming!
about 1 year ago
@mark –
“not one of your counter-points makes any sense …”
- Ok; to many words in my reply
Lets try this:
Todays performance metrics do not offer any clear performance gain for 99.99% of queries. Execution plan (performance) is a horrible reason to stick with SPs (waste project hours)
Using the same database for transaction and reporting is one of the worst examples of the DRY principle I can think of.
And about your listening analogies. The entire point of the post was to list OR/M resource that would help others learn, from industry experts – not me, why its time for us to move to OR/M. Please listen before regurgitating age old pro-SP points that have been proven incorrect time and time again.
about 1 year ago
you seem to lack a desire to understand the points i have made …
for example …
i say SPs provide code reuse … you say “Using the same database for transaction and reporting is one of the worst examples of the DRY principle I can think of” … which by the way i agree with … but … where in the statement of “SPs provide code reuse” is there a mention of using the same database for “transaction and reporting”? … the SP should be created once and utilized on both the transaction and reporting DBs … further i only mentioned reporting as an “example” of another endpoint working with the data that is not the application … there are others …
i also agree that ORM needs to be utilized more in today’s development efforts … however, if you treat an ORM as a just another way to do data access then you’re not entirely getting it … the objective is to, for as much as possible, forget about, as you’re writing your application, forget about that there is a backing store, forget about what it is that you have to do to go and get things and concentrate on what you are trying to accomplish and what your model is supposed to be doing for your application …
ORM does not mean map to db schema … it’s the means of mapping an application’s objects to the data in a backing store … the use of / lack of use of SPs has absolutely nothing to do with ORM …
about 1 year ago
@mark -
i say SPs provide code reuse -
I’m sorry then; I thought you where referring to DRY. Then my point should have been…your creating to places to maintain the same code. no good.
I could not agree any more. OR/M tools are so much more then a pretty solid abstraction from the underlying persistence layer…I never said they are simply DAL’s…Speaking of…its not my project that is only using EF for nothing more then DAL to SPs
L2S …enough said.
about 1 year ago
Hey, check your blog. pending comment. Please approve me. Please keep the discussion technical.
about 1 year ago
you still miss the obvious … in the scenario you created from my example alternate endpoint … the transactional db would be the publisher and the reporting db the subscriber and the singularly managed SP would be part of that replication …
and again … the use of / lack of use of SPs has absolutely nothing to do with ORM …
about 1 year ago
If your using SPs for DTO…I guess thats an edge case. I totally don’t discount the need for SPs…in 1% of the daily ops.
True that SP or not does not directly translate to the general OR/M topic but…
Your project is using nothing but SPs for DAL.
You suggested that I was mad for not wanting to ever write a SP again…and that SPs where the perfered method of DA
So..in the context of this topic…OR/M has everything to do wth the use or not of SPs
about 1 year ago
ignorance of my current project implementation aside … please allow that discussions of topics can exist outside of current projects … and often one’s desired implementation can not be accomplished for a given project … also … i never suggested that you were “mad for not wanting to ever write a SP again” … depending on one’s role in the development life cycle, the one writing code may not be the one responsible for the backing store … i simply suggested that binding your application’s objects to db schema might not be prudent … depending on the project …
about 1 year ago
I think we’ve gotten quite off topic and the overall state of application design is topic that would be better served face-to-face.