No more stored procedures
-
Of course it happens, your scope of experience maybe doesn't encompass it, but try selling commercial software that depends on stored procedures that end users can and will mess about with on their own on a regular basis. Madness!
I agree - for the same reason, no application should ever use the registry, or have a config file. I mean, those pesky end users, they can never be trusted, or educated, for that matter.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
John Cardinal wrote:
Stored procedures should never be used in a commercial software application.
Wow....
John Cardinal wrote:
Performance is not noticeably better to the end user.
I guess that depends. How can you claim that it never is when 1 - stored procs are precompiled 2- stored procs lower the amount of network traffic required to make a request
John Cardinal wrote:
All data access should be done through a data access layer in a properly stratified design
Correct. And, using stored procs can help do this. How much code do you see on the web where people type SQL right into aspx files, for example ?
John Cardinal wrote:
There are many *many* things you can not do with stored procedures that you can easily do in code.
How is this possible, when a stored proc is just a bunch of SQL ? Name 3.
John Cardinal wrote:
There is little or nothing you can do to protect your intellectual property when it's in plain sight in the database in stored procedures.
If you're writing a C++ app, this is true. Assuming someone has access to your SQL Server, they can see your SQL.
John Cardinal wrote:
There is no security issue with using dynamic sql as long as you know what you are doing.
It's true that dynamic SQL doesn't guarentee security issues, but it does remove the easiest way to add security - the logged in user should only be allowed to call stored procs.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
Christian Graus wrote:
1 - stored procs are precompiled
Some DB's cache compiled SQL, so under specific circumstances a query can appear to be just as performant as a stored proce because the execution plan has been cached.
Christian Graus wrote:
stored procs lower the amount of network traffic required to make a request
This is one area that a stored proc will always shine. However, if need be a separate server process, ie. a local server program, can replace the stored proc, but how it becomes invoked will depend upon factors built into that process. But I'm of the mind that says, rules carved in stone just require a bigger hammer. :)
Chris Meech I am Canadian. [heard in a local bar] I agree with you that my argument is useless. [Red Stateler] Hey, I am part of a special bread, we are called smart people [Captain See Sharp] The zen of the soapbox is hard to attain...[Jörgen Sigvardsson] I wish I could remember what it was like to only have a short term memory.[David Kentley]
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Miszou wrote:
Any thoughts?
I mostly think that your supervisor's views (no pun intended) are insane. If you change DB's and all the database coding is in the code itself, you'll most likely end up needing to change the code as well. However, the DB isn't the best place for abstraction either. What you really need is a layer of abstraction between the DB and your app that defines the interface for what you're doing. Then a concrete layer to handle the specifics of the target DB. It's the concrete layer that implements the abstraction that should be exchangeable to handle the quirks of a different DB.
Miszou wrote:
I was instructed to change it to two separate calls from within the code: recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )
Well, I'm no fan of hardwiring the code to a particular schema either, as changes to the schema require changes to the code. As a result, I much prefer auto-generated SQL as much as possible. I also don't like having the DB generate the ID for me. I use GUID's for primary key values and I hook the new row event and populate the ID before the DB even sees the transaction. That way, I can update the primary table and foreign tables [edit]independent of the DB even getting involved[/edit] then send all the transactions at once (and rollback also if necessary). But then again, some may say my approach is equally insane. :) So, fundamentally, if your boss is trying to make an application that is DB-agnostic, I think he has to go a lot further than declaring that all DB related stuff should be in the code itself. That'll probably make the problem worse, not better. Marc
People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith -
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
In the end anything is possible. I think that you may want to look as some type of abstraction to the process; like SubSonic at CodePlex, etc. But my spider sense would wonder if the manager knows of some kind of buyout or something. Remember what sounds good in marketing does not always work well in like. So, if he can say that the database is agnostic then that’s a selling point. My suggestion - look for something that will make your like easy and fulfills your managers demands and at the same time update your resume.
-
Stored procedures should never be used in a commercial software application. 1) Performance is not noticeably better to the end user. That used to be true a very long time ago supposedly, I don't know from personal experience because I wasn't databasing in the 70s and 80's. I can testify without a shadow of a doubt that it is no longer true for FireBird, MS SQL server or Oracle. I had a major app that was targetted exclusively at MS SQL server, about halfway through development we came to our senses and decided to target different databases, I personally have benchmarks and a lot of experience converting the app to DAL and there is no question that the end user will never see a difference in peformance either way. 2) All data access should be done through a data access layer in a properly stratified design, i.e. presentation layer, business object layer, data access layer (and usually more in between). This gurantees portability, forcing your end user into a particular database brand when it's easy to write an independant DAL for different DB's is just stupid. 3) There are many *many* things you can not do with stored procedures that you can easily do in code. 4) There is little or nothing you can do to protect your intellectual property when it's in plain sight in the database in stored procedures. 5) There is no security issue with using dynamic sql as long as you know what you are doing. I agree completely with your boss on the first part, on the second part about inserting a record adn retreiving it's unique id that's completely wrong for dynamic sql, instead generate the ID's at the application and insert the record in one trip. We use Guid's for that exactly to avoid the whole server generated identity trap.
John Cardinal wrote:
Stored procedures should never be used in a commercial software application.
How are you defining commercial software? I sell business solutions and have used SPs for donkeys years and never had a single issue.
John Cardinal wrote:
- All data access should be done through a data access layer in a properly stratified design, i.e. presentation layer, business object layer, data access layer (and usually more in between). This gurantees portability, forcing your end user into a particular database brand when it's easy to write an independant DAL for different DB's is just stupid.
That makes sense, but it doesn't preclude using SPs on the database.
Michael CP Blog [^] Development Blog [^]
-
I agree - for the same reason, no application should ever use the registry, or have a config file. I mean, those pesky end users, they can never be trusted, or educated, for that matter.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
Not sure whether or not you're being facetious, but I'll drink to that anyway. The registry is evil.
-
Stored procedures should never be used in a commercial software application. 1) Performance is not noticeably better to the end user. That used to be true a very long time ago supposedly, I don't know from personal experience because I wasn't databasing in the 70s and 80's. I can testify without a shadow of a doubt that it is no longer true for FireBird, MS SQL server or Oracle. I had a major app that was targetted exclusively at MS SQL server, about halfway through development we came to our senses and decided to target different databases, I personally have benchmarks and a lot of experience converting the app to DAL and there is no question that the end user will never see a difference in peformance either way. 2) All data access should be done through a data access layer in a properly stratified design, i.e. presentation layer, business object layer, data access layer (and usually more in between). This gurantees portability, forcing your end user into a particular database brand when it's easy to write an independant DAL for different DB's is just stupid. 3) There are many *many* things you can not do with stored procedures that you can easily do in code. 4) There is little or nothing you can do to protect your intellectual property when it's in plain sight in the database in stored procedures. 5) There is no security issue with using dynamic sql as long as you know what you are doing. I agree completely with your boss on the first part, on the second part about inserting a record adn retreiving it's unique id that's completely wrong for dynamic sql, instead generate the ID's at the application and insert the record in one trip. We use Guid's for that exactly to avoid the whole server generated identity trap.
The one reason I like stored procedures is that when you are setting up indexes for the database you can view all the sql that will be run against the database before hand. With everything in application code at the very least you have to look in code to do the same thing, but commonly you have to actually run the application and profile it to see what sql is being executed against the database. It doesn't make that much difference for small apps, but when you have large applications with dbas and developers on seperate teams, which are on a different team then the opperational dba that would be the guy to collect the profile info, it can make things much easier. In the end there isn't that much difference between using and not using stored procedures, the biggest issue is managing the flow of data across the network effectively.
I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
-
John Cardinal wrote:
Stored procedures should never be used in a commercial software application.
Wow....
John Cardinal wrote:
Performance is not noticeably better to the end user.
I guess that depends. How can you claim that it never is when 1 - stored procs are precompiled 2- stored procs lower the amount of network traffic required to make a request
John Cardinal wrote:
All data access should be done through a data access layer in a properly stratified design
Correct. And, using stored procs can help do this. How much code do you see on the web where people type SQL right into aspx files, for example ?
John Cardinal wrote:
There are many *many* things you can not do with stored procedures that you can easily do in code.
How is this possible, when a stored proc is just a bunch of SQL ? Name 3.
John Cardinal wrote:
There is little or nothing you can do to protect your intellectual property when it's in plain sight in the database in stored procedures.
If you're writing a C++ app, this is true. Assuming someone has access to your SQL Server, they can see your SQL.
John Cardinal wrote:
There is no security issue with using dynamic sql as long as you know what you are doing.
It's true that dynamic SQL doesn't guarentee security issues, but it does remove the easiest way to add security - the logged in user should only be allowed to call stored procs.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
Christian Graus wrote:
I guess that depends. How can you claim that it never is when 1 - stored procs are precompiled 2- stored procs lower the amount of network traffic required to make a request
I said *noticeably* different. If we forget entirely and conveinently the fact that execution plans are cached on most database servers now regardless of whether they came from dynamic sql or sp, we're talking maybe 10 seconds total over the entire course of day. Stored procedures do *not* reduce network traffice, well designed queries do.
Christian Graus wrote:
How is this possible, when a stored proc is just a bunch of SQL
Because you can write the sql dynamically in your code as your application is running, there are many areas you can take advantage of this when you are not fettered by set in stone stored procedures, I use it for filtering and sorting in a 3rd party datagrid that has complex filtering built into it that isn't sql friendly, I use it for very performant knowledgebase type searching, complex reporting.
Christian Graus wrote:
John Cardinal wrote: There is little or nothing you can do to protect your intellectual property when it's in plain sight in the database in stored procedures. If you're writing a C++ app, this is true. Assuming someone has access to your SQL Server, they can see your SQL.
Well my primary concern here is that I don't want my well meaning end users to go messing about with the stored procedures which they will do on their own given an easy opportunity to do so, but secondarily since my logic is primarily in my source code and it's obfuscated it's harder to get at that in the end which is all we can really do.
-
I guess anything is possible, I've never seen it be the case in practice, my apps are business apps and make *extreme* use of the power of the database engine itself and I've tested and timed and profiled with all manner of different queries and back end databases when I was researching going to a data access layer a couple of years ago and I could never find a perceptible difference between dynamic and stored procedure. Something which a lot of people said would be the case despite the common belief that stored procedures are always faster. I'm kinda intense on this issue :) and any other issue involving software development where people have beliefs that aren't validateable in the real world. I think I'm going to publish a "Heretics guide to software development" explaining why Frames are not necessarily a bad thing in web applications, why stored procedures are a bad thing in commercial application design etc etc. I can only imagine the grief people would try to give me! :)
Today's heresy is tomorrow's dogma.
-
I guess anything is possible, I've never seen it be the case in practice, my apps are business apps and make *extreme* use of the power of the database engine itself and I've tested and timed and profiled with all manner of different queries and back end databases when I was researching going to a data access layer a couple of years ago and I could never find a perceptible difference between dynamic and stored procedure. Something which a lot of people said would be the case despite the common belief that stored procedures are always faster. I'm kinda intense on this issue :) and any other issue involving software development where people have beliefs that aren't validateable in the real world. I think I'm going to publish a "Heretics guide to software development" explaining why Frames are not necessarily a bad thing in web applications, why stored procedures are a bad thing in commercial application design etc etc. I can only imagine the grief people would try to give me! :)
The attitude that Stored Procedures run quicker is a holdover from older RDBMS systems. Most decent RDBMS have very sophisticated caching mechanisms that render the differences moot. I've written responses about this in the past, and it is something that I can get quite hot under the collar about. What I will argue is that you should never render your dynamic SQL via something like:
string sql = string.Format("insert into mytable values ('{0}')", name);This opens up all sorts of security holes - but by using proper parameters then you can get past this quite nicely.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before. -
James R. Twine wrote:
Not really - we have had little utilities like strings for quite some time now. If you have enough permissions to launch the application, you have enough to dump the binary into an editor and/or get the strings out of it. Unless you encode the strings in some manner, they are in the binary in plaintext.
I can't tell you the number of clients over the years that have messed with databases that we include with our software. They see it, have a little experience and want to mess with it. Stored procedures are easily messed with by end users, dynamic sql isn't. The number one security threat for commercial software developers is protecting users from themselves, external threats are a distant second.
John Cardinal wrote:
Stored procedures are easily messed with
You've never had to code in PL/SQL from Oracle, I'd reckon. :)
Chris Meech I am Canadian. [heard in a local bar] I agree with you that my argument is useless. [Red Stateler] Hey, I am part of a special bread, we are called smart people [Captain See Sharp] The zen of the soapbox is hard to attain...[Jörgen Sigvardsson] I wish I could remember what it was like to only have a short term memory.[David Kentley]
-
I agree - for the same reason, no application should ever use the registry, or have a config file. I mean, those pesky end users, they can never be trusted, or educated, for that matter.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
When it costs you real hard dollars out of your own pocket to support software (and we never charge for support because we think it's extortion but that's another discussion) you make damn sure you cover every eventuality that might prevent the user from being able to use it productively. I can't begin to count the number of times end users have messed with databases for all manner of bizarre reasons. The single biggest security threat to most commercial software is well meaning people using it in the first place, not malicious hackers.
-
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Getting rid of the stored procedures is only one small step in preparing to write an application that runs on mulitple database systems, and isn't even an essential one. Writing a data access layer that is sufficiently abstract is what you have to do, and it's a lot of extra work if you are never going to make use of it. For your particular example recordset.open("insert data into table; select scope_identity() as userid") is the way that I'd work around that particular request. Most developers don't make enough use of batching SQL requests using stored procedures or not.
I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
-
Of course it depends, everything *depends*, it depends on how the query was written and the knowledge and experience of the developer. Knowledgeable and experienced programmers are always going to wrap queries into the fewest possible trips to the server anyway which has nothing to do with stored procedures or dynamic sql so that's a moot point. The only difference between a stored procedure and dynamic sql is compilation time of the query which is cached anyway in most rdbm's which makes the whole argument moot, but for the sake of it even assuming it isn't cached, how much difference do you think the end user of the application is going to see between one query that uses a precompiled execution plan (which may in fact be recalculated anyway) to one that doesn't? User's care about features and functionality, sacrificing either of those to save that user a total of maybe 10 seconds waiting time over the entire course of a day isn't really very smart now is it?
John Cardinal wrote:
Of course it depends, everything *depends*, it depends on how the query was written and the knowledge and experience of the developer.
Exactly - which is why you cannot make a blanket statement about how raw/dynamic SQL is better than an SP - if the wrong developer reads that things can go very, very wrong.
John Cardinal wrote:
Knowledgeable and experienced programmers are always going to wrap queries into the fewest possible trips to the server anyway which has nothing to do with stored procedures or dynamic sql so that's a moot point.
Depends on the DB access layers and/or wrappers available to the developer's involved. Believe it or not, not all of them are
Open-Batch-Batch-Batch-Closecapable.John Cardinal wrote:
The only difference between a stored procedure and dynamic sql is compilation time of the query which is cached anyway in most rdbm's which makes the whole argument moot, but for the sake of it even assuming it isn't cached, how much difference do you think the end user of the application is going to see between one query that uses a precompiled execution plan (which may in fact be recalculated anyway) to one that doesn't?
And the query plan? Too many unique queries can get your previous query knocked out of the cache, requiring recompiling, redoing the query plan, etc. And not all software development is user-centric. To a single user, taking 1/10th of a second longer for a sequence of operations may not be a problem. When you are working with 100's or 1000's of users, or with connected high-throughput systems, things are a bit different; delay always adds up add up - desktop development is very different than truly distributed systems development.
John Cardinal wrote:
User's care about features and functionality, sacrificing either of those to save that user a total of maybe 10 seconds waiting time over the entire course of a day isn't really very smart now is it?
I have never had to reduce functionality or features because of using stored procedures. I have also been able to do both - use SPs for the main functionality of the system as well as offer a "query builder" interface for creating custom queries. SPs and raw/dynam
-
John Cardinal wrote:
Stored procedures should never be used in a commercial software application.
How are you defining commercial software? I sell business solutions and have used SPs for donkeys years and never had a single issue.
John Cardinal wrote:
- All data access should be done through a data access layer in a properly stratified design, i.e. presentation layer, business object layer, data access layer (and usually more in between). This gurantees portability, forcing your end user into a particular database brand when it's easy to write an independant DAL for different DB's is just stupid.
That makes sense, but it doesn't preclude using SPs on the database.
Michael CP Blog [^] Development Blog [^]
No, to be fair I'm just so fed up with "faith based" programmers spouting so called common knowledge, taking things on faith without really trying it out for themselves to see it's not true that I was a little too harsh there, commercial software developers can and will do whatever they want to. I think it's wrong from a practical standpoint of selling and supporting software.
-
Getting rid of the stored procedures is only one small step in preparing to write an application that runs on mulitple database systems, and isn't even an essential one. Writing a data access layer that is sufficiently abstract is what you have to do, and it's a lot of extra work if you are never going to make use of it. For your particular example recordset.open("insert data into table; select scope_identity() as userid") is the way that I'd work around that particular request. Most developers don't make enough use of batching SQL requests using stored procedures or not.
I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
Andy Brummer wrote:
Writing a data access layer that is sufficiently abstract is what you have to do
5!
Andy Brummer wrote:
Most developers don't make enough use of batching SQL requests
5! Well said! Marc
People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith -
The attitude that Stored Procedures run quicker is a holdover from older RDBMS systems. Most decent RDBMS have very sophisticated caching mechanisms that render the differences moot. I've written responses about this in the past, and it is something that I can get quite hot under the collar about. What I will argue is that you should never render your dynamic SQL via something like:
string sql = string.Format("insert into mytable values ('{0}')", name);This opens up all sorts of security holes - but by using proper parameters then you can get past this quite nicely.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before. -
I've just recieved an email from my supervisor, asking me not to use any server-side functions, stored procedures, views or queries and to keep all database coding within the code itself - just in case we need to change databases or sell to a client that doesn't use the same database that we do. We write in-house web apps (classic ASP and C#) using SQL Server 2000 and have so far sold a total of zero applications to third parties (We are not a software house - just a small IT department serving the rest of the company). Pseudo-code for the offending stored procedure that prompted the new policy is shown below:
begin insert data into table select scope_identity() as userid endI was instructed to change it to two separate calls from within the code:recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )Any thoughts? I have mine, but I'd be interested in hearing from others...
The StartPage Randomizer | The Timelapse Project | A Random Web Page
Miszou wrote:
I was instructed to change it to two separate calls from within the code: recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" )
That is likely to be broken from the start in any multiuser environment. There is a very high likelihood that the value returned by the second select will not even be related to the identity value created in the first, since there is ample opportunity for another user to insert a record into the same table.
-
Stored procedures should never be used in a commercial software application. 1) Performance is not noticeably better to the end user. That used to be true a very long time ago supposedly, I don't know from personal experience because I wasn't databasing in the 70s and 80's. I can testify without a shadow of a doubt that it is no longer true for FireBird, MS SQL server or Oracle. I had a major app that was targetted exclusively at MS SQL server, about halfway through development we came to our senses and decided to target different databases, I personally have benchmarks and a lot of experience converting the app to DAL and there is no question that the end user will never see a difference in peformance either way. 2) All data access should be done through a data access layer in a properly stratified design, i.e. presentation layer, business object layer, data access layer (and usually more in between). This gurantees portability, forcing your end user into a particular database brand when it's easy to write an independant DAL for different DB's is just stupid. 3) There are many *many* things you can not do with stored procedures that you can easily do in code. 4) There is little or nothing you can do to protect your intellectual property when it's in plain sight in the database in stored procedures. 5) There is no security issue with using dynamic sql as long as you know what you are doing. I agree completely with your boss on the first part, on the second part about inserting a record adn retreiving it's unique id that's completely wrong for dynamic sql, instead generate the ID's at the application and insert the record in one trip. We use Guid's for that exactly to avoid the whole server generated identity trap.
John Cardinal wrote:
Stored procedures should never be used in a commercial software application.
I think that is a way too general statement. It's like saying that no othe language should be used except C++.
John Cardinal wrote:
there is no question that the end user will never see a difference in peformance either way.
I beg to differ. I did my own benchmarks and in many complex queries SPs performed a lot better than plain SQL through code. Talk about a difference of 6 hrs and 15 minutes in one case.
John Cardinal wrote:
forcing your end user into a particular database brand when it's easy to write an independant DAL for different DB's is just stupid.
Agreed!
John Cardinal wrote:
- There are many *many* things you can not do with stored procedures that you can easily do in code.
There are many things you can do in SPs which may be difficult in code.
Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -Brian Kernighan
-
John Cardinal wrote:
Of course it depends, everything *depends*, it depends on how the query was written and the knowledge and experience of the developer.
Exactly - which is why you cannot make a blanket statement about how raw/dynamic SQL is better than an SP - if the wrong developer reads that things can go very, very wrong.
John Cardinal wrote:
Knowledgeable and experienced programmers are always going to wrap queries into the fewest possible trips to the server anyway which has nothing to do with stored procedures or dynamic sql so that's a moot point.
Depends on the DB access layers and/or wrappers available to the developer's involved. Believe it or not, not all of them are
Open-Batch-Batch-Batch-Closecapable.John Cardinal wrote:
The only difference between a stored procedure and dynamic sql is compilation time of the query which is cached anyway in most rdbm's which makes the whole argument moot, but for the sake of it even assuming it isn't cached, how much difference do you think the end user of the application is going to see between one query that uses a precompiled execution plan (which may in fact be recalculated anyway) to one that doesn't?
And the query plan? Too many unique queries can get your previous query knocked out of the cache, requiring recompiling, redoing the query plan, etc. And not all software development is user-centric. To a single user, taking 1/10th of a second longer for a sequence of operations may not be a problem. When you are working with 100's or 1000's of users, or with connected high-throughput systems, things are a bit different; delay always adds up add up - desktop development is very different than truly distributed systems development.
John Cardinal wrote:
User's care about features and functionality, sacrificing either of those to save that user a total of maybe 10 seconds waiting time over the entire course of a day isn't really very smart now is it?
I have never had to reduce functionality or features because of using stored procedures. I have also been able to do both - use SPs for the main functionality of the system as well as offer a "query builder" interface for creating custom queries. SPs and raw/dynam