Another religious question: LINQ vs stored procedures
-
IMHO If you are / have access to competent DB developers (i.e. you can write or have written good SQL stored procedures, can debug them and, if necessary, measure and tweak performance) then stored procedures are the way to go. If you are scared of SQL, or are crap at it, have no idea what is efficient or not, can manage a quick select or insert butr quake at the sight of anything more complex, then you might be more comfortable writing C# or VB rather than SQL and let LINQ to SQL manage the SQL code for you. I am with you on the SP front - I much prefer the separation of concerns - I can have my .Net devs write against what is to essentially an interface, and my DB developers concentrate on the DB. IF a SP is inefficient, I can tinker with it, and test it independently of any .Net code, and redeploy it without the need to re-deploy any client side code. With well constructed SPs I can even significantly change the underlying DB structure without worrying about changing any deployed .NET code. And if it is more efficient to mangle the data in the application, I can still have an SP return a record set and use LINQ to POCO to do the mangling. My first use of SPs was entirely due to security of the DB - access to which was restricted entirely to stored procedures, so even giving someone access to the DB server still only gave them access to the SPs - they couldn't even see the tables - let alone try to update them. (AM I starting to sound religious enough?)
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Yep, that's largely the way I've seen things as well.
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
-
jschell wrote:
After all why would anyone want a database layer in their application
You're making the assumption that I was stipulating a 2 tier app. As I told Marc, even if you have a db layer, ultimatey you either write your queries in a compiled language or you put them in sql procs in your db. I'm not seeing the benefit of the former.
jschell wrote:
Being a bit sarcastic lets suppose that you are less than a DB guru when it comes to your database. And no one else is either. And something is wrong with your database. You don't even know what.
Well, since you're invoking sarcasm, I'd say that if your team is that inept, perhaps you should all just step away from the keyboard. Perhaps a career change might even be in order. :) Seriously, though, incompetence is not a reason to choose an architecture, although tool manufacturers make good money trying to make programming easy for those who should never be attempting it in the first place. As for refactoring, if you make major changes to your database schema, you will absolutely be refactoring your code, whether it's LINQ or ADO. It's simply a matter of where - inside or outside of your business objects. Perhaps both. And as an aside, you don't need LINQ to create a collection of absracted business objects. We've been doing that since the days of DOS / C++.
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
Christopher Duncan wrote:
You're making the assumption that I was stipulating a 2 tier app
No I wasn't. When I said "layer" I meant it in many ways but specifically that one would keep their database code in one location rather than scattering it throughout the rest of the application. The reason one does that is to make maintenance easier when changes to the database code is needed. And the same thing can be applied to using store procs in that they provide a disconnected api in the database.
Christopher Duncan wrote:
Well, since you're invoking sarcasm
Unfortunately I said that incorrectly - the first part of my response was intended to be sarcastic where the second half wasn't.
Christopher Duncan wrote:
I'd say that if your team is that inept,
There is of course a difference between "team" and "individual". Allowing all team members to hack at the database without review is probably a process failure, and one that I have seen in a company where performance and big data was a requirement. That would be a 'team' failure. An individual might also fail as well. And unless one is specifically focused on a database it might still be the case that pulling in a consultant, with specific database experience, can be helpful in any company that does do large amounts of data.
Christopher Duncan wrote:
As for refactoring, if you make major changes to your database schema
Probably but, as I believe I mentioned, with layering it is less likely that the entire code base needs to be changed versus just parts. And one might still be fairly competent at creating the interface API design while still failing at a database side solution that meets performance goals. So procs could provide that disconnect.
Christopher Duncan wrote:
And as an aside, you don't need LINQ to create a collection of absracted business objects. We've been doing that since the days of DOS / C++.
With about 20 years of experience specifically related to creating services and implementing persistent storage solutions in a variety of languages and database solutions I am aware of what they do.
-
You do you understand the meaning of the word "can" right? You are arguing assertions I never made nor even discussed. To turn this around, are you honestly asserting that stored procedures have zero CPU cost? Moreover, have you seen what some developers put in stored procedures?
jschell wrote:
I am guessing they had "benchmarks" to "prove" it.
No, actual tests with actual databases with terabytes of data which could get under extremely heavy loads due to tens of thousands of clients. Offloading some of the processing to clients helped immensely.
Joe Woodbury wrote:
You do you understand the meaning of the word "can" right?
However you also said "anything more than simple stored procedures were prohibited for this reason"
Joe Woodbury wrote:
To turn this around, are you honestly asserting that stored procedures have zero CPU cost?
Nope. But there is a big leap from there to banning everything.
Joe Woodbury wrote:
Moreover, have you seen what some developers put in stored procedures?
And that has what to do with anything? I have seen a C++ class with 200,000 lines of code. I have seen an application that dragged the ENTIRE database across the network, computed on it, and then sent it back and that specific design was a bottleneck. I have seen a VP lock up a entire database and idle 200 call center employees on a weekly basis because he insisted on having direct access to the production database. All of those however are PROCESS PROBLEMS. They have nothing to do with technology.
Joe Woodbury wrote:
actual tests with actual databases with terabytes of data which could get under extremely heavy loads due to tens of thousands of clients
Which seems like something that qualify the initial post with would have made it much clearer. Most businesses will never see anything like that however.
Joe Woodbury wrote:
Offloading some of the processing to clients helped immensely.
One might suppose however that other businesses have other business needs and thus restrictions to one business environment should not be blindly applied to all industries and all businesses.
-
jschell wrote:
Exactly. Which is far different than claiming that stored procs are always bottlenecks.
This pisses me off. I NEVER said that stored procedures are always bottlenecks. I said they CAN be bottlenecks and they can be. (Can is a conditional. Unfortunately, this isn't the first time you've distorted what was written, creating a straw man and then attacked the writer. It's getting tiresome. Please learn to read before replying.)
Joe Woodbury wrote:
I NEVER said that stored procedures are always bottlenecks
You certainly did imply that when you also said "where anything more than simple stored procedures were prohibited for this reason ". You did not qualify that statement nor did you qualify your following statement about the tests used to prove this with the original comment. Your follow on post, and not this one, qualified that you were talking about a specific case with a business model unlikely to ever be applicable to most businesses.
Joe Woodbury wrote:
Please learn to read before replying
You said "where anything more than simple stored procedures were prohibited for this reason". Did you meant that in fact that complex ones were allowed when you said "prohibited"? Did you have some qualification for "simple" which suggested that only really, really complex ones where prohibited or, as I took it, did you mean anything more basic than CRUD? When you said that there were tests that proved your assertions did you qualify that with the specific business case where it applied? Because I didn't see that when I "read" it. (Nor in this reply either.) There is of course a difference between qualified and unqualified statements as well as a difference between what one meant and what one wrote.
-
Joe Woodbury wrote:
You do you understand the meaning of the word "can" right?
However you also said "anything more than simple stored procedures were prohibited for this reason"
Joe Woodbury wrote:
To turn this around, are you honestly asserting that stored procedures have zero CPU cost?
Nope. But there is a big leap from there to banning everything.
Joe Woodbury wrote:
Moreover, have you seen what some developers put in stored procedures?
And that has what to do with anything? I have seen a C++ class with 200,000 lines of code. I have seen an application that dragged the ENTIRE database across the network, computed on it, and then sent it back and that specific design was a bottleneck. I have seen a VP lock up a entire database and idle 200 call center employees on a weekly basis because he insisted on having direct access to the production database. All of those however are PROCESS PROBLEMS. They have nothing to do with technology.
Joe Woodbury wrote:
actual tests with actual databases with terabytes of data which could get under extremely heavy loads due to tens of thousands of clients
Which seems like something that qualify the initial post with would have made it much clearer. Most businesses will never see anything like that however.
Joe Woodbury wrote:
Offloading some of the processing to clients helped immensely.
One might suppose however that other businesses have other business needs and thus restrictions to one business environment should not be blindly applied to all industries and all businesses.
jschell wrote:
However you also said "anything more than simple stored procedures were prohibited for this reason"
Do you understand the concept of an illustration to make a point? To anyone with the slightest literacy, this was clearly an illustration of why stored procedures CAN be bad. Now, I could understand you misreading one comment, but you continue to argue against assertions I never made and even make statements that support what I wrote, but in condescending way. Despite all this, you never refuted my actual points. Based on this and previous posts by you, I can't help but wonder if you are being intentionally antagonistic and argumentative. I don't know how old you are or how experienced in the field of computer science, but you come off as very arrogant and immature. When your errors are pointed out, you become combative and change your arguments as well as turning them back on the original poster as though it was all their fault. This borders on narcissism and makes dealing with you very unpleasant.
-
Joe Woodbury wrote:
I NEVER said that stored procedures are always bottlenecks
You certainly did imply that when you also said "where anything more than simple stored procedures were prohibited for this reason ". You did not qualify that statement nor did you qualify your following statement about the tests used to prove this with the original comment. Your follow on post, and not this one, qualified that you were talking about a specific case with a business model unlikely to ever be applicable to most businesses.
Joe Woodbury wrote:
Please learn to read before replying
You said "where anything more than simple stored procedures were prohibited for this reason". Did you meant that in fact that complex ones were allowed when you said "prohibited"? Did you have some qualification for "simple" which suggested that only really, really complex ones where prohibited or, as I took it, did you mean anything more basic than CRUD? When you said that there were tests that proved your assertions did you qualify that with the specific business case where it applied? Because I didn't see that when I "read" it. (Nor in this reply either.) There is of course a difference between qualified and unqualified statements as well as a difference between what one meant and what one wrote.
jschell wrote:
You certainly did imply that when you also said "where anything more than simple stored procedures were prohibited for this reason ".
Here is my comment: "On heavily loaded client/server applications, stored procedures can cause major bottlenecks. I was on the fringe of at least two projects where anything more than simple stored procedures were prohibited for this reason (and they had tests to prove it.)" Note "heavily loaded" and can. In the second sentence, the phrase "for this reason" builds on the conditionality of the first sentence. This is all a single paragraph, where one sentence builds on the other. What you are going, by contrast, is taking my words out of context. You then argue by setting up straw men and knocking them down and finally you blame me for making sure you understand what I wrote. You are a fool.
-
Christopher Duncan wrote:
You're making the assumption that I was stipulating a 2 tier app
No I wasn't. When I said "layer" I meant it in many ways but specifically that one would keep their database code in one location rather than scattering it throughout the rest of the application. The reason one does that is to make maintenance easier when changes to the database code is needed. And the same thing can be applied to using store procs in that they provide a disconnected api in the database.
Christopher Duncan wrote:
Well, since you're invoking sarcasm
Unfortunately I said that incorrectly - the first part of my response was intended to be sarcastic where the second half wasn't.
Christopher Duncan wrote:
I'd say that if your team is that inept,
There is of course a difference between "team" and "individual". Allowing all team members to hack at the database without review is probably a process failure, and one that I have seen in a company where performance and big data was a requirement. That would be a 'team' failure. An individual might also fail as well. And unless one is specifically focused on a database it might still be the case that pulling in a consultant, with specific database experience, can be helpful in any company that does do large amounts of data.
Christopher Duncan wrote:
As for refactoring, if you make major changes to your database schema
Probably but, as I believe I mentioned, with layering it is less likely that the entire code base needs to be changed versus just parts. And one might still be fairly competent at creating the interface API design while still failing at a database side solution that meets performance goals. So procs could provide that disconnect.
Christopher Duncan wrote:
And as an aside, you don't need LINQ to create a collection of absracted business objects. We've been doing that since the days of DOS / C++.
With about 20 years of experience specifically related to creating services and implementing persistent storage solutions in a variety of languages and database solutions I am aware of what they do.
I think we agree more than we disagree about this stuff. One of the scenarios someone brought up for doing queries in the code was the scenario where you work at a big enough company to have DBAs, procedures and politics, all of which would slow you down (nobody cares that you couldn't meet the deadline because the DBAs were stonewalling - you take the hit anyway). In that case, a reality check says you do what you gotta do. And I didn't really take the sarcasm seriously. I figured you were just yanking my chain a bit, so I thought I'd yank back. :-D
Christopher Duncan Author of Unite the Tribes: Leadership Skills for Technology Managers (2nd ed, just released) Have Fun, Get Paid: How to Make a Living With Your Creativity (Due Nov 2013) The Career Programmer: Guerilla Tactics for an Imperfect World
-
jschell wrote:
However you also said "anything more than simple stored procedures were prohibited for this reason"
Do you understand the concept of an illustration to make a point? To anyone with the slightest literacy, this was clearly an illustration of why stored procedures CAN be bad. Now, I could understand you misreading one comment, but you continue to argue against assertions I never made and even make statements that support what I wrote, but in condescending way. Despite all this, you never refuted my actual points. Based on this and previous posts by you, I can't help but wonder if you are being intentionally antagonistic and argumentative. I don't know how old you are or how experienced in the field of computer science, but you come off as very arrogant and immature. When your errors are pointed out, you become combative and change your arguments as well as turning them back on the original poster as though it was all their fault. This borders on narcissism and makes dealing with you very unpleasant.
Joe Woodbury wrote:
Do you understand the concept of an illustration to make a point?
Your original comment did not seem like an example of one case where it could be a problem. It is that comment to which I responded of course. The statements, taken together, seemed to suggest strongly that in most cases stored procedures should be avoided.
Joe Woodbury wrote:
Despite all this, you never refuted my actual points.
I didn't need to because you provide further qualification in follow on posts that made it clear (presumably) that you were referring to a very limited problem domain space. That however doesn't alter the fact that your original comment did not make that clear.
Joe Woodbury wrote:
When your errors are pointed out,
Your original comment was made without qualification. I disputed the totality of that statement. Best I can tell you are now agreeing that your original comment only applies to a limited domain. I don't see an error in my part in terms of your lack of the original qualification. But I can assure you that I am more than willing to accept when I am in error - when in fact that is the case. Versus of course someone just repeatedly claiming that that is the case.
Joe Woodbury wrote:
...and makes dealing with you very unpleasant.
Best I can suggest for that is that you park the emotionalism at the door.