Another religious question: LINQ vs stored procedures
-
First of all, thanks very much to everyone who kicked in with thoughts regarding MVC. I'm currently doing some upgrades / installs for VS 2012 since I've been running 2010 so that I can give MVC 4 a go. Since I'm reevaluating technologies, here's something I just don't get - but perhaps I'm missing something. A year or two ago I played with LINQ. Essentially, it's a nice little chunk of technology to move your db queries into your code. My approach has traditionally been to use stored procedures, keeping SQL stuff in the database and procedural code / logic in the application. Why on earth would I want to move my queries into C# code? As long as the params and columns returned don't change, I can make changes all day long in Sql Server and never touch web apps or services. If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something. I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table. What am I missing?
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
They're not mutually exclusive. I use neither. Old tried-and-true still rules.
Christopher Duncan wrote:
SQL stuff in the database
The database is for data; code belongs in a code base (preferably a library).
Christopher Duncan wrote:
move my queries into C# code
Because they're not there yet, but should be? And stored procedures still leave SQL in the program anyway in the form of the EXECUTE statements and parameters, etc.
Christopher Duncan wrote:
I can make changes all day long in Sql Server and never touch web apps or services
That's a baaaaad thing. Your code relies on the procedures doing a particular thing, but the procedure could be changed to do something completely different. Or a table could be changed in a way that breaks the procedure. And besides you'd still have to update the EXECUTE statements if you add or remove parameters.
Christopher Duncan wrote:
If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something
Exactly; that's a gooood thing.
Christopher Duncan wrote:
I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table
That's about right. There's a guy on my team now who seems to think that every new thing is the bestest way to do things and any (me) who want to stick with tried-and-true need to be enlightened.
-
First of all, thanks very much to everyone who kicked in with thoughts regarding MVC. I'm currently doing some upgrades / installs for VS 2012 since I've been running 2010 so that I can give MVC 4 a go. Since I'm reevaluating technologies, here's something I just don't get - but perhaps I'm missing something. A year or two ago I played with LINQ. Essentially, it's a nice little chunk of technology to move your db queries into your code. My approach has traditionally been to use stored procedures, keeping SQL stuff in the database and procedural code / logic in the application. Why on earth would I want to move my queries into C# code? As long as the params and columns returned don't change, I can make changes all day long in Sql Server and never touch web apps or services. If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something. I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table. What am I missing?
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
While I would say you're not missing anything, my experience is that people who write SP's have really no good practices regarding re-use (I believe another person responded about the lack of re-use with SP's as well.) And the triggers can get huge, spaghetti code, and invariably have undocumented logic and special case handlers, often using hardcoded data values. It's a mess, and when you talk to the people managing the code, they invariably say, we know it's a mess but we've had to make all these patches without any budget to fix the real underlying problems. And why's that? Because that would require changes to application, and the app devs, that were outsourced to write the app to begin with, are no longer around and nobody knows how to make changes in THAT code base without breaking half a dozen other features. So, that describes my real-world experiences, and probably those of many other people. Also (stepping down from my rant platform), what you're describing in both cases is a 2-tier environment in most cases: app talking directly to the database. I hope I never have to work in a 2-tier environment ever again, but most people don't understand the benefits of a 3-tier environment. First off, you can isolate the application from "the database business." The stuff that manipulates data in the database's representation (aka schema) rather than the application's model, which can be quite different (but I'm not saying anything you don't already know.) But, what I like to do is then choose whether something should be an SP, or the SQL can be auto-generated by the middle tier, or it pulls in additional metadata that supplements the DB schema for further automation, or, as a last resort, the code in the middle tier does some processing of the data in C# (or whatever.) But it's hard to convince people to take the time to architect a 3-tier system in which most stuff can be handled by metadata (schema + domain-specific declarative stuff) and then leverage either the middle tier or the SP's for the stuff that doesn't fit. So, the question, in my mind, isn't "what am I missing with SP's vs Linq vs EF?", but "why am I still stuck in a 2-tier development environment?" Well, that's my somewhat ranty reply. :) Marc
-
Joe Woodbury wrote:
stored procedures can cause major bottlenecks
You are claiming that for every single bit of SQL that implementing that in the application is going to be significantly faster? And no one has actually discovered that until now? Not to mention of course that requirements, architecture and design have far more impact on systems in terms of performance. A poor data model can completely destroy a system regardless of how it is implemented.
Joe Woodbury wrote:
and they had tests to prove it
I am guessing they had "benchmarks" to "prove" it. I suspect that for most of the major technologies that I am familiar with that I can come up with a benchmark in technology X that proves it is better than technology Y. Naturally I will be able to reverse X and Y.
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:
Because stored procedures take computing time and aren't always trivial
Versus for example dragging the entire database across the network, doing calculations on a single client box and then sending the entire database back? (And just to be clear that isn't hyperbole - I actually encountered a system that did that and it would only use one client machine.)
Joe Woodbury wrote:
The answer is, of course, it depends
Exactly. Which is far different than claiming that stored procs are always bottlenecks.
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.)
-
While I would say you're not missing anything, my experience is that people who write SP's have really no good practices regarding re-use (I believe another person responded about the lack of re-use with SP's as well.) And the triggers can get huge, spaghetti code, and invariably have undocumented logic and special case handlers, often using hardcoded data values. It's a mess, and when you talk to the people managing the code, they invariably say, we know it's a mess but we've had to make all these patches without any budget to fix the real underlying problems. And why's that? Because that would require changes to application, and the app devs, that were outsourced to write the app to begin with, are no longer around and nobody knows how to make changes in THAT code base without breaking half a dozen other features. So, that describes my real-world experiences, and probably those of many other people. Also (stepping down from my rant platform), what you're describing in both cases is a 2-tier environment in most cases: app talking directly to the database. I hope I never have to work in a 2-tier environment ever again, but most people don't understand the benefits of a 3-tier environment. First off, you can isolate the application from "the database business." The stuff that manipulates data in the database's representation (aka schema) rather than the application's model, which can be quite different (but I'm not saying anything you don't already know.) But, what I like to do is then choose whether something should be an SP, or the SQL can be auto-generated by the middle tier, or it pulls in additional metadata that supplements the DB schema for further automation, or, as a last resort, the code in the middle tier does some processing of the data in C# (or whatever.) But it's hard to convince people to take the time to architect a 3-tier system in which most stuff can be handled by metadata (schema + domain-specific declarative stuff) and then leverage either the middle tier or the SP's for the stuff that doesn't fit. So, the question, in my mind, isn't "what am I missing with SP's vs Linq vs EF?", but "why am I still stuck in a 2-tier development environment?" Well, that's my somewhat ranty reply. :) Marc
I'm totally with you conceptually, although the rather loose way I phrased things allowed you to make an incorrect assumption. It doesn't matter if you have 2 tiers or 42 - ultimately, you have to talk to the database. When you do, you can write your queries in compiled code, be it a separate assembly, a web service, etc. or you can write them in stored procedures. My point is that I don't see the benefit in writing queries in a compiled, procedural language as opposed to letting sql be sql and do what you pay it to do. On the other hand, it sounds like using LING with non relational db stuff could be very handy indeed. So, put down that flamethrower, son, and keep your hands where I can see them. :-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
-
Christopher Duncan wrote:
Why on earth would I want to move my queries into C# code?
No idea. After all why would anyone want a database layer in their application when it would obviously make more sense to scatter it throughout the business layer in exactly each spot where it is used?
Christopher Duncan wrote:
What am I missing?
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. So you hire a DB guru. And they fix it. If you managed a reasonable business interface with your store procs you will have no or minimal changes to your application code. Without you will be refactoring everything.
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
-
I have not and never intend to use linq to the database but then new and shiny does not attract me. Linq against iEnumerable is just excellent. EF is a nightmare for new devs, sure it is easy and very quick developing the app but they have no depth in their knowledge of the database. Take a look at some of the questions in Q&A, they have no idea how a database works. I hate black box software, if it breaks (and it does) you are screwed. EF being in it's 4th or 5th version is much more stable but is still a black box.
Never underestimate the power of human stupidity RAH
Yeah, I've been reading an MVC book today and Linq against IEnumerable sounds extremely handy.
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
-
They're not mutually exclusive. I use neither. Old tried-and-true still rules.
Christopher Duncan wrote:
SQL stuff in the database
The database is for data; code belongs in a code base (preferably a library).
Christopher Duncan wrote:
move my queries into C# code
Because they're not there yet, but should be? And stored procedures still leave SQL in the program anyway in the form of the EXECUTE statements and parameters, etc.
Christopher Duncan wrote:
I can make changes all day long in Sql Server and never touch web apps or services
That's a baaaaad thing. Your code relies on the procedures doing a particular thing, but the procedure could be changed to do something completely different. Or a table could be changed in a way that breaks the procedure. And besides you'd still have to update the EXECUTE statements if you add or remove parameters.
Christopher Duncan wrote:
If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something
Exactly; that's a gooood thing.
Christopher Duncan wrote:
I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table
That's about right. There's a guy on my team now who seems to think that every new thing is the bestest way to do things and any (me) who want to stick with tried-and-true need to be enlightened.
I know what you mean by tried-and-true. To a degree, that's what I've been invoking. Developing web apps with the sql code in the database and the procedural code in the app is the way things have been done for a long time, and it still works just fine. That said, I try to keep an open mind as new technologies come out so I don't miss anything worthwhile. Of course, I've also been in the biz long enough to know that every time you turn around MS is telling you to throw away what you used to do in favor of this new, shiny thing - which they'll in turn tell you to throw away next year. Consequently, I take all this stuff with a grain of salt. :)
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
-
First of all, thanks very much to everyone who kicked in with thoughts regarding MVC. I'm currently doing some upgrades / installs for VS 2012 since I've been running 2010 so that I can give MVC 4 a go. Since I'm reevaluating technologies, here's something I just don't get - but perhaps I'm missing something. A year or two ago I played with LINQ. Essentially, it's a nice little chunk of technology to move your db queries into your code. My approach has traditionally been to use stored procedures, keeping SQL stuff in the database and procedural code / logic in the application. Why on earth would I want to move my queries into C# code? As long as the params and columns returned don't change, I can make changes all day long in Sql Server and never touch web apps or services. If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something. I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table. What am I missing?
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
As someone who has worked as both a DBA and developer I have a pet theory about this development away from stored procedures. Like you I tend to stick to stored procedures and pass the parameters through from the client. I have noticed that DBA's have a tendency to lock down as much of the database as possible and in many companies a stored procedure needs to go through a thorough vetting process before it is allowed into the database. So add to the project lifecycle the client and database qc procedures and you have a bit of a nightmare on your hands - plus if a stored procedure needs to be changed... As a consequence developers will do what they can to find ways around this spanner in the works of their project. So the developers decide to do away with stored procedures and do the heavy lifting at the client end because they can navigate around the whole DBA qc environment and consequently get their projects completed more quickly. This is my pet theory based on experience in small and very large companies. I tend to think that if a stored procedure is written well and the correct indexes and partitions are created on database tables there should be no real problem with data access bottlenecks. The alternative is to do all this work on the client which is faster, development wise, and requires less database knowledge.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
As someone who has worked as both a DBA and developer I have a pet theory about this development away from stored procedures. Like you I tend to stick to stored procedures and pass the parameters through from the client. I have noticed that DBA's have a tendency to lock down as much of the database as possible and in many companies a stored procedure needs to go through a thorough vetting process before it is allowed into the database. So add to the project lifecycle the client and database qc procedures and you have a bit of a nightmare on your hands - plus if a stored procedure needs to be changed... As a consequence developers will do what they can to find ways around this spanner in the works of their project. So the developers decide to do away with stored procedures and do the heavy lifting at the client end because they can navigate around the whole DBA qc environment and consequently get their projects completed more quickly. This is my pet theory based on experience in small and very large companies. I tend to think that if a stored procedure is written well and the correct indexes and partitions are created on database tables there should be no real problem with data access bottlenecks. The alternative is to do all this work on the client which is faster, development wise, and requires less database knowledge.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
I think your assessment is spot on. Since I tend to write about life in the real world and how to cope with all those pesky humans, this fits right in with my own perceptions as well. Add the shiny factor to this and you have a powerful force for change, whether it's warranted or not.
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
-
As someone who has worked as both a DBA and developer I have a pet theory about this development away from stored procedures. Like you I tend to stick to stored procedures and pass the parameters through from the client. I have noticed that DBA's have a tendency to lock down as much of the database as possible and in many companies a stored procedure needs to go through a thorough vetting process before it is allowed into the database. So add to the project lifecycle the client and database qc procedures and you have a bit of a nightmare on your hands - plus if a stored procedure needs to be changed... As a consequence developers will do what they can to find ways around this spanner in the works of their project. So the developers decide to do away with stored procedures and do the heavy lifting at the client end because they can navigate around the whole DBA qc environment and consequently get their projects completed more quickly. This is my pet theory based on experience in small and very large companies. I tend to think that if a stored procedure is written well and the correct indexes and partitions are created on database tables there should be no real problem with data access bottlenecks. The alternative is to do all this work on the client which is faster, development wise, and requires less database knowledge.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
do all this work on the client which is faster, development wise, and requires less database knowledge.
The deuce you say.
-
GuyThiebaut wrote:
do all this work on the client which is faster, development wise, and requires less database knowledge.
The deuce you say.
I don't mean that everyone who uses the client side option knows less about databases.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
First of all, thanks very much to everyone who kicked in with thoughts regarding MVC. I'm currently doing some upgrades / installs for VS 2012 since I've been running 2010 so that I can give MVC 4 a go. Since I'm reevaluating technologies, here's something I just don't get - but perhaps I'm missing something. A year or two ago I played with LINQ. Essentially, it's a nice little chunk of technology to move your db queries into your code. My approach has traditionally been to use stored procedures, keeping SQL stuff in the database and procedural code / logic in the application. Why on earth would I want to move my queries into C# code? As long as the params and columns returned don't change, I can make changes all day long in Sql Server and never touch web apps or services. If my db logic is in the code, I have to recompile and deploy the binaries every time I touch something. I know it's the shiny new thing to play with but other than resume enhancement, I just don't see what value it brings to the table. What am I missing?
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
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')
-
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