Stored Procedures - Good or Bad? 629
superid asks: "I'd like to get opinions and real world experiences that people have had with database centric applications that rely extensively on stored procedures. I believe that most enterprise class databases such as Oracle, MS-SQL, PostgreSQL, DB2 and others implement stored procedures. MySQL has been criticized for not supporting stored procedures and will be adding them in MySQL 5. The ANSI-92 SQL Standard also requires implementing some form of stored procedure (section 4.17). So, I'm asking Slashdot readers: if you were architecting a highly data-centric web based application today from a clean slate, how much (if at all) would/should stored procedures factor into your design? Where are they indispensable and where do they get in the way?"
"The arguments for stored procedures are pretty straightforward: 1) Centralized code; 2) Compiled SQL is faster; 3) Enhanced security (as our application is over 15 years old, and consists of much legacy code, reimplementation and feature creep that now includes over 3000 stored procedures). At one time we had a client/server architecture so those three advantages were relevant. However, in the past 4 years we have moved everything to web front ends and I have argued that this is no longer true. Does it really matter if my business rules are centralized in stored procedures or in a set of php/asp scripts (ie, in the web tier)? Is it really important to shave compilation time when connection and execution times dominate? (and overall response is ok anyway?) Since the focal point is the webserver, shouldn't security be done there, rather than the DB?
In addition, you either have to have a dedicated T-SQL or PL/SQL coder who then is the weak link in your coding chain, or your pool of developers must become fluent in both your scripting language of choice as well as the SP language. I have experienced both of these approaches and found this to cause bottlenecks when 'the database guy' is unavailable and learning curve problems (bugs) with new coders getting familiar with the db language.
Finally, after staying with our DB engine choice for all these years we are acknowledging that they may not be around forever. Management has asked us to look into migrating our data and business logic to another DB choice. We'd sure love to just be able to point the web tier at a new data source but that is unattainable due to a convoluted tangle of db specific code."
I don't use em unless I have to (Score:5, Insightful)
Stored Procs and triggers make can make the code simpler and more efficient, but spread out the workings of the application and unless properly documented, more difficult to understand.
Just my $0.02 CDN
Two answers. (Score:4, Insightful)
If I were a bit more of a tinfoil hat wearing man, I'd be Slashdot makes some of these "Ask Slashdot" topics up because the ensuing flamewar will cause more page hits than usual.
Re:an important question.. (Score:3, Insightful)
That, or he's preparing some kind of presentation/paper to justify the use of stored procs to a boss who doesn't believe in them (or vice versa), and is seeking real-world examples to bolster his point.
Just a couple of possibilities.
Version management (Score:2, Insightful)
I don't like using SPs, but I think version management for me is the nail in the coffin.
Mark
Good. (Score:5, Insightful)
The idea of a database is to put the whole data-relation logic in the database, if only to insure atomicity of operations.
Because as soon as you rely on an external process to maintain data integrity, you're bound to fall prey to some sloppy programmer who does not understand the data relationships and will not properly maintain the data integrity.
At least, when you use stored procedures, you can concentrate the data integrity logic in only one place, which is easier to control and manage.
Stored Procedures vs adhoc queries in apps (Score:5, Insightful)
One major problem with enterprise applications is that when a problem is found in an adhoc query (poorly written, a bug with the DBMS, performance related, etc) then the application would normally have to be recompiled and pushed out to the entire enterprise (could be tens of thousands of computers to push to). This isn't desirable.
Moving the queries into stored procedures (where possible) allows you to correct the stored procedure at a central location and roll it back to the 'old' stored procedure if necessary with minimal effort.
A good rule of thumb: use stored procedures for compiled applications
Jason L. Froebe
Re:Stored Procedures are a must (Score:4, Insightful)
I can't speak for PL/SQL but T-SQL is pretty simple to pick up and anyone who knows a 4th generation language should have no problems designing their own logic. It is probably best to have a database czar to manage the creation of objects on the database though. If the database guy is the bottleneck it's time to get a new database guy, that simple.
Re:Two answers. (Score:5, Insightful)
I guess it's a matter of perspective.
Re:Two answers. (Score:3, Insightful)
Data constraints in the database, and appliation logic in the application.
Re:Two answers. (Score:3, Insightful)
Based on the sentence preceding the one I'm quoting, I'd say the main flamebait here is your post. How do people like me, who develop corporate LAMP sites with a great deal of "real database work" going on behind the scenes, fit into your neat little conception of who is and is not a real DB developer? Asshole.
Both (Score:3, Insightful)
2) Stored procedures aren't always the fastest because you can't do array inserts with stored procedures, for instance.
3) Queries are cached. So the second time a query is executed it won't be compiled. Just make sure that your queries are parameterized. Don't put your values in the query with string concatenation. Use parameters. Otherwise queries can't be cached. You will also be vulnerable to SQL injection attacks.
4) Use stored procedures when you will gain a clear performance advantage. You may want to try to implement it in your data tier first, and if that isn't fast enough, move it to a stored procedure.
5) Buy or make a code generator that will generate data tier code for you (and possibly other code).
6) As for database compatibility, if you implement it as stored procedures, you are screwed for sure. If you use normal SQL you are probably screwed anyway. Check out this chart [mimer.com] this chart for compatibility. And that only points out the parts of these databases that follow the standard. They do have plenty of non standard features as well. If you want to try your queries for standards compliance you can go here [mimer.com].
I have plenty more where that came from, but the wife needs the computer. Good luck though.
Separation of Duties (Score:1, Insightful)
If your project is small, one programmer, then there is no need to separate duties.
Re:I don't use em unless I have to (Score:4, Insightful)
As a developer I've found otherwise. The reason being that when you're examining a bit of code with embedded SQL you often lose context of what table structures it is trying to refer to.
Of course my DBA is very good in helping out and training the developers in SP usage, so YMMV.
Stored Procedures often more harmful than helpful (Score:5, Insightful)
Prepared statements and vendor-neutral SQL are the way to go for portability and controllability of the development process. Use SPs judiciously, if at all, and only when there's a highly compelling need to do so(e.g., order of magnitude speedup, etc).
Re:What's the point of this question? (Score:5, Insightful)
Re:I don't use em unless I have to (Score:5, Insightful)
Stored procedures aren't good or bad...they just are. Passing a value judgment on whether they're good or bad is completely dependent on the situation.
I would argue that the three main points you made in favor of stored procedures are not points that apply to every case (I don't think they were intended to, either, but hear me out):
I would argue that business rules and business logic should be implemented in a vendor-independent way. Also, I would implement the business functionality of the app so that it can support a web front end, but also someday a desktop UI, a programmatic web services front end, etc. That's the soul of n-tiered architectures, they're supposed to bring that kind of flexibility along with the use of tiers...this kind of flexibility is the point.
If an app is not flexible in this way but claims to be an "n-tiered architecture", I'd argue that it is only nominally so. Looking like an n-tiered app without providing any of the benefits is a Pyrrhic victory for the architects and designers. That would be inconsequential except for the (usually large) investment of company resources.
Stored procedures == Database API (a good thing) (Score:5, Insightful)
In the past I've supported keeping more of the business logic in the database, but I no longer believe this is an optimal design. Now I keep business logic out of the database as much as possible and limit the stored code to enforcing data integrity and making the database look like a "black box" to the apps.
portability (Score:5, Insightful)
So unless you like vendor tie-in... stay away from db-specific stored procedures.
My take (Score:5, Insightful)
Retrieve the 20th page using a page size of 50 records for all the SKUs under a catalog (potentially millions total) for a specific user which could or not have visibility permissions for each SKU. Assume the security provided by the database is too coarse to fulfill the business requirements, therefore some set of rules must be evaluated to determine SKU visibility for a particular user.
That query would normally be very fast if implemented as a stored procedure because:
1) Only one round-trip is needed.
2) You don't have to move all the data to a middle-tier and then filter out information.
3) RDBMSes are usually faster at filtering data out (by using indexes, denormalization, etc.) that what a developer could code in a middle-tier to filter out information.
4) Most RDBMSes are very good at scheduling tasks, caching, managing memory, etc. The more you move logic away from it, the more you would have to implement it yourself.
You could send all the SQL statements to the database and achieve the same effect, but it might make debugging harder and you still have all the SQL logic in some place, only a different one.
On the flip side:
1) It's harder to write stored procedures than it is to write code in a managed language like Java or C# (thirty-line SELECT statements are not very intuitive).
2) Generally speaking, the compiler of a managed language does a better job at catching errors than a compiler for stored procedures, where a lot of the errors will be caught at runtime.
3) Stored procedures are not portable.
My advice is, if you are only using the RDBMS as a persistence device and your data size is not huge, avoid stored procedures and create some sort of middle-tier object model. Only when performance is a impediment, use stored procedures. You might as well use a hybrid approach, try to model as much as you can in the middle-tier and implement stored procedures for those tasks which are performance intensive.
I work with people that worship UML and patterns as well with RDBMS Gods that can plow through pages and pages of stored procedures without blinking. As much as I love ULM and patterns there are some tasks that must be done in the RDBMS for performance reasons, and tasks that are simply more maintainable when done in the middle-tier. Both approaches have advantages and disadvantages, the trick is to use the best approach according to the situation.
Advantages of stored procedures (Score:4, Insightful)
With stored procedures, I just refence the stored procedure name and leave the query tinkering to a DBA.
The only thing that I have to make changes for is when the DBA changes a column name in a table or a parameter for the stored procedure. Also when a stored procedure is in use, and it needs to be changed, I have to make the program use a second procedure name and switch procedure names each change, because if the procedure is changed as my program is running, it will break if a parameter is added or removed.
I had to work on a docket calendar program for a law firm and we used stored procedures with the reports. The managers and lawyers were always adding things to the reports which needed changes to the stored procedures. We eventually maxed out the max number of tables allowed, and each stored procedure was five pages long with if else statements because of all the things that the managers and lawyers wanted.
Using regular queries would not work because of the flexability that T-SQL had to meet the law firm's demand. MySQL would not have cut it. The reports were in Crystal Reports.
(Flame Bait) SP = bad, RDBMS = bad (Score:3, Insightful)
Personally, I think way too much stuifdf is stored in RDBMSs. I work as a Java programmer in a non-IT industry, and everyone is happy-go-lucky about making every object map to a table. But its a huge impeadance mismatch. We have layers of DTO, DAO, VO, etc. in the way.
I think the world would be a better place if most of the typical day-to-day was stored in an object-oriented, transparent database, and the relational database was left for storing things where an RBMS really shines (arbitrary relational queries, etc.).
Once you've gone the way of an OOBMS, you have objects, so naturally all of your logic stays in your objects. The fact that your objects happen to be persisted for you is irrelavent. All you car eis that you have your objects.
Re:Good or bad? (Score:5, Insightful)
It's not the query he is talking about, it's the transference of the data set from the database to the application server. This is where the data will be processed if the logic is in the app server.
Re:I don't use em unless I have to (Score:5, Insightful)
Actually, this depends on the database in question these days. SQL Server 2k does a pretty good job of keeping embedded queries hot, so the performance gain is waay less impressive than it was in, say, SQL 7.0
[cue MSSQL Bashing in 3...2....1.....]
Haven't really kept up with competing RDBMSes recently, but it wouldn't surprise me if competitors were also narrowing the gap
> Enhanced security
One incredibly common security hole being SQL Injection, I have to agree with this, but with the following caveat
I've seen developers create stored procs which do a bunch of string concatenation within the SP, then EXEC the resulting string. This is just as injection prone as doing it in a script in the first place, but the developers in question often cite SQL injection as their one of their reasons for using SPs in the first place.
Again, back to the problem with lack of knowledge on the developer's part causing security holes, rather than the platform.
Re:Good. (Score:4, Insightful)
I agree that they're good.
Aside from the virtues already espoused, implementing a database application within the database app means that as long as you can send a "SELECT ..." query to the database, you needn't re-implement your logic across multiple languages, platforms, etc.
What you get is a nice API for your data that is testable and perhaps even stable outside of the choices and requirements of the client app(s).
Good for what they're for; crap otherwise (Score:5, Insightful)
On the other hand, you should never, ever put actual application logic in a stored procedure. The reasons are several. The most important is that stored procedure languages are all, to a greater or lesser extent, crap. This comment will cause me to be flamed to death by those who only know PL/SQL etc, but the fact is it's true. They are not general-purpose programming languages.
Sure, you might not RIGHT NOW want to fork off sendmail from your application, but some day you might. Or, horror of horrors, maybe you'd like to write directly to a system file? Or use a neat SNMP library you found? Although there are twisted, hacker-like ways to do these things in most DBMs they are hardly the model of reliability or professionalism. [1]
Secondly, they tie you in at a fundamental level to a particular database vendor. Database software is generally neither Free nor free. They want you to put your business logic in their stored procedure language because it will only run in their database products. Lock in is bad. OK, you'll be locked in whatever you do, but I'd rather be locked into Java or Python than PL/SQL.
Thirdly, you are losing control of your application's performance. You have very little control over how the code will be optimised or run.
Fourthly, you are breaking abstraction. It is very, very hard to write stored procedures which aren't entirely dominated by the structure of the underlying database.
Finally, assuming you probably will have to have a middle layer between the client and the database anyway, it's a bad idea from a maintainability point of view to bits of the same functionality among your layers.
[1] have you ever written a cron job to run a query to dump a table to a file to be parsed by a Perl script to send an email? You might be an Oracle Portal user.
Re:Good or bad? (Score:3, Insightful)
What kind of shop are you working in? 2.5k is HUGE? Try 500+ users all hitting a database with queries and reports that average 100-200k or more. And that's With highly optimized queries and stored procedures.
If your using a web server or some other application server, you could have them connected via a separate backplane and generally not have any ill effects on the network at large. If, however, you are using some sort of client side front end(Either application or something linke Crystal Reports) then it is definately better to offload the processing to the db server.
Easy to learn isn't an argument for or against a technology. That aside, relatively portable is logically equivalent to completely fucking unportable when translated from Marketing into English.
That may be true for a lot of technolgies, but I've been working with SQL(DB2, Oracle, MS, MySQL, Postgres) for 15 years and with a few exceptions it is a very portable language. I worked on it on OS/390's, HP/UX, Solaries, Linux and Windows.
Stored Procedures vs. Not (Score:3, Insightful)
First, every business has different needs. Every software development group is also different in what they can or cannot provide. There are camps on both sides- many people in the database discipline will say "put everything in the database" while hard code developers will sometimes opt for queries in code.
Some considerations:
1) Consider the needs of your application. Is there a good chance your application will need to talk to another database platform or backend at some point in the future? This could be an argument for not using stored procedures. AS far as centralizing business logic goes, that can be done in just about any tier.
2) Where is your current bottleneck? How possible will it be to scale out your database server? If you are in a web farm scenario, your database server may be under significant load. Putting more logic on the database server can be a lot more expensive- it is typcially a lot cheaper to sacrifice performance on the backend for scalability. In other words, if you can keep your database server relatively load-free you can always add more web servers. I currently support a site that has over 2000 concurrent users at any given time, and currently our DB is the biggest bottleneck. It is a lot cheaper to cluster web servers than DB servers, since the DB is centralized and web servers can be duplicated easily.
3) Consider the experience of your staff and the culture of your IT department. If you have a lot of developers/dba's that are used to programming with stored procedures, and management is used to that paradigm, it may be difficult to change architectures without a compelling reason. "If it ain't broke don't fix it".
I'm sure there are other considerations, but those are probably the most three important ones I can think of right now.
Re:I don't use em unless I have to (Score:5, Insightful)
Re:Good or bad? (Score:2, Insightful)
Erm. . . you must be dealing with some mighty tiny databases, tables, fields, and queries, if you think 2500 characters is a huge query. In this day and age, where more and more data is being stored in databases, 2500 characters is nothing.
Or, are you refering to the SQL query itself, as opposed to the result data it returns? Sure, a query might only be 2500 characters, but remember, often the data can be processed locally and reduced prior to transfer, and *that* can have a huge impact.
Depending on your application and requirements, I've seen situations where the query has to return multiple megabytes of data, which are then processed by the application into a usable format.
I'm personally familiar with a situation where a DBA was able to move that processing from the application to the database in the form of a stored procedure, reducing the bandwidth from an average of 6MB to about 4KB.
Oh, yeah, and the time it took for the database to process it directly was about 8% of the time required to transfer all of the data to the application, and then process it there.
Re:I don't use em unless I have to (Score:3, Insightful)
Not only does this make the main logic more readable, it allows you to modify the SQL implementation without touching a lot of code.
For instance if you split one table with a column with a few large pieces of data that are repeated many times into two tables, one with a reference to a key for the large data pieces. Or if you add an index that makes a different SELECT statement faster than the current.
Re:I don't use em unless I have to (Score:4, Insightful)
Re:I don't use em unless I have to (Score:2, Insightful)
1) Develop Business Logic Tier (Jboss like tier). Any ever do this? Yup. Lot's of effort.
2) Convert all SQL to standard SQL (hmmm much easier to start this way. This ain't easy either)
3) Test againts all major DBs
Hmmm sounds like a lot of work. So class.. the lesson is:
Do you want to have an OPEN application that is platform independant? Do you want a product that can participate in the enterprise? Do you want to avoid the DB / OS religion wars?
Great then seperate your logic from your data from presentation tiers.. it's simple design and I've made a lot of money off of other people's lazy code
(On the other hand if you have a small app that no one out side your department will use, break out the MS Access. Funny story, I know a federal government agency that has 1200 (yes you read that correctly) Access dbs in production... BTW.. they fear centralization... surprise...
Why stored procedures are bad. (Score:3, Insightful)
1) Stored procedures are not written in an object oriented language and are almost always not written in an object oriented way.
2) Stored procedures are not checked into a version control engine.
3) There is no sane way to organize them beyond manimg tricks. No breaking up your stuff using directories for example.
4) No global compilation. No way to check ahead of time whether you just broke another SP by passing a string instead of a number in as a parameter. You won't know that till it runs.
5) No unit testing frameworks.
6) No cohesive way to examine code flow. What you end up with is a mountain of code snippets scattered all across your database. Cross your fingers and hope each step gets excuted properly.
7) No real debugger. No stepping through the code, no breakpoints, no watches.
8) Most commercial databases charge you per CPU. This means your CPU cycles are best used to keep data integrity, process queries and return recordsets. Most middle tiers are not licensed on a per CPU basis so you can afford to throw a lot of CPU cycles into executing code.
9) Last but not least you can couple your middle tier using a high speed interlink so there is no real need to use SPs.
Feel free to add to the list. SPs are not good.
Re:I don't use em unless I have to (Score:5, Insightful)
SQL injection is a very large problem in the enterprise. Stored procedures facilitate better safety. Yes, you can test for all the characters in your code but as soon as your app and password are compromised their is open reign on your database if you gave it data reader, data writer, dbo or heaven forbid SA. All new applications are build on stored procedures not only for data modfication but also SELECT queries. The application itself has no rights to the underlying tables. This ensures if indeed the application was ever compromised the most anyone could do is what the application does today. Also, they would have to figure out the XML strings to manipulate the data with a stored procedure.
Re:Good. (Score:3, Insightful)
This doesn't make sense. At my last job, I built what's essentially an ERP system. We did order capture and processing. An order was basically the following:
* billing address
* shipping address
* list of one or more line items
* list of one or more payment methods
* detail on how much each method pays
[more stuff]
OK, make me a stored procedure that can take this kind of structure all at once and do all the appropriate inserts/updates.
I had a design that made it quite easy to add new concepts to the order object and store, retrieve, and update it atomically without any one person having to know about all of the elements that might make up an order.
Re:Stored Procedures often more harmful than helpf (Score:3, Insightful)
When was the last time you saw the CPU sustain a high load on your database server? Database machines are constrained by I/O (disk and network) and memory, not by CPU in most cases. Therefore, your 8-way SQL box is sitting at 10% utilization. Why not get a bit more use out of it?
Portability for the sake of portability is a waste of time. More importantly, given that every vendor has non-standard extensions, and the SQL definitions (SQL-92, SQL-99) don't go far enough, you'll find that you almost always need to use some vendor-specific features, whether you're writing ad-hoc queries or stored procedures. At least with stored procedures, you only have to change it in one place when you migrate, rather than changing it all over your code. Tell me, how do you add an AUTOINCREMENT/IDENTITY/auto-numbering column to a table in vendor-neutral SQL?
Says you. In my team, all of our stored procedures are stored in the same code tree as everything else, controlled by the same source control. We take it even a step further, and check in all of our database object generation code (tables, keys, indexes, triggers, etc). So, just because you don't track your stored procedures in a source control system doesn't mean it can't be done.
You're assuming that the speed benefit from stored procedures typically comes from the fact that they're compiled. While that's true, it's also not much of a benefit (you might win 50-100ms per query, yay!). No, what's more important is that your queries are centralized, so that you can optimize them more easily. What's easier to optimize: A single query to <insert operation here> in a stored procedure, with the procedure called by 100 different methods; or 100 different queries to <insert operation here> across all of your code? Yeah, sure, you can centralize that as well, but what's stopping a developer from writing his own query rather than using the optimized one you've already provided for him? With stored procedures, you can deny access to individual tables and force that person to use your stored procedure.
Business Logic In Stored Procedures (Score:3, Insightful)
My tendency with data intensive applications is to put all of the business logic in Oracle stored procedures. I then have a variety of front end applications accessing the stored procedures. When the integrity of the database is the main concern of the application, I might write all of the business logic in a Java, PHP or C++ layer, hoping that no-one dinks with the data.
The big advantage of putting all of the business logic in the PL/SQL layer is that it helps make a very clean separation between the different tiers of the application.
Re:An example of good (Score:5, Insightful)
I've always wondered about the real speed difference between MySQL and PostgreSQL. You said that you were able to take out a lot of code because PostgreSQL was to do more fo the checks for you, and the stored procedures handled the remaining high-level details. Back in the day, the MySQL developers publically trashed the idea of transactions, instead recommending people emulate similar functionality in their client-side code. And that they did.
Now, consider two similar DB-based applications. One is connecting to MySQL with all sorts of client-side code to emulate transactions and referential integrity (foreign keys, etc). The other is connecting to PostgreSQL where most of the work is done in the DB engine. Foreign keys are used, as are transactions and stored procedures. Thus the number of app->db requests will be far fewer than the client-heavy MySQL app. Now, even though the MySQL server may beat the PostgreSQL server in raw speed from simple selects, how would that change when you factor in the extra work that the MySQL app has to do? And what about more complex queries? I've found that PostgreSQL can handle complex queries (sub-selects, unions, aggregrates, etc) very well. It's much more efficient to do a single big multi-row query than lots of single-row queries. It probably takes longer for the programmer to write the query, but once that's done the optimizer gobbles it up and produces a plan that gives you all your data at once. Very nice.
I'd bet that all or most of the MySQL/PostgreSQL benchmarks don't take into account the very different ways these two DB's are used in real apps. MySQL is traditionally used as a simple data store with an SQL interface. Whereas PostgreSQL can become a more intergral part of the application, with stored procedures, triggers, foreign keys, etc.
Stored procedures not complied, but separation def (Score:4, Insightful)
As far as intermixing these code bases, your procedural business logic and data business logic should be split when it makes sense. The database is optimized for merging and managing sets of data, and procedural code is good for binding this to a functional form. The business logic should be split into these two zones and implemented appropriately. It would be inappropriate to return a set from a database then loop through that set searching for some name or value. And at the same time it would be unwise to return two sets and join them in your code. With experience it seams cleaner to maintain these two zones of code. This doesn't mean that you need to use stored procedures though.
As far as stored procedures, they are a convenient way of separating these two types of languages, another way is to in place the Sql code into your procedural code, but it seams advisable to centralize this type of code in one place for visibility, and manageability. If stored procedures are not available or undesirable, then using classes or function that are located in some central, or locatable place, is recommended.
As far as for speed, implementing the data and logic in the appropriate place will speed your application, but stored procedures will not in there own right speed anything up. At least in MsSql server, stored procedures are not precompiled. They exist as plain text, just like issued queries. They do however get their own query cache, separate for the issued query cache, which could be of a little assistance.
Anyways. I am over talking about this. Take it, as u wants it.
-- Grimace1975
Re:Life without them... (Score:3, Insightful)
Well, that's exactly my situation, and I've always avoided Stored Procs because if the query is written in the cf, then I can see the actual sql being passed to the server in the cf debug info while the page is being executed - and if there's a problem with it it's pretty easy to find.
When we use stored procs, I don't have that luxury - i just get the sql server error number and description, but not the query itself. Worse, if there's a problem that doesn't result in a db error (wrong data inserted, e.g.), I don't have any feedback at all.
If I knew a way around that one little problem, i'd move to stored procedures immediately.
Stored procedures are for data integrity... (Score:3, Insightful)
Data Model Abstraction (Score:3, Insightful)
Re:I don't use em unless I have to (Score:3, Insightful)
My experience has usually been "get the damn thing working" by doing all of the sql in the application.
Then, when things are looking good, customer is happy, you're happy...you can start moving to stored procedures.
Some people might feel that this is wrong, because it should 'be done right the first time'. But usually the reality is that it just has to get done SOON. And if I can sit there and dink around with some queries while I've got someone on the phone who is hitting 'refresh' to see the changes, then I am going to do it in the application where I have better access.
In my experience, which is not the be-all and end-all, but is 6 years or real-world day to day web app programming...(judge that as you will) 90% of the queries are total 'junk' anyway.
I don't really see the need to put things like 'select * from story where storynum=956' in a stored procedure. Looking through most of my code, I would guess that at least 90% of my queries are similar to that.
I've got other routines that do things like aggregate hundreds of thousands of pieces of data- in that case I use stored procedures because they make the difference between 6 minutes and 60 minutes. (but then again, if I were a real genius, maybe it would only take 6 seconds...but then my server might catch on fire or something) but most of my work on the web is pretty straight-forward.
On the other hand, I used to work with a guy who was always 'trying to save cycles'. Like he didn't want the server to have to work too hard. He would spend days trying to cut something down from 500 milliseconds, to 20 milliseconds. Yes, I agree that is all well and good (depending on the frequency of the query...in the 500/20 example, we're talking about a DAILY routine). But we've upgraded servers twice since that time, and that 500 millisecond query IS a 20 millisecond query now.
Not to mention the fact that the server sits at 1% of processor capacity all the time. I hate to say it, but we're living in a fairly hardware-rich environment. Most (I said most) people don't need to optimize the crap out of something, because as the original story said, users are waiting on their connection- not my database access.
Okay- it all sounds like me making excuses for being a lazy coder. But man, you should see all of my pretty comments!
Last thing...where I work, we pretty much upgrade hardware based on TIME, not on NEED- it is one of those 'spend it or lose it' situations. So, saying that if I had optimized the code, I wouldn't have needed to upgrade is a moot point- we would have upgraded anyway.
Re:Don't Like Them... (Score:3, Insightful)
WHY isnt it as easy to add more database servers then it is to add more web servers?
It's becouse you're not looking at the applications from the side of someone who manages data and databases.
It can work both ways. And in reality, it should work both ways..
Ever seen a database having rows locked by 100 different web servers becouse they DIDN'T use any form of stored procedures?
Re:Two answers. (Score:2, Insightful)
Re:Good for what they're for; crap otherwise (Score:3, Insightful)
You know nothing about database-backed application development. I would recommend that you find some people who know what they are talking about and learn from them. Find an old Oracle or DB2 DBA or developer and talk to him. You know nothing about what a database is.
Thirdly, you are losing control of your application's performance. You have very little control over how the code will be optimised or run.
You've never used a database, obviously. I'll argue that there are zero high level programming languages that allow the granulaity of control that a real RDBMS allows.
Lock in is bad. OK, you'll be locked in whatever you do, but I'd rather be locked into Java or Python than PL/SQL.
You've never worked for a real company, have you? Front end app languages change more often than the weather, and in one company, many are usually used at the same time. One department uses VB, another uses Oracle Forms, another uses Java, another uses Perl. Companies don't change databases willy-nilly.
Seriously, I think that you should probably work with some real database people before you try building any apps on your own. You have a tremendous amount to learn.
Re:I don't use em unless I have to (Score:2, Insightful)
Re:Good or bad? (Score:4, Insightful)
- more complex constraints than foreign keys and check constraints
- enforcement of denormalizations
- enforcement of data movement (eg to a decision support system, archive, system interface, etc...)
Your statement that business logic should go in higher layers doesn't rule out SPs, because these are a reasonable choice to BE one of the higher layers.
In some cases, "business logic" can be configured as data in tables. In almost all cases it generally depends on business process state strored in tables. Depending on the amount of data required to make the business logic decision, it is sometimes superior to implement this as a stored procedure. Consider something that loops over all rows in a large data set, and makes a decision about each row. In a stored procedure, you might be rate limited by the speed of memory access and CPU. For the middle layer to implement it, you may have to pipe the large data set over the network, which might be orders of magnitude slower.
My experience is that overall system performance is usually a very strong driver of implementation strategy here. There is no reason SPs can't be just as maintainable as any other middle tier code. The only technical difference is that they run on the most centralized shared resource, but they have much faster data access. So it's a scalability for speed tradeoff.
In fact, often stored procedures are MORE maintainable, because reality in a heterogeneous enterprise environment is that no matter what you pick for your middle layer, somebody you have to integrate with won't be able to deal with it. Your spiffy java API for checking whether a series of transactions is properly ordered isn't callable from their COM object or vice versa. What ends up happening is you end up reimplementing solutions in multiple places, which is fine until the business rules change. Contrast this with the fact that just about every language supports a database connectivity that allows calling stored procudures. So their perl code logs into your database and calls your API -- easy.
Re:Life without them... (Score:3, Insightful)
Re:Why stored procedures are bad. (Score:3, Insightful)
I'd add:
10) Stored procedure languages are usually kludgy and encourage bad programming practices.
11) Applications written in two or more languages are usually a significant maintenance headache. Not only do you require experts in all the languages but the interfaces themselves can be problematic. This is particularly true for applications that store significant state both in and out of the database. Synchronisation can be a major issue.
I use stored procedures when I have to, usually only because I need to reduce the size of a retrieved dataset before it hits the network and a simple SELECT won't do. Other than that I avoid stored procedures like the plague.
DBA's in the organisations I've worked in have generally had an unrealistic view of their importance and technical abilities. They get away with a lot more than they should mainly because they are gatekeeper to the organisation's "crown jewels", the data, and because database management is a mess that most developers try to avoid.
I've found from a practical standpoint it's best to keep the business logic out of the database and treat the database as a simple data store. Your mileage may vary.
---
It's wrong that an intellectual property creator should not be rewarded for their work.
It's equally wrong that an IP creator should be rewarded too many times for the one piece of work, for exactly the same reasons.
Reform IP law and stop the M$/RIAA abuse.
security in depth (Score:2, Insightful)
Security should be done in every layer of the system. If you only did security on the web server, if crackers are able to compromise it, getting into the database is simple at that point. But if they can only run stored procedures, they then need to find a hole in the database which halves the likelihood of getting in.
Of course there are all the costs of maintaining the extra level of security. So you have to consider the cost/benefit of the security you put in place.
Braindead assertion... (Score:2, Insightful)
"[snip]At one time we had a client/server architecture so those three advantages were relevant. However, in the past 4 years we have moved everything to web front ends and I have argued that this is no longer true. Does it really matter if my business rules are centralized in stored procedures or in a set of php/asp scripts (ie, in the web tier)?[snip]"
You've argued this because you've only been around for a we fraction of time and have never watched technology change from one generation to the next. If you stick the logic in the database, what is the cost of developing a new platform or changing web technologies? What's the cost of change if you don't put your logic in the database? In five years, are you going to assume your PHP code is a tomb of business process logic that can be trusted because its logic has been segmented and acts independently from data presentation hacks or frufy one-off hack jobs?
"[snip]Since the focal point is the webserver, shouldn't security be done there, rather than the DB?[/snip]"
Web software is a moving target. For every platform, web program, service, etc., you have to enforce the same set of rules. Stored proceedures are to databases what functions are to programming languages. Use them or your business will suffer from inconsistencies ranging from data management to more serious problems such as data access and theft. I bet companies that process credit cards or people who work on medical records probably have strong and justified opinions on the topic of data security... think the cost of implementing consistent database proceedures is bigger or smaller than the cost from a screw-up that lets the data walk out the front door?
You mention MySQL in your post... use a real database such as PostgreSQL for two months and let me know how your opinion on databases has changed at the end of your trial run. Compare and contrast this experience with your experience stemming from application development with MySQL. If you think MySQL in its current state is still a valid core for an organization at the end of that duration, you obviously suffer from some form of brain damage.
Re:Good for what they're for; crap otherwise (Score:2, Insightful)
Say, you may not get out much, but there are other ways to build an app besides on top of an SQL server. You'll note that Doom 3, Microsoft Word, and the Google search engine don't use them. You'll concede that those are all real apps produced by real companies, right?
I'll argue that there are zero high level programming languages that allow the granulaity of control that a real RDBMS allows.
A little reminder here: databases aren't made from magic pixie dust; they're written in other languages. An RDBMS can't be more granular than the language it's written in, it can only be less. Indeed, it probably should be less, as its purpose is to make development simpler, easier.
The only question here is when it's more cost-effective to use an RDBMS on a given project. Back before DBMSes existed, people wrote a lot of low-level code to get things done. But eventually people pulled some of that code into common libraries for managing records, and the DBMS was born. If you're doing the kinds of things that Oracle expects in a way that Oracle expects, using an Oracle server may save you a lot of money and development time.
But if you're Google, an off-the-shelf DBMS is a big waste of time. Oracle's a great general-purpose engine, but Google engineers don't give a damn about the general case. They are building an utterly specific application; the generality of a database server mainly gets in their way.
So ease off on the "If you don't use a database you must be an ignorant fool," crap, ok? The parent poster was clearly inflamatory, but he describes one perfectly reasonable way to build successful applications. His isn't the only way, and neither is yours.
Stored Procedures Solve a Non-Existent Problem (Score:3, Insightful)
In a three-tier or web architecture, stored procedures have no place. Centralize your business logic in business objects on the server. This makes your application independent of any underlying table structure or persistence mechanism. You can get the speed of stored procs by using prepared SQL in your database mapping code. These days, you can use mapping tools like JDO to avoid any database mapping code.
Where stored procs still have a place is inadministrative functionality, such as background batch processing. That's it.
Re:Good for what they're for; crap otherwise (Score:2, Insightful)
Notice that I said app development with a database back end. I know that not every app uses a database. Not by a long shot.
A little reminder here: databases aren't made from magic pixie dust; they're written in other languages. An RDBMS can't be more granular than the language it's written in, it can only be less. Indeed, it probably should be less, as its purpose is to make development simpler, easier.
Oh, I understand that, but Oracle & DB2 & other "real" RDBMS' aren't written in high level laguages. With Oracle, at least (my experience), you can control queries and other processes down to the memory paging level. You can't control where *exactly* you put memory with C++ or Java. They have pointers, granted, but that's nothing compared to the fine tuning you can do in Oracle (which I've only *seen* done... I was a developer, not a DBA).
Re:I don't use em unless I have to (Score:2, Insightful)
I guess the lesson learned was to review the design by DBAs before implementing, and test under load before going in to production.
Re:I don't use em unless I have to (Score:3, Insightful)
The great thing about using parameters though is that not only do they help to secure you against SQL injection attacks, they actually reduce the amount of work you need to do, too. For instance, suppose you have to put a date/time in the database. Using straight SQL this is a bit of a pain - you need to make sure you use the right format for the database etc. With parameters, it just drops out. Same goes for null values etc.
Re:Good for what they're for; crap otherwise (Score:4, Insightful)
You're right, that's why JVMs don't have garbage collection or strong typing, because JVMs are written in C, and C doesn't.
Oh, wait. It looks like software can have features that its host language doesn't. How 'bout that?