No more stored procedures
-
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
Stored procedures [can] improve performance. Every major database supports them, though the syntax may differ. The notion that you will sell your stuff to a third party is a really dumb reason not to optimize your environment. Surely such a sale would include optimizing the app for the customer's setup. In fact, why would anyone be interested in buying a product that was intentionally hamstrung? (There are reasons not to overuse stored procedures; in some cases you can create bottlenecks at the server.) Edit: I added 'can' since as I had pointed out here and John Cardinal observed, this is by no means a given. And, as I eluded to in the previous paragraph, I've seen situations where stored procedures caused serious performance problems.
Anyone who thinks he has a better idea of what's good for people than people do is a swine. - P.J. O'Rourke
-
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
If at all
Miszou wrote:
just in case we need to change databases or sell to a client that doesn't use the same database that we do.
Miszou wrote:
select @@identity
Good Luck making that work with Oracle. I have a product that works on both Oracle and SQL Server (it is C++) but the concepts apply. I have a few stored procedures written for both databases and I also run queries from code. Most of the complex stuff such as parent child relationships and tree lookup code is written as stored procedure in my application. This way the C++ code is simple. The problem is not so much of stored procedure as it is of the queries being compatible across the databases. In this case even though you moved the query to code it is still not compatible with Oracle. In one way stored procedures help because all you need to do is to write a new stored proc for the new database and keep the compiled code the same. The other thing about stored procs is that people assume that they perform better, but it may or may not be the case always. I have seen people converting even simple select/insert queries to stored procs but in all my tests parameterized queries perform as fast as the stored procs.
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
-
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
bye bye optimisations ...
Regards Ray "Je Suis Mort De Rire" Blogging @ Keratoconus Watch
-
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
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.
-
Like David said... Not only does it have the possibility of reducing performance over stored procedures, you have the chance for something else to insert into the table in-between the two SQL calls you have to insert and then to get back the identity value.
@@SCOPE_IDENTITYmight help in this case... Peace!-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!)James R. Twine wrote:
Not only does it have the possibility of reducing performance over stored procedures
Ancient fallacy! If you're talking microseconds, once per query then you're right, if you're talking about something noticeable to the end user, that's complete hogwash.
James R. Twine wrote:
you have the chance for something else to insert into the table in-between the two SQL calls you have to insert and then to get back the identity value.
Yeah, that's just plain wrong, when you go dynamic sql you generate your own unique identifiers in code. (Guid's rock for this)
-
Stored procedures [can] improve performance. Every major database supports them, though the syntax may differ. The notion that you will sell your stuff to a third party is a really dumb reason not to optimize your environment. Surely such a sale would include optimizing the app for the customer's setup. In fact, why would anyone be interested in buying a product that was intentionally hamstrung? (There are reasons not to overuse stored procedures; in some cases you can create bottlenecks at the server.) Edit: I added 'can' since as I had pointed out here and John Cardinal observed, this is by no means a given. And, as I eluded to in the previous paragraph, I've seen situations where stored procedures caused serious performance problems.
Anyone who thinks he has a better idea of what's good for people than people do is a swine. - P.J. O'Rourke
-
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
First thought - which other database does the idiot think @@identity belongs to? Oracle - oh wait, no it doesn't. (I really need a sarcastic smiley). Second, why not look at something like an OR/M or DevExpress Persistent Objects? Third - two separate calls to the DB? That's really good for performance.
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. -
Joe Woodbury wrote:
Stored procedures improve performance
To who? Not the end user, that's been proven time and again so often that I'm stunned to see some people here still contributing to that fallacy.
After I posted that I realized I should have written "stored procedures CAN improve performance" since I actually know of cases where they cause lower performance (which I eluded to in a later sentence.) (I modified my original post with a note indicated that I had done so.)
Anyone who thinks he has a better idea of what's good for people than people do is a swine. - P.J. O'Rourke
-
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
It seems very shortsighted from the supervisor. It kills any chance to leverage the given database system's optimization capabilities, and will increase the complexity of your C# code (unnecessarily, in my opinion). Also consider the security implications - with database-specific code to insert or delete records created as stored procedures in the database itself, you can limit the security privileges of the ASP.NET user account that connects to the database. Instead of giving that user account privileges to modify and delete data directly in each underlying table (a very wide surface area for hackers to exploit), you can give that user account only EXECUTE permission on the specific stored procedures with no direct access to the underlying tables (a much more narrow surface area). If the supervisor is planning on future distributions to other database systems, I think this is a more intelligent approach: "Let's focus on designing the application to use a data tier and object tier, so we can swap out the data tier in the future in case we want to support other database systems."
-
After I posted that I realized I should have written "stored procedures CAN improve performance" since I actually know of cases where they cause lower performance (which I eluded to in a later sentence.) (I modified my original post with a note indicated that I had done so.)
Anyone who thinks he has a better idea of what's good for people than people do is a swine. - P.J. O'Rourke
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! :)
-
James R. Twine wrote:
Not only does it have the possibility of reducing performance over stored procedures
Ancient fallacy! If you're talking microseconds, once per query then you're right, if you're talking about something noticeable to the end user, that's complete hogwash.
James R. Twine wrote:
you have the chance for something else to insert into the table in-between the two SQL calls you have to insert and then to get back the identity value.
Yeah, that's just plain wrong, when you go dynamic sql you generate your own unique identifiers in code. (Guid's rock for this)
John Cardinal wrote:
Ancient fallacy! If you're talking microseconds, once per query then you're right, if you're talking about something noticeable to the end user, that's complete hogwash.
That depends entirely on how often the query is being executed, the type of query, etc. The multiple round-trips also take unnecessary time. Even microseconds can add up. I would rather be the application that can show a benchmark of 10231 records/sec instead of a competing application that does 10074 records/sec, or consumes 25% less of the CPU time and requires less memory than the other one. Performance matters - you are not the only application on a target system competing for system resources, nor the only user of the database. Peace!
-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!) -
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.
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
-
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.
Hear hear! I've had stored procedures "disappear" from the database (SQL Server); one minute it's there, next minutes it's gone, and the whole system fails.
-
Hear hear! I've had stored procedures "disappear" from the database (SQL Server); one minute it's there, next minutes it's gone, and the whole system fails.
That's never happened to anyone I know, sounds like an issue on your end.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
John Cardinal wrote:
Ancient fallacy! If you're talking microseconds, once per query then you're right, if you're talking about something noticeable to the end user, that's complete hogwash.
That depends entirely on how often the query is being executed, the type of query, etc. The multiple round-trips also take unnecessary time. Even microseconds can add up. I would rather be the application that can show a benchmark of 10231 records/sec instead of a competing application that does 10074 records/sec, or consumes 25% less of the CPU time and requires less memory than the other one. Performance matters - you are not the only application on a target system competing for system resources, nor the only user of the database. Peace!
-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!)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:
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:
If you're writing a C++ app, this is true. Assuming someone has access to your SQL Server, they can see your SQL.
I was going to reply to the OP, but... Not really - we have had little utilities like
stringsfor 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. Another reason for having the SP - just because a user can execute an SP via an application does not necessarily mean that they have permission to get directly to the database and executesp_helptext. Peace!-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!) -
That's never happened to anyone I know, sounds like an issue on your end.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
Christian Graus wrote:
If you're writing a C++ app, this is true. Assuming someone has access to your SQL Server, they can see your SQL.
I was going to reply to the OP, but... Not really - we have had little utilities like
stringsfor 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. Another reason for having the SP - just because a user can execute an SP via an application does not necessarily mean that they have permission to get directly to the database and executesp_helptext. Peace!-=- James
If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!)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.
-
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]