Beta

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Stored Procedures - Good or Bad?

Cliff posted about 10 years ago | from the applying-the-proper-code-fu dept.

Data Storage 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."

cancel ×

629 comments

Sorry! There are no comments related to the filter you selected.

FP (-1, Offtopic)

Anonymous Coward | about 10 years ago | (#9849095)

FP

SP (-1, Offtopic)

Anonymous Coward | about 10 years ago | (#9849099)

SP

I don't use em unless I have to (5, Insightful)

ph4rmb0y (711836) | about 10 years ago | (#9849108)

I like to keep business logic in one place as much as possible. You are almost assured to have some in your app, so I try to keep all logic there.

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

Re:I don't use em unless I have to (4, Insightful)

nz_mincemeat (192600) | about 10 years ago | (#9849237)

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.

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.

Re:I don't use em unless I have to (2, Informative)

flngroovy (8003) | about 10 years ago | (#9849298)

There are many reasons to use them. Performance is always one of the first reasons that people give. When we have applications that run in our enterprise, it is very easy to update a stored procedure if you find a problem. Your clients do not need to update their software when this happens. Personally, I rarely put SQL commands in my code. I leave it all in the procedures on the server. SQL 2005 will allow the use of C# in stored procedures which should increase their usefulness. In a team environment it is easy to have someone work on the procedures while you work on the code. (I know this can be done with interfaces and classes, but this is also very convenient)

Good or bad? (4, Informative)

Anonymous Coward | about 10 years ago | (#9849109)

Bad, of course.

I only put triggers or constraints or whatever in the database for one reason: to make sure only valid data enters the database.

For instance if ColumnA must be between 5 and ColumnB+34, that should go in the database. The database itself should guarantee the data is clean. "Data logic" you could call it.

Business logic and everything else should go in the higher layers. There is some ambiguity about what is "data logic" and what is "Business logic" but it's usually pretty clear.

Why? Maintanence. The stored procedures tend to rust in place over time. If you're an "agile" developer you'll go nuts not being able to refactor business logic or have unit tests check your database procedures. If you're a "BDUF" (big design up front) shop, you might like it, but thankfully many are moving away from that.

Re:Good or bad? (1)

jfroebe (10351) | about 10 years ago | (#9849166)

for small number of applications you would have a point but if you are dealing with thousands of machines in an enterprise environment, your argument falls apart. Keeping the changes in a central location with the ability to 'roll back' any of the changes quickly is the goal.

Re:Good or bad? (1)

smallfeet (609452) | about 10 years ago | (#9849243)

That's were an application server would come in. If it were J2EE you would put this logic in the EJBs. Not putting business logic in the DB makes it easier to maintain and also makes it possible to change the DB if needed (though THAT never happens).

A previous post mentioned 'data logic' in the DB. I thinks that is a good way to think about it, limit it to data integrity.

Re:Good or bad? (2, Informative)

saberworks (267163) | about 10 years ago | (#9849189)

I totally disagree. Stored Procedures have a lot of benefits.

Speed

Code Reuse - if everyone uses stored procedures, it's less likely that your developers will just write another (duplicate?) query when they don't want to go mucking around in all your php/asp classes/functions.

Bandwidth between the database server and your web server will be reduced - instead of passing huge queries across the link, you send a simple stored procedure call.

Plus, PL/SQL is really easy to use and learn and is relatively portable across at least PostgreSQL and Oracle.

They also provide a good facility for logging or tracking that can be (should be!) completely transparent to the web application.

Re:Good or bad? (1)

Miniluv (165290) | about 10 years ago | (#9849247)

Bandwidth between the database server and your web server will be reduced - instead of passing huge queries across the link, you send a simple stored procedure call.
Don't be assinine. A modern production environment is running at least 100MBit Ethernet, if not 1GBit. A HUGE query, say 2500 characters, is 2.5KBytes. Gee, thats pretty rough.

Plus, PL/SQL is really easy to use and learn and is relatively portable across at least PostgreSQL and Oracle.
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.

an important question.. (-1, Flamebait)

js3 (319268) | about 10 years ago | (#9849114)

why are you designing a database if you don't know these things? you will only give your coworkers headaches

Re:an important question.. (2, Funny)

firebus (49468) | about 10 years ago | (#9849124)

why are you posting if you don't have an answer?
you are only giving me a headache.

Re:an important question.. (2, Insightful)

DaveJay (133437) | about 10 years ago | (#9849144)

A bit trollish, but I'll respond: perhaps he's actually smart enough to seek outside opinions, even though he thinks he knows the answer.

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.

Re:an important question.. (1)

treke (62626) | about 10 years ago | (#9849208)

Fairly simple for someone like me. Over time peoples jobs change, and in a small shop you might have to take up tasks that you previously would not have done. The logical thing to do is try and learn from other peoples experience since you aren't likely to have much in the specific topic. Slashdot probably isn't the best place to go, but you have to give him credit for seeing a hole in his knowledge and trying to compensate rather than just blindly proceeding forward.

What's the point of this question? (1)

joshuao3 (776721) | about 10 years ago | (#9849115)

There are no real negatives to stored procedures from a development standpoint. Databases that support them, assuming cost is not an issue, will always be prefered over those that do not. Why is this question even being asked on an open forum such as this?

Re:What's the point of this question? (1)

bwalling (195998) | about 10 years ago | (#9849154)

There are no real negatives to stored procedures from a development standpoint.

Other than the fact that they suck, there are no negatives! Seriously though, the database is for data, other languages are for business logic. Why split your business logic between your class library and your database server? Keep the logic in one place. Isn't that why you created the class library in the first place?

Re:What's the point of this question? (1)

heller (4484) | about 10 years ago | (#9849201)

...the database is for data...

Ah yes. so transactions certainly don't belong in a database. Nor do constraints. And definately no sorting! Or reporting. We must remove all aggregation functions because that's logic and not data!

Please. There is always logic that applies to data specifically. If there wasn't, there would be no real reason to have a seperate database. ** Martin

Stored Procedures are a must (5, Interesting)

Anonymous Coward | about 10 years ago | (#9849116)

Particularly for an application where you are returning large amounts of data, stored procedures hold a distinct advantage over dynamic SQL queries in that, if the SP is designed correctly, the database has pre-optimized the query plan at compile-time and runtime execution is therefore much faster. It also allows for underlying table structures to change without impacting your application logic.

Also, when it comes to long-term database maintainability, putting your database logic in stored procedures allows the db admins to get an accurate overview of what objects/tables are in use and which are no longer needed. At my company, where we have over 20 databases, this is an absolute must.

Generally speaking, I use dynamic SQL during initial development and move to stored procs for QA and production.

Re:Stored Procedures are a must (0)

Anonymous Coward | about 10 years ago | (#9849139)

(((It also allows for underlying table structures to change without impacting your application logic.)))

What about views?

Re:Stored Procedures are a must (4, Interesting)

Bedouin X (254404) | about 10 years ago | (#9849197)

I may be incorrect but views are still not as fast as SPs as SPs are compiled code. Also, you can't really pass arguments to views unless you're using dynamic SQL, which brings you back to square 1.

Re:Stored Procedures are a must (3, Insightful)

Bedouin X (254404) | about 10 years ago | (#9849163)

SPs can also make switching web development platforms pretty easy if all of your special functions and dynamic queries are already coded.

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:Stored Procedures are a must (1)

complexmath (449417) | about 10 years ago | (#9849202)

Very well said. The DB applications I've written use stored procedures exclusively for these very reasons. If a DB does not support stored procs then it isn't even a candidate.

That said, views do have the same performance advantages, but don't have quite the same degree of encapsulation. If I had to I'd use views but they still aren't as effective as stored procs.

When did Slashdot switch to IIS? (-1, Troll)

Anonymous Coward | about 10 years ago | (#9849117)

All these 5xx errors are annoying. Go back to Apache.

Re:When did Slashdot switch to IIS? (0, Offtopic)

earthforce_1 (454968) | about 10 years ago | (#9849142)

I know this is OT, but there is nowhere else to post this - I thought I was the only one seeing them. Is it the viruses running rampant, or did something break after their last time they tweaked slashcode?

Re:When did Slashdot switch to IIS? (1)

isbhod (556556) | about 10 years ago | (#9849183)

not sure about IIS, but slashdot has been running a lot slower than normal for me too. Again sorry for the off topic post.

Re:When did Slashdot switch to IIS? (1)

rbbs (665028) | about 10 years ago | (#9849188)

I've just spent the last 10 minutes trying to get through and this is the first time i've succeeded. It's been going on for about a fortnight now at least. I'm getting maybe a 50% success rate on average....

Heh enterprise database (-1)

Anonymous Coward | about 10 years ago | (#9849119)

Since when was MySQL an enterprise database?

I love it and use it on small projects but its far from enterprise, sonny ;)

SPs are good (2, Funny)

TommyTyker (605166) | about 10 years ago | (#9849122)

I actually miss them in my current job with mySQL. I used to like the way you could run a stored proc every X period to copy "live" data to "public" areas. Or, archive "public" data after "x" period of time. But then again, I am a micro$oft developer at heart, and all this Perl, CGI, Java, (even COBOL) on old RS6000 systems gives me a headache sometimes.

SPs are GOOOD (1, Funny)

Anonymous Coward | about 10 years ago | (#9849126)

Data controlled by the DBA good. Data controlled by developers or users... bad, very, very bad.

good (1)

menix (179754) | about 10 years ago | (#9849128)

personally I like them. The centralization of code is a big advantage, but I think the biggest advantage is typing the incoming data. This plugs some of the security holes.

My favorite SQL (1, Funny)

Anonymous Coward | about 10 years ago | (#9849129)

SELECT * FROM users WHERE CLUE = TRUE
0 Rows Returned

or if there are rows, fix it quick!

DELETE
FROM users
WHERE CLUE=TRUE

Postgres supports (2, Informative)

Sir_Real (179104) | about 10 years ago | (#9849136)

One of my favorite thing about postgres is it's support of plpythonu (python stored procedures) and the recently added java support.

Just define the function (in java or python) and SELECT it with whatever arguments you've designed it for. I don't know what the overhead involved is, but we used it more for convenience than anything else.

I was told basically, "the fewer the better" and "keep them confined to the innermost loops."

Two answers. (4, Insightful)

MisterFancypants (615129) | about 10 years ago | (#9849138)

You're going to get two answers. You'll get the "always used stored procedures" answer from people who actually do real database work and the "never use stored procedures" from the people who hack small websites in PHP.

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:Two answers. (5, Insightful)

Anonymous Coward | about 10 years ago | (#9849164)

That's funny, I was going to make the comment but instead of "people who actually do real database work" I was going to say "people who think it's still 1980 and who think changing software should take an act of congress" and instead of "people who hack small websites in PHP" I was going to say "people who use dynamic languages to deliver high-volume applications in weeks instead of months".

I guess it's a matter of perspective.

Re:Two answers. (1)

TheSpoom (715771) | about 10 years ago | (#9849185)

From the perspective of someone who, at the risk of killing my ego, is in group two, why are stored procedures a good thing? I have a feeling that my later level database design courses in college will tell me, but I'd like to know.

Re:Two answers. (4, Informative)

Bedouin X (254404) | about 10 years ago | (#9849223)

They let you abstract data access procedures. It's kind of like asking "why are functions a good thing?"

Because a person who knows what the hell they are doing can write a good function (or stored procedure) containing what the hell they know so that a person who doesn't know a hell of a lot about what the first person knows but a lot about something else on a higher level can interface with that logic and get a result without having to be a guru.

Or something like that...

Re:Two answers. (1)

silas_moeckel (234313) | about 10 years ago | (#9849263)

OK I'll aswer the question not as somebody thats in either camp just the guy that gets to scale the thing. First off I dont care out nice OO design or business logic etc etc thats all just PHB fluff. Stored procedures allow you to scale a DB if you do it well.

Pretty much it boils down to this if your web app can call one stored procedure and generate the responce your golden. The more time your spending going back and forth grabbing data and colating it just takes to long to scale as you have a bigger memory foot print blocking call sockets theads etc for a longer time. Now thats a perfect world were writing stored procedures is just as easy etc etc etc as writing your web app. Your best results are often from good coding in the rough out stage and replacing whole functions with stored procedure wrappers after profiling where your app is spending most of it's time under load.

Re:Two answers. (3, Insightful)

puppetman (131489) | about 10 years ago | (#9849215)

Actually, I'm a DBA who is responsible for about 20,000 lines of stored procedure code that I did not originally write, and I still think they are a bad idea.

Data constraints in the database, and appliation logic in the application.

Re:Two answers. (3, Insightful)

Daniel Dvorkin (106857) | about 10 years ago | (#9849217)

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.

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.

Re:Two answers. (1, Funny)

Anonymous Coward | about 10 years ago | (#9849245)

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.

Call me weird, but I like these types of flamewars. I've done a little PHP hacking with MySQL and I'm curious about the issue of stored procedures, since I've never had the opportunity to use them.

Re:Two answers. (0)

Anonymous Coward | about 10 years ago | (#9849264)

This is GOD. You are a great disappointment. Please do not procreate or as I will be forced to to flood the earth once again...

Re:Two answers - not always (1)

filesiteguy (695431) | about 10 years ago | (#9849273)

Actually I fit both molds.

I do PHP and use "static" mySQL in code blocks for my personal sites.

For my day job, I tend to use ASP/VB and perform most of my calls within massive stored procedures - often times creating multiple temp tables. Generally my databases have >10M rows in the primary online tables (not to mention some archive tables), so stored procs work well there. I use MSSQL, DB/2 and IMS interchangebly.

I tried out the stored procedrues in PHP5 and wasn't impressed. Since I'm coming up with a new online app in PHP/mySQL, we decided to go with "quasi" stored procs where we have a php page that can accept "parameters" and generate the SQL code accordingly.

Re:Two answers. (0)

Anonymous Coward | about 10 years ago | (#9849319)

"You'll get the "always used stored procedures" answer from people who actually do real database work and the "never use stored procedures" from the people who hack small websites in PHP."

Can we add "proof by claiming to work in a bigger company" to the list of stupid assertions [jyi.org] ?

Version management (2, Insightful)

Anonymous Coward | about 10 years ago | (#9849146)

What about version management of stored procedures? Yes I know it's possible, but it creates pain. Everyone must have their own copy of the DB otherwise an error by one developer modifying a SP breaks it for everyone, even if they have their own copy of the app. Scripts must be written to ensure that the latest SPs can be applied at the same time as code updates.

I don't like using SPs, but I think version management for me is the nail in the coffin.

Mark

Re:Version management (0)

Anonymous Coward | about 10 years ago | (#9849257)

It's possible through Visual Source Safe and Visual Studio, if you're a Microsoft kind of person

Re:Version management (1)

Miniluv (165290) | about 10 years ago | (#9849283)

What exactly do you think makes versioning stored procedures hard? Do you think developers go about changing SPs in a production environment with testing, committing the SP to CVS/Subversion/SourceSafe/BobsFilingCabinetOfSourc eContral? If so, please forward me the checksum of your resume so I can ensure I never hire you, work near you, or support anything you ever touched.

Migration (0)

Anonymous Coward | about 10 years ago | (#9849147)

Finally, after staying with our DB engine choice for all these years we are acknowledging that they may not be around forever.

I've never taken this argument seriously. If you put all your business logic into a web front-end, then you're locked into that web front-end. If you put all your business logic into the database, you are locked into that database. Sure, both are pains, but you can't consider it a downside for the database without also considering as an upside for the database.

Good. (4, Insightful)

Pig Hogger (10379) | about 10 years ago | (#9849148)

They're double plus good, of course.

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.

Re:Good. (1)

arkanes (521690) | about 10 years ago | (#9849258)

Well, if you're doing anything of any size (as opposed to something crapped up in PHP), you're going to want a centralized repository of functions and business logic anyway, to ensure the correctness of your DB functions. You could write that as a library in your application code. But since you're going to need it anyway, you may as well do it on the database with stored procs and get the extra layer of security and performance that comes basically for free. The only point I can see to doing it on the application side is that you can use generic (read: lowest common denominator) SQL for everything to maximise your portability. But again, if you're working with anything more than minimal datasets you're going to need the performance boost that comes with db-specific optimizations - in my experience, query overhead dwarfs connection and processing time. If it doesn't, you should probably take a real close look at your app code.

Stored Procedures are Better! (0)

Anonymous Coward | about 10 years ago | (#9849150)

Just think about this. You move from client server to web front end was made easier because your business rules were stored procedures. They were not dependant on the end application.

Life without them... (5, Interesting)

qurve (689356) | about 10 years ago | (#9849151)

...would be hell for me.

Most of the development I do at my job is Coldfusion+Fusebox with SQL Server on the backend (I don't care if you hate MS, don't bother knocking SQL Server) and stored procedures just make life easier. They're also handy in the instance that you may have multiple front-ends written in multiple languages accessing the same database in many cases. Making a change to the way data is returned is far easier to do in one stored procedure than in X number of front-ends. One of the main reasons we don't use mysql is because the stable versions don't have them.

Re:Life without them... (0)

Anonymous Coward | about 10 years ago | (#9849235)

The other main reason I don't use MySQL is because you have to pay a license to distribute your database. Hence why I use PostgreSQL. I too can't imagine doing anything more than trivial database work without using stored procedures, it can certainly save a lot of pain.

And Programming Logic, Too... (1)

BrianMarshall (704425) | about 10 years ago | (#9849313)

Making a change to the way data is returned is far easier to do in one stored procedure than in X number of front-ends.

Stored procedures can be good for programming logic that uses the database and that should be the same across multiple applications.

Stored Procedures vs adhoc queries in apps (5, Insightful)

jfroebe (10351) | about 10 years ago | (#9849152)

If the applications are written in one of the various scripting languages, then this argument doesn't apply:

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

Good and bad (1, Interesting)

Anonymous Coward | about 10 years ago | (#9849168)

The good side about them is you can put them in one place, centrally, and basically forget about them.

This means several teams can develop apps against the same database (often using completely different languages, and with completely different goals). The business logic isn't hidden in some obscure class that might be in a language you aren't fluent in.

The bad is that they tend to make the individual apps difficult to debug. sp's often throw errors that are non-obvious, and if they are buggy, will do very strange things to your data. Often, hours of debugging latter, you realize it had nothing to do with your code, but was a bug or bizarre error condition in the sp or trigger.

Lately, the databases are allowing sps triggers to be created in more modern languages. Postgres IMHO kicks normalizes everyone elses ass with it's ability to use python, perl, php and ruby (among others) to create your sp's. Then at least, it is code that is a big more understandable.

Good (1, Interesting)

Anonymous Coward | about 10 years ago | (#9849173)

I am in the "always" stored procedure camp. They allow me to centralize a great deal of business logic close to the data source.

They provide a great deal of security if used properly. Not only do I not have to allow my users to have ANY direct access to tables, but I ahve the ability to change underlying structure more easily.

It is wonderful to make the boss' suggested change to his favorite application without having him even close out of it.

The downside is that you have an additional layer of code to manage. However, I have done a lot of code automation, and 90% of my sp's can be written with a few clicks of the mouse...

Only use stored procedures when you have to... (1)

RobbieJohn (521233) | about 10 years ago | (#9849175)

There are times when there are compelling reasons to use stored procedures. Unless you have one of those situations, there are lots of good reasons to avoid them. In addition to the portability and skills issues, I'd like to add that PLSQL and TSQL are both pretty crude languages. Stored procedures also make it hard to partition your application across servers. I'm in the 'avoid them if you can' camp.

NO WAY! (0)

matth (22742) | about 10 years ago | (#9849178)

Stored procedures should ONLY be used for sensative data such as in a bank environment, where information should not be passed to the database (such as usernames/passwords/account info etc) but rather the database server should run it. Otherwise the proccessing should be done on the app side!

There is NO REASON for the database server to be doing proccesing.. it's a database server!!

Re:NO WAY! (1)

jfroebe (10351) | about 10 years ago | (#9849216)

umm.... what do you think a query is? It is a set of instructions to the DBMS of how to retrieve a desired set of data. a stored procedure has less of an impact on the DBMS than an adhoc query because it is already compiled to be used by the engine.

You have much to learn about databases and are likely trolling but I'll give you the benefit of the doubt.

Shhhh! Don't tell ASP/PHP/Perl (1)

Percy_Blakeney (542178) | about 10 years ago | (#9849271)

There is NO REASON for the database server to be doing proccesing.. it's a database server!!

Likewise, there is no reason for a web server to be doing processing... right?

Where I've used Stored Procedures: (1)

temojen (678985) | about 10 years ago | (#9849179)

If you have views set up to restrict which rows a user can select, but you don't want to restrict user creation (with a certain group, and with pre-defined rights) so that your webserver doesn't need administrative rights, a stored procedure fits the bill.

(PL/PGSQL example)

BEGIN WORK;

CREATE GROUP "salespeople";
CREATE GROUP "customers";

CREATE TABLE customerAddresses (
customername name references pg_users(usename) primary key,
address varchar(50)
)

GRANT SELECT ON customerAddresses TO GROUP "salespeople";
GRANT INSERT ON customerAddresses TO GROUP "salespeople";
GRANT UPDATE ON customerAddresses TO GROUP "salespeople";
GRANT DELETE ON customerAddresses TO GROUP "salespeople";

CREATE VIEW myAddress as select address from customerAddresses where customername = current_user;

CREATE RULE updateMyAddress as on update to myAddress do instead update customerAddresses set address = NEW.address where customerName=current_user;

CREATE RULE insertMyAddress as on insert to myAddress do instead insert into customerAddresses values (current_user, NEW.address);

GRANT INSERT ON myAddress to group "customers";
GRANT UPDATE ON myAddress to group "customers";
GRANT SELECT ON myAddress to group "customers";

COMMIT;

-- Now the problem becomes: How do un-privaledged users (ie salespeople or webserver) create all those customer accounts?
-- It's easy to solve with a little PL/pgSQL.

BEGIN WORK;

CREATE GROUP authorizedtocreatecustomers;

create or replace function makeuser (name,text) returns int4 as '
BEGIN
execute ''create user '' || quote_ident($1) || '' with password '' || quote_literal($2) || '' in group customers;'';
return 0;
END;
' language 'plpgsql' security definer;

REVOKE EXECUTE ON function makeuser (name, text) FROM PUBLIC;
grant execute on function makeuser (name,text) to group authorizedtocreatecustomers;

COMMIT;

-- Now, just make sure webserver and all your salespeople are in the group "authorizedtocreatecustomers"

The "Centralized code" argument is ridiculous (1)

kevingolding2001 (590321) | about 10 years ago | (#9849180)

I have heard this nonsense spouted more times than I care to remember. "Keep the code IN the database" the enthusiasts would cry. What you end up with is code spread all over the place. Some of your business logic now resides in source files written in your app. language, and some of it resides in the database in SP language. You can no longer look in one place to see what the program is doing. You have to read two sets of source code side by side. Throw triggers into the mix so that your SP's are silently called as a side effect of other statements and you have a maintenance nightmare. And what happens you need to roll out an upgraded version of one of your programs? You can't just transfer the binary there any more. Now suddenly every new release becomes a database upgrade, requiring scripts to be run to modify the database etc.

Re:The "Centralized code" argument is ridiculous (1)

petepac (194110) | about 10 years ago | (#9849318)

It is the ONLY way to go!!!!

You get more code spread all over the place when it's imbedded in apps that no one shares the code for. Yes, there's code reusability, yet I've seen very little of it. With stored procedures I, as a DBA, get to control and manager distribution. Most of the database developers I've seen can't write good procedures, let alone a normalized data schema. The think that it's easier to put data integrity in the app. The one that can write them well become DBAs.

If you don't want a multi-tier app, then stored procedures are the only way to go. The front end app should be the data entry, validation and presentation layer. The procedures do a second data validation and process business logic. At one shop we created a standard database with procedures and tables that all applications could use. When there were changes made to the procedures, we still presented the result set in the same way so NONE of the apps had to change.

The was another instance where we were developing an app that was constantly changed to support new user requests. By keeping the data access in the procedures, we were able to keep multiple versions of the application running using the same procedures. Try doing that in just code.

The biggest benefit is security access. Once the user gets access to the procedure, you don't need to give them access to the tables those procedures use. Keeps the power users from mucking up the data using Excel or Access.

Single view of business processes, better security and simplified modification of processes without recompiles and distribution of code. That keeps me from loosing sleep at night.

Oh, and one more thing. NO SQL INJECTION HACKS!!!

Middle Server (1)

Cranx (456394) | about 10 years ago | (#9849187)

I almost always use a middle layered server that buffers calls between applications and the database. It's job is really to coordinate transactions, users and to perform tasks that go faster natively than through a database. A middle tier invariably makes it FAR simpler switching from one database engine to another than trying to port stored procedures.

All business logic in the app? (0)

Anonymous Coward | about 10 years ago | (#9849191)

Then you are a moron.

SP not good all the time (1)

TechGladiator (741686) | about 10 years ago | (#9849195)

At my work we write apps that are only used inhouse and app that are used by 1000+ users. Our applications are mostly data entry apps with a decent amount of reporting. We only use stored procedures in the software that we sell to our users because it makes it easier to implement any updates/fixes in code than it's in a stored procedure and just send them a program(executable) update. For inhouse we prefer stored procedures because maintenance/updatability is not a problem.

I think if you are looking at large databases where stored procedures may make a huge difference weigh your options between speed and ease of sending updates to the users.

Just my 0.02 cents.

Don't Like Them... (1, Redundant)

puppetman (131489) | about 10 years ago | (#9849196)

Stored Procedures are hidden logic, and they're difficult to write, debug and maintain. In some databases, they are nearly impossible to debug without the equivilent of printf statements.

You also place more burden on the database, and lose the ability to scale horizontally. It's easier to add more web/app servers to you site than it is to add more databases.

Not sure I agree with the point about them being tied to a specific database; I don't think it's practical to write vendor-neutral SQL, so you will always have functions like TO_DATE vs DATE_FORMAT to worry about.

Use foriegn keys, check constraints and strongly typed columns to preserve your data. Keep the application logic outside your database.

Depends (2, Funny)

Billobob (532161) | about 10 years ago | (#9849198)

If it can magically make 503 errors go away - yes if it does anything else - no

Is it that big a deal? (2)

Miniluv (165290) | about 10 years ago | (#9849199)

Stored procedures that execute the sme query as one sent over the normal client process usually execute faster. The DB server knows more about the query and the table structure, and therefor can optimize it better.
Having the queries in one place is also generally an advantage, and if the vast majority (or entirety) of your queries are in those stored procedures then migrating from one DB to another means NO messing with DB specific code (and every query ends up being DB specific if it does much of anything at all) except for the query developers.
The shop I work in has two main applications which access the same database. One is a web environment written entirely in Perl where all of the DB logic has been pushed into stored procedures, and then further abstracted into modules. Now migrating from one DB engine to another simply means rewriting the stored procedures from PL/SQL to TransactSQL (for example), and some minor modifications to the underlying modules. Then if we want to change the business rules for that data we can change the modules with only minor changes to the app logic.
Contrast this with a mega app written in C which has tons of queries directly in it, a minimum of stored procedures, and a constant stream of bugs because of the morass this has created. The app moves slowly, ponderously, and half the time wrongly.

Anytime I'd be called upon to architect something, I'd be pushing stored procs as much as possible. They're a logical extension of good, modular design.

depends on your user interface (1)

stonebeat.org (562495) | about 10 years ago | (#9849203)

I started with Oracle Programming, then moved to Objectivity [objectivity.com] for Pure OO DBs, and now I am using mySQL. Stored procedures are very useful, if your application runs in a console mode, from the server that is hosting the DB. But in real-life this is not usually the case. Most applications are web based, in which case the web server is the middle tier. The latency added by the middle tier and the whole HTML/XML is so much that using stored procedure vs C++ doesn't help too much. Nowadays I use Java/PHP code to process the data, instead of PL/SQL, since it is the middle tier that is the bottleneck, and not the fact that I m processing data on the middle tier instead of on the DB server.
With all advances in the client (browser) technology I am even doing some processing (mostly for presentation) on the client using applets, and XML/CSS.

I would use them for their intended purpose. (1)

paenguin (311404) | about 10 years ago | (#9849209)

Stored procedures can be used to isolate field names from the application that calls them. This encapsulation allows you to change data column names without regard for the application that is calling them. The calling application uses the stored procedure to retrieve and store data, and the stored procedure knows how to translate the data column names.

They also allow you to put business rules and other data centric items at the database where they operate.

For example, let's say you have a trigger on a data row that causes a formula to run. Wouldn't it be best to keep that formula with the data it operates on so that it can run rapidly without the need for data to go over the wire several times?

Stored procedures are also a great place to store a complex set of queries so that the presentation to the outside world is more directly understandable. You might have data that is difficult to represent certain views, but a stored procedure can bridge that gap without adding complexity to the calling program.

All of this is more important when you are talking about a web application where there is not a lot of processing power, state is difficult to maintain and lag times can stretch processing times if the data travels around too much.

Stored procedures can solve all of these problems (and many more) neatly and at the server.

My experience (1)

yellowstone (62484) | about 10 years ago | (#9849212)

2) Compiled SQL is faster;
Usually this fact will trump any other consideration, especially in the minds of those who only have to deal with how fast (or slow) the application actually runs under load.

The main reason I've found not to use stored procedures is in cases (like complex searches) where a dynamically generated SELECT statement will beat any sort of attempt at a generic stored procedure search hands down.

Some time ago, I did some work on a middle tier for some PHBs who got infected with the "stored procedures == faster" meme in a big way. The big sticking point with this was the complex searches they wanted to support, which could be done very quickly and relatively simply with a dynamic SELECT, but became very slow, complex (and thus bug prone) when we attempted a stored procedure implementation. I'm not sure we (the development team) ever really convinced them doing it dynmaically was the right way to go; we just wore them down to the point where they finally dropped the issue.

(PS, to any PHBs who think I might be talking about you: if you keep quite, no one will ever know)

modeling/modules (0)

Anonymous Coward | about 10 years ago | (#9849214)

it's necessary the db is in charge of retrieving desired data. that leaves processing and presentation. in most cases, app will do the presentation, and likely the presentation requires degree of processing at the least. given that, should you split the processing between db and app? where do you draw the line? while sp can be faster (assuming it deals with data stored in db), sp language is typically not as flexible/robust as general purpose programming language, and the designer loses more control due to the use of query optimizer (i think a lot more so than compiler optimizer).

i think sp as way to better define "what" (data) to get is good, but not for actual processing - it seems to amount to optimization hack (and that is when using query optimizier well).

oh, the trigger is the absolutely worst. you thought you were updating a single column, and then whole table get dropped. no one needs that sorta aggravation.

Both (2, Insightful)

3770 (560838) | about 10 years ago | (#9849218)

1) I'd use both and I wouldn't use security as an argument to use stored procedures. Mere "mortals" should not have access to the database server at all. Just beware of SQL injection attacks (Google it if you don't know what that is).

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.

It should be easier than it is... (1)

a_karbon_devel_005 (733886) | about 10 years ago | (#9849220)

There are many promising things about stored procedures, but they are negated by the very thing that should make SQL work strong in many situations.

Supposedly, storing all business logic in the database gives you speed and the ability to change the language used for rendering and UI fairly quickly if need be. In that sense they become similar to the CLR... I can write a PyGTK+ app quickly for a little desktop gui functionality, and use the same validation/business subroutines that the main PHP interface uses. This is certainly sounding like a plus, yes?

However, once you start coding in stored procedures, you're just as married to the database as you would be to any other language. The problem is that while there is generally excellent acceptance of SQL 92 standards, and some decent acceptance of SQL 99 standards, afaik stored procedural languages are not nearly as standarized. Hell, PostgreSQL has like 5! So instead of being married to, say, asp, you're now married to a database, which in most cases is commercial.

Also, I think the point about new coders being unfamiliar with the programming language is slightly invalid... PostgreSQL and Oracle at least have Java (or Java-esque) PL's built in. Pretty familiar for most coders worth their salt.

Encapsulation and balancing of tasks (1)

Kell_pt (789485) | about 10 years ago | (#9849221)

Stored procedures, imho, are essential for data integrity. It's all fine and dandy when you have just a web app sitting on top of the database, but when multiple applications access the database, sometimes from different sources and programmers, you have to make sure they all follow at least the same base set of rules.
Stored procedures centralize a degree of data validation and integrity. They are also for on-modify creation of data. For instance, one can use stored procedures to make sure some read only table is always up-to-date, by making it so that inserts/updates are mirrored to that table. Some could argue that this can destroy normalization, but in some situations where reply time is critical, normalization can do more harm then good, and sometimes you're better off with more data than with having to join two tables. Stored procedures can be of great value here.
Do web applications need stored procedures? Probably most won't. But then again, "web app" doesn't define an application, it just defines an interface, a media.

Stored procedures are also vital in one of the most important things regarding project development: abstraction. You can customize the database layer to execute code that is implicit to database operations, thus saving time by not having to make them explicit in the application layer AND also saving a roundtrip from the app to the db for each of those explicit calls turned implicit - hope this made sense.

Cheers.

security (0)

Anonymous Coward | about 10 years ago | (#9849224)

Stored Procedures -or- parameterized queries can prevent SQL injection attacks.

Dynamic SQL is more more prone to logical errors. Dynamic SQL often involves string manipulation, which can be a performance hit (depends upon your implementation--and helper classes can improve or prevent this--but in general appending strings and variables together inside of code is a little nasty).

Also, consider using strongly typed datasets if you can. Strongly typed datasets reveal datatype problems sooner (compile time vs run time). Sooner = easier / cheaper to fix.

Stored Procs = centralization. Centralization is of course both good and bad. The stored procs can, in theory, be used by multiple applications. Of course, with proper OOP this is less of a big bonus, but it does give some flexibility, esp in an environment where you have different application platforms accessing the same DB.

Stored procs make learning easier in a multi-developer environment, esp one with high turnover. Newbie coders can get up to speed in regards to the DB Schema much more quickly if the library of stored procs is well-named and well thought out. You could also translate this to say it makes maintenance easier over the long haul (think of going back and looking at this stuff again 3 years hence with other projects in-between). It depends on how much of your own dogfood you think you'll have to eat.

Gut reaction based on lots of experience doing both stored procs and dynamic SQL: Stored procs are NOT a silver bullet and can be a pain in the neck early on, but they sometimes pay off in the long run. Once you get into a groove with them, I think you will like them.

Middle Tiers And Different Databases (0)

Anonymous Coward | about 10 years ago | (#9849225)

The bad thing about stored procedures is that they are largely database-specific in syntax. Putting the logic in a middle-tier would allow you to target different databases easier, if your database access framework is more flexible/portable (all of the good ones are).

Separation of Duties (2, Insightful)

Cornelius42 (516371) | about 10 years ago | (#9849232)

If your project is large enough, you separate the development, and allow for your DB admins to create stored procedures.

If your project is small, one programmer, then there is no need to separate duties.

Enforce business rules in the database engine (0)

Anonymous Coward | about 10 years ago | (#9849234)

Stored procedures, triggers and check constraints that enforce business rules ensure that bad data won't get into your database from any source. If your business rules are only enforced by your fat-client application, then anybody with an ODBC client can put bad data in your database.

Stored Procedures often more harmful than helpful (5, Insightful)

1010011010 (53039) | about 10 years ago | (#9849238)

Implementing your application logic as stored procedures has some detrimental side effects.

  1. SPs turn your database into an application server, centralizing things that needn't be, and raising load on that central machine.
  2. SPs invite use of vendor-specific features, and therefore lock-in and loss of portability.
  3. SPs are not typically amenable version control and are maintained outside the rest of your code base.
  4. SPs represent "premature optimization." There may be a time and a place for SPs, but they are used a lot more than needed in many applications. For example, one application at my company has over 1,000 SPs, and quite a number are just wrappers for simple select statements.


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).

Schema maintenance (1)

Clod9 (665325) | about 10 years ago | (#9849242)

The major advantage you didn't mention is that stored procedures let you abstract your database schema from the programmatic logic that accesses it. You may have many disparate applications (both web-based and non-web-based) in various languages which all access the database, and if you always do it via stored procedures, then you can change your schema without upsetting the application code.

This is extremely powerful, especially if you have a DBA, but even if you don't your developers can much more easily audit who is doing what.

For instance, say someone needs to add a column to table T; how to know whether there's logic somewhere that will get broken? You have to search for the table name T in all your application code. If you use stored procedures exclusively, then you can know unambiguously what the effect of the change will be. You can ask questions like "does anyone actually use column C for anything?", or "if we change the datatype of column C, what code will be affected?"

You don't necessarily need a skilled stored proc coder -- after all, if your developers are writing their own queries anyway, wrapping them in a stored proc is not hard. If you need complex logic in the DB layer, someone's going to have to learn to do so regardless of whether stored procedures are used.

SP Kool-Aid == yummy (1)

greengearbox (799231) | about 10 years ago | (#9849244)

I've worked on two mid sized web/db projects now with a couple of old skool Oracle hackers. They are quite sharp, but not really up on all the hot new O/R mapping technology. Hibernate. Castor. etc.
So I figure I'd teach them a bit about the "right" way of doing this. Use the database to store data. Period. Model business objects as Java classes. Implement business rules in Java, and code the views in terms of those objects.

The thing is, the more we talked about this, the more I came to question the way I had done things in the past. For example, it's not always possible (or at least convenient) to define a single set of business objects. A Widget might look one way from a manufacturing perspective, and another from a sales perspective, to take an absurd example. Often I end up fetching more data that I really need, because the BOs have been defined to be as general as possible. And so on.

My particular case is special, because there will more likely always be people with heavy Oracle expertise to maintain these systems than people with Java experience. So the risk of sticking all the rules into the database, only to end up with a maintenance nightmare after the Oracle guys have left doesn't really exist. But even so, I've come around to thinking quite a bit differently about the role of the database in a multi-tier application.

Standardized Data Access (1)

Mike9000 (581090) | about 10 years ago | (#9849246)

I like to keep as much of the data access (read and write) in the SP's. This way you ensure that all of your applications are using the same routines to get your information. This is important when you have a large number of reports. The easiest way to make sure your reports report the same information consistently is to have them access the same SP's.

Just my humble opinion.

As always, it depends (3, Informative)

Percy_Blakeney (542178) | about 10 years ago | (#9849249)

Do you have multiple applications accessing the database, or is it just the single web-based app? If you just have a single app, then it doesn't really matter much -- put it wherever you want. On the other hand, if you currently have or plan on eventually having multiple apps (web-based in PHP/ASP, desktop-based in C++/Java, etc...) then I'd create some stored procedures in the database. It will allow you to keep the database-oriented logic close to the data, thereby reducing how much duplication of near-identical code you'll need to write into the different apps.

In addition, depending on your situation, it shouldn't be too hard for the developers to learn how to write stored procedures for the database. Once you know one language, learning another isn't that hard. The developers might write some inefficient code at first, but they'll get better very quickly. Plus, it will give them a better idea of how the database really works and performs, improving their overall designs.

Of course, IANADBA (I am not a DBA), so take it with a grain of salt.

Forget about performence, it makes your code clean (1)

lphuberdeau (774176) | about 10 years ago | (#9849252)

One of the huge advantages of Stored functions/Procedures is that they make your code a lot cleaner by removing about every logic from the interface. Of course, it makes it easy to have multiple interfaces but most of the time, this probably won't happen.

Of course, for them to be really useful, you need some way to sort and document them because as everything else, you will end up loosing them and forget they exist. I've seen problems like that with undocumented procedures and views. It ends up being a total mess.

I've used Oracle for a while and procedures/functions are quite useful and easy to create once you figured out all the little syntax problems that come with Oracle... (don't they know about make syntax uniform?). I ran a few tests on MySQL 5 alpha, it's quite unstable now but the implentation seem much better to me. I'll have to get the development version from BitKeeper before going any further in the tests because there seem to be a problem with DROP PROCEDURE in the version I have (not blaming, I know the risks of alpha versions).

When it comes to security and data integrity, there is nothing like triggers, but it will have to wait until MySQL 5.1, which is probably over 2 years ahead since MySQL 5 itself probably won't be released before an other 1.5 year.

Databases should store data (0)

Anonymous Coward | about 10 years ago | (#9849253)

My own opinion is that a database was originally made to be efficient in STORING/RETRIEVING DATA, not processing it. It feels wrong to depend too much on a database for storing your data AND taking care of all of the application logic. What if you need to move your data to a completely different database? You are either screwed, or looking at spending a nightmare amount of time porting it over. Develop your logic in your frontend application and just use the database for what it was originally meant for: data storage and retrieval. BTW, I manage an online service that processes millions of database records a day and this is how we are setup. We wouldn't want to do it any differently.

2 tiers (1)

vurg (639307) | about 10 years ago | (#9849254)

You'll worry about these because you're writing both tiers (db and client). Often times it's not the case. The senior programmer will just tell you this is the database server and these are the stored procedures you can call to read or write stuff. You don't have to think about the table structure because the SPs handle those things.

If you're writing both tiers then I could understand that. I myself sometimes get tired of looking back and forth between the SP and the program code.

USE THEM (0)

Anonymous Coward | about 10 years ago | (#9849256)

As a dba for 10 years now I can honstly say that you shoudl be using stored procedures but only for CRUD needs. Imbedding any business logic at all in the proc will only lead to trouble, not using them leads to a bunch of trouble if your trying to tune your db or if you need to change a data structure. PLEASE PLEASE PLEASE do not let your DBA be lazy and not write stored procs for you!!!

An example of good (2, Informative)

Martin Foster (4949) | about 10 years ago | (#9849260)

A year ago, I converted an open source project I write in my spare time from MySQL to PostgreSQL. The primary driving factor in the conversion was to make use of the more robust features of PostgreSQL in order to maintain data integrity.

This involved of course the creation of a schema that made use of referential integrity and stored procedures for certain key operations that would enforce data integrity that the code required but fell outside of relational databases proper.

As I was completing the source code conversion I noticed that a lot of the data checks that had to be done under MySQL 3.x disappeared as PostgreSQL enforced it for me.

The creation of users, and other entities became much simpler as did their removal. Cleaning up the code and making it easier for me to make modifications to the scripts, without having to second guess another script having adverse effects.

The scripts themselves still handle logic, albeit at a higher level. The process of using stored procedures to handle data integrity and enforcing certain rules simply allowed me to concentrate on the bigger picture when dealing with scripts.

Of course, the trade off was in speed. MySQL to this day, still seemed to be capable of handling more loads since the site is dominant on SELECT statements. However that is more of an issue between PostgreSQL and MySQL proper.

Been there, done that (1)

DanielMarkham (765899) | about 10 years ago | (#9849261)

First of all, your question sounds a lot like "What's your favorite color?"

Secondly, where do you want to put your cycles at? For larger applications, that's an important question.

I've done this and seen this a dozen ways from Sunday. For every bad implementation of SPs I can show you a good one. Good coders write good code. Bad coders write crap. Sorry, but there is no one answer. For a compentent code mechanic, you can put it here or there or anywhere. All the same. (lots of "devil in the details" goes here)

Having said that, tiering is a great thing. Use it when you can for better code maintainability. But at the end of the day, you usually end up with business rules all over the place anyway. If you stick the business rules locked into the database, and lock out access otherwise, at least you know they'll always be executed. And that, of course, is the entire reason for having business rules in the first place.

I can argue this from every angle, which probably makes it a great slashdot topic! Endless speculating without any conclusion! (grin)

Maintenance (1)

bboyers (21742) | about 10 years ago | (#9849266)

In my experience stored procedures are the way to go but please take care with maintenance activities. Since database objects can't list external dependencies like internal dependencies (e.g. views/procedures/trigger/etc). Without knowing the dependencies a certain about amount of fear creeps into maintenance work. The fear of updating a procedure and breaking other web pages (or the lack of time for analysis) sometimes cause copies of the same procedure to be create with slight changes but under different names. Over time these "new" procedure become a problem since it just leads to more "fear" of change. Fear is greatest problem when dealing with an application code base.

Of course with good execution and experienced programmers this (and all issues) are not a problem, but over time poor execution and less experienced programmers will compromise the integrity of an application design.

I can only go from what our DBA does, but... (1)

RangerFish (770981) | about 10 years ago | (#9849269)

...she uses stored procedures like crazy.

I can definitely see the sense in this. Although it does make the db design more complicated, it puts a layer between your app and the actual tables and fields. That means that you can change the fields willy-nilly, and as long as your app's only using stored procs to get to the data, that's all you need to change.

There's whole books on using similar concepts in code.

GOOD! (0)

Anonymous Coward | about 10 years ago | (#9849272)

Stored Procs allow the clientside portion of an application to pass queries to a Server-Side processing done by a backend "industrial strength" database housed MOST LIKELY, on a FAR MORE POWERFUL (in RAM + CPU hardware-wise) system than the client machines usually & far more efficient system for data processing (since the queries get compiled too) & return of said data in a return recordset to the client program & client system...

At that point? All the client program has to do is populate say, a grid (AND in the doing of this? I strongly recommend the use of the Sleep API to stall that grid population every 3rd record or so (this you have to tune for, & also this allows SOME users to have more "oomph" than others too, like mgt. which always demand this)).

This use of that API call for external-to-application multitasking & ceding of processor time between apps running on the client system vs. the amount of CPU time given to the middleware (program that passes data back to serverside & acts as interface to server dataengine) helps multi-user setups NOT so much on ordinary LANS locally located @ say, main company site? BUT MORE those that use say Citrix WinFrame/MetaFrame or Windows Terminal Server setups to NOT "lockup" each client node coming thru usually a single remote site pipe over the public internet (this type of solution is often used to save money & create what I call a 'cost-effective 2.5 tier application... it works like a dream, & keeps the client rigs from sailing up to 100% CPU use one by one because on Citrix/TS? They share a SINGLE desktop & middleware technically... you flood that single instance? In effect, you DDOS your citrix/ts desktop shared access point... I've seen it happen, this cured it, & drove clientside remote TS user rigs CPU use down to 4% each from 100% each. Keep this in mind when designing!).

ANYHOW:

The clientside application, all it does using stored procs? Is ask a question & passes it to the server via middleware (how you pass data to/from dataengine on backside), the server-side dataengine processes it, & send the client back its answer!

Most efficient, most effective, & keeps business logic off the clientside app. as well especially in multi-user environs which most apps of this type? ARE!

APK

I like stored procedures (4, Informative)

Kentamanos (320208) | about 10 years ago | (#9849275)

Besides the optimization the DB might do on SP's as opposed to dynamically created SQL statements, SP's are nice from a security point of view.

You have to be extra careful with dynamic SQL due to SQL injection bugs that we all know about. This isn't really an issue when you're dealing with stored procedures that take defined data types as opposed to creating SQL on the fly based upon your data (which could have injected SQL).

Controlling which DB accounts can use what stored procedures is also handy mechanism for determining permissions. Stored procedures represent what all your application might do, so picking which DB connections (which have credentials) can access these is a nice place to control those permissions.

Granted, you can still do lots of stupid things to mess up security :).

Also, there are places where SP's are not really possible. Severely dynamic reports are a good example (assuming you allow that functionality in your application). There's definitely times when you HAVE to generate SQL on the fly. In any event, try to create a "data access layer" in your code and if you have to dynamically generate SQL, run all sorts of checks on it with regexp's etc to check for injection.

stored procedures bad (1)

fred fleenblat (463628) | about 10 years ago | (#9849277)

1. Each vendor has a special language for stored procedures that is incompatible with other DB's...so it's a vendor lock-in mechanism.

2. Stored procedures which are triggers operate in a weird out-of-phase universe where they can do stuff before, during, or after a transaction. Yes, this makes them powerful, but the transactional behavior of the procedure depends on the context in which it is invoked. So you end up making a mental map (it never seems to be documented) of which SP's operate in which phase. One odd but effective way to deal with this to put the word "before", "during", or "after" (optionally row also) into the name of the stored procedure.

3. It kind of breaks the (sometimes adhered to) idea of keeping a clean separation of the algorithms from the data, in the same way that writing into the instruction cache is best not done.

It depends (as always) (2, Informative)

chochos (700687) | about 10 years ago | (#9849285)

One case where I find that SP's can be useful for storing business logic is when your system will have different front ends using different technologies. For example, if you have a web frontend with PHP or Java but also have a rich client written in .NET or VB.
Of course you can also solve this using an additional tier (like an app server and use web services) and it could be easier to maintain, but if performance is too much of an issue, then you could go for SP's for some of the logic.
I don't think it has to be an all-or-nothing decision, though. You usually end up with some logic in the app code and sometimes some logic in SP's.

Neither (0)

Anonymous Coward | about 10 years ago | (#9849307)

Use Hibernate (or an equivilant) and never worry about either again.

Have data access objects helps the most (1)

Invisible Now (525401) | about 10 years ago | (#9849312)

I work for a big bank. For small web based apps we put the sql inline with the ASP or JSP page code. Makes the web page essentially self documenting if you have an ER diagram.

But for big apps - say like www.bigbankxyz.com - what really matters is robust stability and the abilty to tune app performance over time. To do this you need to build a layer of data access objects that abstract and wrap around the SQL or SP.

With a data object layer your frontend folks can then use these objects to do things like:

accountbalance.retrieve acctno

in the page code.

The big advantages in addition to simple productivity are:

1) If the underlying data source or even the source system changes you can update the data access objects and the frontend folks are fine and oblivious

2) If your site has performance issues you can selectively build data caching into slow objects (queries) after deployment and the frontend code runs faster without any code changes

3) You can apply a number of load balancing, regionalization, clustering, fail over etc strategies across frontend servers by manipulating the objects while the page code, again, stays the same

On the other hand, it's not worth the effort if you're always going to have only a few developers running a two server (webserver and DB) setup.

Both have their place (0)

Anonymous Coward | about 10 years ago | (#9849314)

Stored procedures are definitly a good idea once the requirements/design are finalized, but it's a hard arguement to make while developing unless the developer also is the DBA. While some may argue that it enforces better design up front, I would remind you that most requirements/designs rarely make it from paper to code unchanged, and the development costs both in time and talent are significant with stored procedures.

If you went with stored proceedures, everytime there is a business logic change, or requirements change, your front end/business logic developers would have to wait until your DBA makes the appropriate changes to the data layer before being able to continue. For me, that's just too much wasted time

With most applications that i've done, one of the major requirements is database interoperatability. I've always been asked to make my systems portable between at least Oracle/Sybase/SQL2K. With that requirement, you would need someone trained in porting stored procedures in both PL-SQL, and T-SQL. Once you start adding in less familiar databases ( MySQL/PostgreSQL/SAP etc ) things get dicey quickly. The talent required becomes much more expensive and difficult to find.

Contrast that with writing your business logic in pure SQL92. Database porting this way is simple. translate the tables. watch for weird database quirks. translate your value objects. done. If you use java, then things are even easier now with O/R Mapping libraries such as Hibernate [hibernate.org] , Castor [castor.org] and JDO [solarmetric.com] .

If you want the best of both worlds, and you have the skills required, just put the SQL in the code, but write it in TSQL/PLSQL as if it were a stored procedure. That way it's easier to develop, and when it's time to write the stored procedure, it's already half written. Just remember that there are very few instances where the performance requirements really justify writing a stored proceedure. Be smart where your optimize your database.
Load More Comments
Slashdot Login

Need an Account?

Forgot your password?
or Connect with...

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>