No more stored procedures
-
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
-
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:
If it means more features for end users, easier to maintain code and easier to support application?
The one and only time I worked on a project that relied on dynamic SQL it turned out to be the most difficult, unreadable, unmaintainable pile of festering crap I ever had the misfortune to have to work with.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
Your business model is flawed. I never charge for support, either. But, support ends when people mess with the program, simple as that. Can I pull my car to pieces, then call for warranty support because it's broken ? No.
Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
Christian Graus wrote:
Can I pull my car to pieces, then call for warranty support because it's broken ? No.
That is an excellent analogy - I'll have to remember that.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
You can do anything if you know enough, as long as people can't modify my queries, break the program and then come for my support to fix it I don't care what they do.
John Cardinal wrote:
as long as people can't modify my queries, break the program and then come for my support to fix it I don't care what they do.
Okay - so they go in to query analyzer and mess with the data structure and your application fails. Might as well have used stored procedures because its just as messy.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
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:
Woops! That's not something I would be willing to admit
Why not? I think it is great to be able to say I made a mistake and I fixed it and this was the result. It shows willingness to learn.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
James R. Twine wrote:
There is no reason for saying flat out that all SPs suck and should be avoided.
I disagree...it leads to a hopefully, enlightening discussion! ;)
Touche!
-=- 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!) -
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
Yes, I agree with you if you want to go into that level of detail.:)
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
-
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.
- Already beat to death in other replies 2) At minimum, better to have a provider type system if you wish to work with multiple database venders so that you can optimize for each as time permits or usage demands. Additionally, now that we have CLR on the server, it opens up even more doors for optimizing and features. Just a few simple things such as being able to access RegEx server side it pretty handy! 3) Silly... 4) You cannot protect them anyway if you the end user has access to the Server. Anyway can run a profiler on the the transactions and monitor anything going to or from the server. The end result is that you have to distribute new versions of your application every time you have to make a small change to any query. I cannot begin to say how many times a simple tweak to a SP fixed problems or improved performance without all the hassle of producing yet another version of the client applications. SP have become popular, not because someone said to use them, they save time and money when you use them properly and you make sure the server is locked.
Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...
-
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 SmithCurious, since there is a chance howbeit slight, that a GUID is already used, don't you have to add code to make sure that never happens, such as checking if the GUID is already used? Also, isn't it a lot of overhead in the indexes for the larger data of a GUID compared to a int? :: This reply was suppose to be to Marc.. 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). CP really needs to get this bug fixed.. ::
Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...
-
Miszou wrote:
Any thoughts?
Find a new job :-D
only two letters away from being an asset
Now there is an answer! :)
Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...
-
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
Throwing the baby out with the bath water is not a good thing. At this point it appears the product is already optimized for SQL Server and doing all this extra work just negates all the work that was already done along with possibly harming performance. Not to mention most of the SQL is problably not compatible between the databases anyway. If they want to move to support other servers, it is far better to build in a provider model and build out a diffrent provider for each DB. That way you can optimize for each database using all the power of each instead of a the lowest common denominator. This would also allow you to use CLR on SQL Server in the future if you have the need which can even add more abilities. I would go to the boss and push for a simple plug-in type architecture instead of hard coding SQL into the applciation. Every time you need to change one thing in your SQL (even for simply optimizing), you have to release yet another version of your application causing even more work.
Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...
-
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 -
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
-
Curious, since there is a chance howbeit slight, that a GUID is already used, don't you have to add code to make sure that never happens, such as checking if the GUID is already used? Also, isn't it a lot of overhead in the indexes for the larger data of a GUID compared to a int? :: This reply was suppose to be to Marc.. 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). CP really needs to get this bug fixed.. ::
Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...
Rocky Moore wrote:
don't you have to add code to make sure that never happens, such as checking if the GUID is already used?
I don't. I really don't expect it to be an issue. Besides the probability of a GUID being identical, there's also the probability that it will be identical in the same table. It seems extremely remote.
Rocky Moore wrote:
Also, isn't it a lot of overhead in the indexes for the larger data of a GUID compared to a int?
There's a lot of debate[^] on the subject. (And a lot of links you can read up on). Someone published some code somewhere that helps with clustering, but I'd rather not touch it. The thing I like about GUID's is it makes it easy to merge offline changes. And I have to deal with that occasionally. 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
That's probably the most asinine thing I've heard. I've only read about half the thread here, and the focus appears to be on the performance benefits, or lack there-of, of stored procedures. I'll give you good reason to USE stored procedures -- data consistancy. The database applications I write all use stored procedures to keep the data consistant, not for performance reasons. The database maintains itself without any outside C# or C++ or VB.NET code. If you make a change in a table, triggers, functions, stored procs, and whatnot maintain the integrity of the data. Data can be validated and massaged before the changes are commited. The database takes care of itself without any outside influence. The database can pretty much run without a coded user interface. If there is something that needs to change in the database, like a correction to some data, you can do it directly using SQL and the database still maintains itself.
Dave Kreskowiak Microsoft MVP - Visual Basic
-
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
You could write an abstraction layer for data access and have the UI or Business layer use the abstraction layer. The abstraction layer would provide an interface and your actual implementation would use stored procedures. Why still use stored procedures? I have worked with Oracle, MySql, Access (Jet), and Sql Server and all of them have a different SQL syntax. BTW, @@identity is SQL Server only.
On two occasions I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. - Charles Babbage
-
Rocky Moore wrote:
don't you have to add code to make sure that never happens, such as checking if the GUID is already used?
I don't. I really don't expect it to be an issue. Besides the probability of a GUID being identical, there's also the probability that it will be identical in the same table. It seems extremely remote.
Rocky Moore wrote:
Also, isn't it a lot of overhead in the indexes for the larger data of a GUID compared to a int?
There's a lot of debate[^] on the subject. (And a lot of links you can read up on). Someone published some code somewhere that helps with clustering, but I'd rather not touch it. The thing I like about GUID's is it makes it easy to merge offline changes. And I have to deal with that occasionally. 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 SmithMarc Clifton wrote:
I don't. I really don't expect it to be an issue. Besides the probability of a GUID being identical, there's also the probability that it will be identical in the same table. It seems extremely remote.
Yeah, I sure hope mission critical apps think about it ;) Of course, it is not just "that" table as most databases have some form of relationship where they are used as keys. In a large application, they would have to be unique to maybe 20-100 tables. If used in an enterprise, that could be combined over different branch offices to thousands of tables. Not to mention that have no order so you end up using another field to force order. Little things like this bug me so bad, I would have to make extra queries just in case :) Thanks for the thoughts!
Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...
-
Marc Clifton wrote:
I don't. I really don't expect it to be an issue. Besides the probability of a GUID being identical, there's also the probability that it will be identical in the same table. It seems extremely remote.
Yeah, I sure hope mission critical apps think about it ;) Of course, it is not just "that" table as most databases have some form of relationship where they are used as keys. In a large application, they would have to be unique to maybe 20-100 tables. If used in an enterprise, that could be combined over different branch offices to thousands of tables. Not to mention that have no order so you end up using another field to force order. Little things like this bug me so bad, I would have to make extra queries just in case :) Thanks for the thoughts!
Rocky <>< Latest Code Blog Post: SQL Server Express Warnings & Tips Latest Tech Blog Post: USA City Burnt To Death...
Rocky Moore wrote:
Little things like this bug me so bad, I would have to make extra queries just in case
If you consider the bug rate of even an exceptional developer working under an exceptional development process, the probability of the extra queries causing errors is probably more likely then getting a guid collision. :laugh:
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