No more stored procedures
-
Pete O`Hanlon wrote:
What I will argue is that you should never render your dynamic SQL via something
Yeah, I'm assuming for the purposes of this discussion that goes without saying. :)
John Cardinal wrote:
Yeah, I'm assuming for the purposes of this discussion that goes without saying
What scares me is the number of people who aren't aware of this. If they were, then Colin would be out of a job:-D
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. -
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
Rama Krishna Vavilala wrote:
I think that is a way too general statement. It's like saying that no othe language should be used except C++.
Yeah it's hyperbole I agree, I just wanted to get the juices flowing in this discussion! ;)
Rama Krishna Vavilala wrote:
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.
Woops! That's not something I would be willing to admit, there is no difference other than compilation time and the quality of the query written in the first place.
-
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:
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.
BTW I don't think transferring everything to code solves the problem either. I have used tools such as SQL Profiler/ODBC Tracing to reverse engineer many applications to find out why theye were not working or why the behavior was not a expected.
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:
Stored procedures do *not* reduce network traffice, well designed queries do.
So, you're saying that 20 lines of SQL generate as much network traffic as a proc name ?
John Cardinal wrote:
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.
In other words, you're saying that you run some SQL, then do some stuff in code, then run some SQL ? That's not an apples for apples comparison at all. You can turn those SQL snippets into procs and get teh same effect.
John Cardinal wrote:
which they will do on their own given an easy opportunity to do so
I've never had a problem with this, but every app I've deployed, the SQL Server instance was secure from the rank and file, and the people who had access understood that their warranty expired if they changed code themselves.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
Christian Graus wrote:
So, you're saying that 20 lines of SQL generate as much network traffic as a proc name ?
:rolleyes: So what? If it means more features for end users, easier to maintain code and easier to support application?
Christian Graus wrote:
and the people who had access understood that their warranty expired if they changed code themselves.
Again... :rolleyes: My experience is with thousands of users all over the globe that get free support from us, slightly different perhaps.
-
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:
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.
That makes sense, I was kinda puzzled when I read the original post, moving queries from stored procedures to code is of no use if you don't have the infrastructure in your code to support multiple databases. Personally I think you should sell the database software WITH your product. Your manager should have thought of that when he made this decision, because this way he can make profit on both the product and the database software instead of just the product. Also your better able to support the product, as you don't have to train people of the support department in all of the database products your customers use, instead you have to train them in just one.
Andy Brummer wrote:
Most developers don't make enough use of batching SQL requests using stored procedures or not.
Gotta love SQL Server. I tried to batch SQL commands in Access and it didn't work. It was one of the reasons for me to move to microsoft SQL server for even the products that were used on a single computer.
WM. What about weapons of mass-construction? "You can always try to smash it with a wrench to fix that. It might actually work" - WillemM
-
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?
Find a new job :-D
only two letters away from being an asset
-
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! :)
All of our database code is in stored procedures. :~ We're using Oracle, and we have some pretty substantial stuff - a handful of our stored procedures exceed 500 lines of code. In at least one instance, we're building our query string dynamically inside the stored procedure, and then executing it to ultimately retrieve the desired record set. That particular function is freakin' huge.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001 -
Rama Krishna Vavilala wrote:
I think that is a way too general statement. It's like saying that no othe language should be used except C++.
Yeah it's hyperbole I agree, I just wanted to get the juices flowing in this discussion! ;)
Rama Krishna Vavilala wrote:
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.
Woops! That's not something I would be willing to admit, there is no difference other than compilation time and the quality of the query written in the first place.
John Cardinal wrote:
there is no difference other than compilation time and the quality of the query written in the first place.
There is a third thing: data transfer from the DBMS process to the actual client process and converting them to language specific data types such as variants. In the case I am referring there were two million records which have to be all scanned due to some business requirements.
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
-
Christian Graus wrote:
So, you're saying that 20 lines of SQL generate as much network traffic as a proc name ?
:rolleyes: So what? If it means more features for end users, easier to maintain code and easier to support application?
Christian Graus wrote:
and the people who had access understood that their warranty expired if they changed code themselves.
Again... :rolleyes: My experience is with thousands of users all over the globe that get free support from us, slightly different perhaps.
John Cardinal wrote:
So what?
So, I made the point, you disputed it, now you're backing down ?
John Cardinal wrote:
If it means more features for end users
It can't possibly mean that
John Cardinal wrote:
easier to maintain code
It absolutely cannot mean that, it opens the door to hideous to maintain code, although it doesn't guarentee it, and I doubt it's the case in your apps
John Cardinal wrote:
easier to support application
Only in the sense that you seem to be selling to morons and you let them walk all over you.
John Cardinal wrote:
My experience is with thousands of users all over the globe that get free support from us, slightly different perhaps.
It's true that the apps I've deployed have been local ( at least, the ones that use SQL Server, I have users all over the world with an app that doesn't use SQL ). But, I don't see how that changes the basic principle that most users should not have access to the SQL Server, and the people that do, should know that they lose their warranty if they change it. If you go in and fix a problem due to people changing procs, it gets charged at a premium rate. Do that once, and their management will make sure it doesn't happen again.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
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?
Does your boss have pointy hair?
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. -
That's the dumbest thing I've ever heard.
CodeProject: 'I mean where else would you rather be pissed off?' - Jeremy Falcon
David Stone wrote:
That's the dumbest thing I've ever heard.
I agree.
"That's no moon, it's a space station." - Obi-wan Kenobi
-
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 have mine, but I'd be interested in hearing from others...
Run away!!!
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
John Cardinal wrote:
there is no difference other than compilation time and the quality of the query written in the first place.
There is a third thing: data transfer from the DBMS process to the actual client process and converting them to language specific data types such as variants. In the case I am referring there were two million records which have to be all scanned due to some business requirements.
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
-
Rob Graham wrote:
That is likely to be broken from the start in any multiuser environment.
Not true: From docs: @@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session.
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
Rama Krishna Vavilala wrote:
Not true: From docs: @@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session.
recordset.open( "insert data into table" ) ... recordset.open( "select @@identity" ) As long as the first open() doesn't close the connection after executing its statement, or the second open() doesn't close/open a new connection before executing its statement, then the current session may be equal to current connection. Even then you would have to use SCOPE_IDENTITY() not @@identity (the insert may fire a trigger which executes another insert - different scope). I believe few are willing to take the chance on this (session == connection) always being true across all RDBMS', let alone that their recordset class maintains a connection behind the scenes.
...cmk Save the whales - collect the whole set
-
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:
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 read the entry for CREATE PROCEDURE[^] you will see that one of the options is to encrypt it.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
John Cardinal wrote:
So what?
So, I made the point, you disputed it, now you're backing down ?
John Cardinal wrote:
If it means more features for end users
It can't possibly mean that
John Cardinal wrote:
easier to maintain code
It absolutely cannot mean that, it opens the door to hideous to maintain code, although it doesn't guarentee it, and I doubt it's the case in your apps
John Cardinal wrote:
easier to support application
Only in the sense that you seem to be selling to morons and you let them walk all over you.
John Cardinal wrote:
My experience is with thousands of users all over the globe that get free support from us, slightly different perhaps.
It's true that the apps I've deployed have been local ( at least, the ones that use SQL Server, I have users all over the world with an app that doesn't use SQL ). But, I don't see how that changes the basic principle that most users should not have access to the SQL Server, and the people that do, should know that they lose their warranty if they change it. If you go in and fix a problem due to people changing procs, it gets charged at a premium rate. Do that once, and their management will make sure it doesn't happen again.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
I'll concede that dynamic sql could in some cases result in more traffic outgoing to the db server, considering the ratio of outgoing to incoming data I'm also sure you are willing to concede that this is pretty much irrelevant in the 21st century that most of us are working in now. ;)
Christian Graus wrote:
But, I don't see how that changes the basic principle that most users should not have access to the SQL Server,
Well they shouldn't and you're right we're under no obligation to fix it if they break it...technically...but if you have thousands of users and even a small percentage do this then it's worthwhile avoiding the potential for the problem in the first place. Even if it's a case where you charge them to fix it, the time spent just figuring out that the problem is a result of someone changing something and the effort required just to prove that to their boss against their employees denials is something else again. You seem to take things very literally and down to the millisecond where I'm speaking more in generalitites here. Of course a stored procedure with a pre-compiled execution plan run once will be technically faster than the dynamic sql with no cached execution plan, but the differences are so miniscule in reality that if there is even one more feature you can add to the program that is populare and useful but requires dynamic sql then you'd be crazy not to use it. In this day and age people want choices, choices of database platform is a big one and you can never go out of business giving the customers what they want.
-
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 by end users, dynamic sql isn't.
You can always fire up the Query Analyser and mess with it instead. :rolleyes:
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
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 read the entry for CREATE PROCEDURE[^] you will see that one of the options is to encrypt it.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
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.
John Cardinal wrote:
Stored procedures do *not* reduce network traffice, well designed queries do.
And having a query manipulate data inside a stored procedure then send back the final answer is much better than having lots of data being sent back-and-forth across the network so some client app can do what could be done in a stored procedure.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
John Cardinal wrote:
Stored procedures are easily messed with by end users, dynamic sql isn't.
You can always fire up the Query Analyser and mess with it instead. :rolleyes:
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos