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!

MySQL Stored Procedure Programming

samzenpus posted more than 7 years ago | from the a-little-lite-afternoon-reading dept.

Book Reviews 206

Michael J. Ross writes "MySQL may be the most popular open source relational database management system (RDBMS) in the world, but during the first decade of its existence, it lacked support for stored programs, i.e., store procedures, functions, and triggers. The major commercial RDBMS vendors — including Oracle, IBM, and Microsoft — could point to this deficiency as reason enough to choose their proprietary systems over MySQL or any other open source system, such as PostgreSQL. But with the release of MySQL version 5.0, in October 2005, the "little database engine that could" dramatically improved its position against the competition. The most comprehensive discussion of these new capabilities is in the book MySQL Stored Procedure Programming." Read below for the rest of Michael's review

Written by Guy Harrison and Steven Feuerstein, and published by O'Reilly Media in March 2006 under the ISBNs 0596100892 and 978-0596100896, this book is the first one to offer database programmers a full discussion of the syntax, usage, and optimization of MySQL stored procedures, stored functions, and triggers — which the authors wisely refer to collectively as "stored programs," to simplify the manuscript. Even a year after the introduction of these new capabilities in MySQL, they have received remarkably little coverage by book publishers. Admittedly, there are three such chapters in MySQL Administrator's Guide and Language Reference (2nd Edition), written by some of the developers of MySQL, and published by MySQL Press. Yet this latter book — even though published a month after O'Reilly's — devotes fewer than 50 pages to stored programs, and the material is not in the printed book itself, but in the "MySQL Language Reference" part, on the accompanying CD. That material, in conjunction with the online reference documentation, may be sufficient for the more simple stored program development needs. But for any MySQL developer who wishes to understand in-depth how to make the most of this new functionality in version 5.0, they will likely need a much more substantial treatment — and that's exactly what Harrison and Feuerstein have created.

The authors are generous in both the technical information and development advice that they offer. The book's material spans 636 pages, organized into 23 chapters, grouped into four parts, followed by an index. The first part, "Stored Programming Fundamentals," provides an introduction and then a tutorial, both taking a broad view of MySQL stored programs. The remaining four chapters cover language fundamentals; blocks, conditional statements, and iterative programming; SQL; and error handling. The book's second part, "Stored Program Construction," may be considered the heart of the book, because its five chapters present the details of creating stored programs in general, using transaction management, using MySQL's built-in functions, and creating one's own stored functions, as well as triggers. The third part, "Using MySQL Stored Programs and Applications," explains some of the advantages and disadvantages of stored programs, and then illustrates how to call those stored programs from source code written in any one of five different programming languages: PHP, Java, Perl, Python, and Microsoft.NET. In the fourth and final part, "Optimizing Stored Programs," the authors focus on the security and tuning of stored programs, tuning SQL, optimizing the code, and optimizing the development process itself.

This is a substantial book, encompassing a great deal of technical as well as advisory information. Consequently, no review such as this can hope to describe or critically comment upon every section of every chapter of every part. Yet the overall quality and utility of the manuscript can be discerned simply by choosing just one of the aforesaid Web programming languages, and writing some code in that language to call some MySQL stored procedures and functions, to get results from a test database — and developing all of this code while relying solely upon the book under review. Creating some simple stored procedures, and calling them from some PHP and Perl scripts, demonstrated to me that MySQL Stored Procedure Programming contains more than enough coverage of the topics to be an invaluable guide in developing the most common functionality that a programmer would need to implement.

The book appears to have very few aspects or specific sections in need of improvement. The discussion of variable scoping, in Chapter 4, is too cursory (no database pun intended). In terms of the book's sample code, I found countless cases of inconsistency of formatting — specifically, operators such as "||" and "=" being jammed up against their adjacent elements, without any whitespace to improve readability. These minor flaws could be easily remedied in the next edition. Some programming books make similar mistakes, but throughout their text, which is even worse. Fortunately, most of the code in this book is neatly formatted, and the variable and program names are generally descriptive enough.

Some of the book's material could have been left out without great loss — thereby reducing the book's size, weight, and presumably price. The two chapters on basic and advanced SQL tuning contain techniques and recommendations covered with equal skill in other MySQL books, and were not needed in this one. On the other hand, sloppy developers who churn out lamentable code might argue that the last chapter, which focuses on best programming practices, could also be excised; but those are the very individuals who need those recommendations the most.

Fortunately, the few weaknesses in the book are completely overwhelmed by its positive qualities, of which there are many. The coverage of the topics is quite extensive, but without the repetition often seen in many other technical books of this size. The explanations are written with clarity, and provide enough detail for any experienced database programmer to understand the general concepts, as well as the specific details. The sample code effectively illustrates the ideas presented in the narration. The font, layout, organization, and fold-flat binding of this book, all make it a joy to read — as is characteristic of many of O'Reilly's titles.

Moreover, any programming book that manages to lighten the load of the reader by offering a touch of humor here and there, cannot be all bad. Steven Feuerstein is the author of several well-regarded books on Oracle, and it was nice to see him poke some fun at the database heavyweight, in his choice of sample code to demonstrate the my_replace() function: my_replace( 'We love the Oracle server', 'Oracle', 'MySQL').

The prospective reader who would like to learn more about this book, can consult its Web page on O'Reilly's site. There they will find both short and full descriptions, confirmed and unconfirmed errata, a link for writing a reader review, an online table of contents and index, and a sample chapter (number 6, "Error Handling"), in PDF format. In addition, the visitor can download all of the sample code in the book (562 files) and the sample database, as a mysqldump file.

Overall, MySQL Stored Procedure Programming is adeptly written, neatly organized, and exhaustive in its coverage of the topics. It is and likely will remain the premier printed resource for Web and database developers who want to learn how to create and optimize stored procedures, functions, and triggers within MySQL.

Michael J. Ross is a Web programmer, freelance writer, and the editor of PristinePlanet.com's free newsletter. He can be reached at www.ross.ws, hosted by SiteGround.


You can purchase MySQL Stored Procedure Programming from amazon.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

cancel ×

206 comments

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

MySQL aren't trustworthy (3, Insightful)

ShieldW0lf (601553) | more than 7 years ago | (#18786829)

You know what it is?

It's lack of trust.

MySQL started off making their fast little datastore that uses a little SQL syntax, and they told everyone under the sun that if MySQL didn't do it, you didn't need it, and ought to design around it.

Referential integrity, ACID compliance, enforcement of rules... every step along the way, they tell you you don't need it and you ought to push the missing logic to the next tier, until they get it, if they get it, then they're so great.

Couple that with the Project Mayo/DivX corporate structure, then place Postgresql next to the whole shebang as a superior alternative, and the arguments for giving MySQL any of your attention become increasingly small.

MySQL owes its success to the fact that it was always so simple to choke it off when you were giving it to people on $5/month hosting plans, so it became popular among the very cheap.

Period.

The parent comment is a classic example of FUD (2, Insightful)

Andy Tai (1884) | more than 7 years ago | (#18786889)

Does it tell anything concrete about MySQL 5? No.

Not really FUD (3, Interesting)

Anonymous Coward | more than 7 years ago | (#18787269)

It IS a little out of date, but the MySQL forums were dotted with little gems from the programming team like "if MySQL doesn't do it, you don't need it".

For a high-traffic fairly leisurely updating database where element veracity isn't CRITICAL, they were right.

What they didn't realise was that people on the forums didn't all work in areas where that was true and would prefer not to have to use two databases...

They're better, but then again, they're losing the edge on speed, too.

Re:Not really FUD (5, Informative)

elp (45629) | more than 7 years ago | (#18788085)

Yep it was really irritating. A lot like the Postgres users who kept claiming the next version of their server was going to be really fast.

In the end the postgres crowd have a right to be upset. They could have been the major player for open databases but their devs were too arrogant to listen to their users and design it to be friendly.

For all its faults mysql is trivial to install and works out of the box for most applications. Last time I looked the default install for postgresql still seemed to be tuned as though it would never have to handle more than a handful of users. Stored procedures and TCP/IP are off by default and the auto vacuum thing needs to be set up manually. Then don't forget that while the postgres query parser does more error checking that mysql its error messages are incredibly cryptic so its MUCH hard to trouble shoot.

Your average newbie takes one looks at it, gives up and moves to mysql. When that newbie finally grows up he has too much time invested in mysql to be bothered learning the intricacies of postgres. Every extra feature that mysql adds is one less reason for anyone to try postgres.

Postgres is a better DB once its setup and tuned properly but considering its usability issues and that mysql users probably out number postgres users a thousand to 1 or more I think mysql is always going to be the number one opensource DB.

Re:The parent comment is a classic example of FUD (4, Interesting)

cyphercell (843398) | more than 7 years ago | (#18787337)

Does it tell anything concrete about MySQL 5? No.

It does speak volumes about MySQL's history, though. The things he lists are are the reasons why MySQL is still compared with M$ Access. They are valid points, because MySQL as a company has a sordid past, as far as presenting something that resembles modern database theory. I'm not saying I don't appreciate MySQL, I'm just say that I thoroughly understand the frustrations. MySQL has been presented as an off the shelf enterprise contendor for years and that's only begining to resemble fact.

Is 640kB enough to run a MySQL server? (1)

faramir_fr (831190) | more than 7 years ago | (#18786895)

If not... the keystone that says that we will never need more than 640kB must be thrown away.

Re:MySQL aren't trustworthy (3, Insightful)

bahwi (43111) | more than 7 years ago | (#18786927)

"Postgresql next to the whole shebang as a superior alternative"

I call shenanigans. Only recently has it become superior. Documentation isn't just a feature, it's a necessity, even with the open source world, and Postgresql's documentation is finally getting to a good point. I tried numerous times to install and use this "superior alternative" with bad documentation(it was all there, but in backwards, confusing steps, how to access your database was before creating it, etc..) it was just disorganized. Privileges were a bitch with very confusing instructions. Yeah, if you knew it, superior alternative, if you didn't, RTFM, oh wait, that didn't actually help. Now the MySQL can compete better on features(not completely mind you, PostgreSQL still has some important features missing from MySQL) the documentation is starting to improve a lot on the postgresql front.

Re:MySQL aren't trustworthy (2, Informative)

Overly Critical Guy (663429) | more than 7 years ago | (#18787615)

I've never had a problem with documentation. Are you saying the only reason you discounted PostgreSQL as a superior database was the documentation, despite the fact that for years, MySQL would happily drop data without telling you?

Re:MySQL aren't trustworthy (1)

morgan_greywolf (835522) | more than 7 years ago | (#18787963)

I've never had a problem with documentation. Are you saying the only reason you discounted PostgreSQL as a superior database was the documentation, despite the fact that for years, MySQL would happily drop data without telling you?


I am! Let me tell you: PostgreSQL's documentation, until very recently, was quite lacking. I agree the poster you're replying to: the docs were a disorganized mess and were missing important parts.

A piece of software is only useful if you can figure out how to use it without having to run to the forums or google for answers every 5 minutes. The key to being able to figure out something as complicated as an RDBMS is documentation.

Put another way: how useful would [insert your favorite distro] Linux be to even a seasoned UNIX administrator with no Linux experience if it had either no manpages or poorly written ones?

Re:MySQL aren't trustworthy (1, Interesting)

Anonymous Coward | more than 7 years ago | (#18787667)

Bahwi, what the hell are you talking about...

I have been using PostgreSQL for over 6 years and I've always found all the documentation I've needed (mostly on postgresql.org actually).

You call shenanigans...I'm sorry but I have to call incompetence on your part...

Re:MySQL aren't trustworthy (3, Informative)

XorNand (517466) | more than 7 years ago | (#18787739)

I recently decided to move to Postgres from MySQL. There are some pretty cool things about Postgres; flexible authentication options and sequences being my current favorite. I've found that it's docs are actually pretty good (I don't know what they were like before). However, since the topic here are stored procedures, I have to really complain bitterly about Postgres's implementation of stored procedures. Creating a sproc that returns a dataset rather than just a outpur parameter is a convoluted dance. You have to exactly define the output using a "TYPE" and then iterate over the dataset using a cursor-like syntax.

Consider this simple example that I'm currently working on. It's for an internal peer review application where coworkers give one positive and one negative comment about each other:

DROP TYPE answers_type CASCADE;
CREATE TYPE answers_type AS (
positive text
,negative text);

CREATE OR REPLACE FUNCTION answers_get(
_review_id int
) RETURNS setof answers_type AS $$
DECLARE
rec answers_type;
BEGIN

FOR rec IN SELECT
positive
,negative
FROM answers
WHERE review = _review_id
ORDER BY id DESC
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END; $$ LANGUAGE plpgsql;

This ends up being very unwieldy for larger and more complex stored procedures. I recently compared simple output -parameter only stored procedures [rightbrainnetworks.com] between Postgres, MySQL, and MS SQL on my blog. Fortunately it's a lot more straightforward when using that method. I'll stick with Postgres for now and just hope that this is eventually improved.

Re:MySQL aren't trustworthy (3, Insightful)

LizardKing (5245) | more than 7 years ago | (#18788871)

Only recently has it become superior. Documentation isn't just a feature, it's a necessity, even with the open source world, and Postgresql's documentation is finally getting to a good point.

Unless something dramatic has happened to MySQL documentation since I last used it (version 4.1), then PostgreSQL has the better documenation and has done since as long as I've been using it. The PostgreSQL documentation reads like a coherent set of books, while MySQL documentation seems to be a random mess that grew out of some simple README file. As for the software itself, PostgreSQL has always been superior - a simple comparison of features alone bears this out, but if you want to go further take a look at the code as the same distinction is found there.

Names matter sometimes (4, Insightful)

ClosedSource (238333) | more than 7 years ago | (#18787215)

I think part of postgresql's problem is its awful name. Just about anything would have been better (e.g. TurboSQL, AgileSQL, FastSQL, UglySQL, FatDumbAndHappySQL, etc).

Re:Names matter sometimes (2, Funny)

Sloppy (14984) | more than 7 years ago | (#18787603)

Ok, I'll fork it and call it OggVorbiSQL.

Re:Names matter sometimes (0)

Anonymous Coward | more than 7 years ago | (#18788917)

CodecSQL? DiskworldSQL?

Re:MySQL aren't trustworthy (0)

Anonymous Coward | more than 7 years ago | (#18787247)

Spot on. Couldn't have put it better myself.

isnt it the same for ALL the stuff that took off : (1)

unity100 (970058) | more than 7 years ago | (#18787265)

MySQL owes its success to the fact that it was always so simple to choke it off when you were giving it to people on $5/month hosting plans, so it became popular among the very cheap.


age old concept - cheap, easy, simple thing that works. almost all tech stuff took off like that.

Emulated SP's (1)

vivin (671928) | more than 7 years ago | (#18787653)

Although it wasn't efficient, I hacked together a way to call "stored procedures" in MySQL and PHP. Essentially I had a function callSP. You passed in the name of the SP's and whatever parameters you needed (as a string). Then I'd use PHP to build the necessary query and return $result. It worked for whatever I was doing... although it wasn't a "true SP".

Re:Emulated SP's (1)

Ajehals (947354) | more than 7 years ago | (#18788849)

Its a "procedure" that is "stored" somewhere, if it works it works!

For the record on the MySQL (The world's most popular open source database) vs PostgreSQL (The world's most advanced open source database), I think Prefer flat files and grep.

Wait, this sounds familiar... (1)

Mr. Underbridge (666784) | more than 7 years ago | (#18787661)

... every step along the way, they tell you you don't need it and you ought to push the missing logic to the next tier, until they get it, if they get it, then they're so great.

Sounds like they're violating a patent for "Method and Implementation of Field-Based Reality Distortion" held by Apple, Inc.

Re:MySQL aren't trustworthy (5, Interesting)

CoughDropAddict (40792) | more than 7 years ago | (#18788459)

Amen, brother.

For people not familiar with MySQL's history, I would suggest a little reading from previous versions of MySQL's manual:

How to cope without COMMIT/ROLLBACK: For the moment, we are much more for implementing the SQL server language (something like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance. [utk.edu]

Reasons not to use foreign keys. There are so many problems with FOREIGN KEYs that we don't know where to start. [utk.edu]

I decided long ago that the MySQL guys are clowns. MySQL's lack of features was never as big a problem as the fact that I just couldn't take these guys seriously (and the above is only a small subset of the reasons for that).

MySQL vs Firebird (0, Offtopic)

BuR4N (512430) | more than 7 years ago | (#18786857)

This might come off as offtopic, but I'll take the risk.

Is there anyone here that have experiance with both of these databases that can explain in simple terms why one would go with MySQL over Firebird ?

No intention of starting a flamewar, I'm just interested in finding out the major pro's and con's between the two.

Re:MySQL vs Firebird (4, Insightful)

PCM2 (4486) | more than 7 years ago | (#18786979)

Is there anyone here that have experiance with both of these databases that can explain in simple terms why one would go with MySQL over Firebird ?

My understanding is that Firebird is by any measure more sophisticated than MySQL, but it lacks the "critical mass" of users that makes it attractive to people who need to be sure they can get ready support for their software. MySQL is available on just about every cheap hosting provider around, too, which means that a lot of ready-built open source Web apps target MySQL as their database of choice.

Put it to you this way: If the features of MySQL are "good enough" for the application you want -- which arguably goes for most of the Web apps out there -- why would you not choose MySQL? There are times when swimming upstream is a noble effort, but generally all it gets you is tired.

If, on the other hand, you have a specific application for which you need a relational data store and you need higher-end RDBMS features, by all means, choose Firebird. Only I think in those cases most people still choose PostgreSQL, for more or less the same reasons as mentioned above.

Re:MySQL vs Firebird (1)

otis wildflower (4889) | more than 7 years ago | (#18787549)

Is the CLI for Firebird as friendly as MySQL's? I've found most vendors' CLIs extremely unfriendly and limited compared to MySQL. Sort of like how Sun's unix utilities are largely crap compared to their GNU analogs.

Trying to show columns from table in Sybase? I'd rather stick a fork in my balls.

Re:MySQL vs Firebird (1)

gnuman99 (746007) | more than 7 years ago | (#18788483)

Try PostgreSQL. The command in psql (CLI for Postgres) is a nice,

\d [table name]

or \l to list all tables in database.

Very handy and fast.

Re:MySQL vs Firebird (1)

aled (228417) | more than 7 years ago | (#18788567)

Is the CLI for Firebird as friendly as MySQL's?


Firebird CLI (as of version 1.0.3 which is not a recent one but is what I use) is as cool to use as eating rocks. It made me have remember fondly the old Informix dbaccess utility...

Re:MySQL vs Firebird (1)

newt0311 (973957) | more than 7 years ago | (#18788689)

in postgres:

\d <table_name>

pretty easy.

Re:MySQL vs Firebird (1)

kpharmer (452893) | more than 7 years ago | (#18788663)

> Put it to you this way: If the features of MySQL are "good enough" for the application you want -- which arguably goes for
> most of the Web apps out there -- why would you not choose MySQL? There are times when swimming upstream is a noble
> effort, but generally all it gets you is tired.

Great point. Aside from asking what exactly "good enough" means - and pointing out how odd it is that some would insist on picking a tool because it's "good enough" while others insist on picking "the best tool for the job", a few possible reasons:

1. maybe you don't want to waste time testing for exceptions that should be reported in a more robust fashion
2. maybe you need a large reporting database - and don't want to waste $100k+ on extra hardware to make up for mysql's lack of partitioning, parallelism, automatic summarization and mature optimization.
3. maybe you need multiple databases, and one of them is a reporting database - so decide to go for a consistent other option to save on labor (which is more expensive that licensing costs these days)
4. maybe you want free online backups
5. maybe you want to avoid licensing costs
6. maybe you want to avoid having to talk to a lawyer to deal with mysql's obfuscated license
7. maybe you need better optimization for complex queries
8. maybe you want to ensure that clients can't override your data quality constraints
9. maybe you find that there are many great programmers who would prefer not to work with mysql
10. etc,etc,etc

So, quite a few reasons why a person might think that mysql has a way to go before being good enough for their project.

Re:MySQL vs Firebird (0)

Anonymous Coward | more than 7 years ago | (#18788001)

I can't tell you about MySQL because I never used it and probably never will. With the others that are available, why bother.

Between Firebird and Postgres, much of the same capabilities are there, but the philosophy is different. Firebird was originally meant to be an embedded database. Its ancestor can be found in the Ahbrams tank. Its intended to be self-optimizing. Therefore there is not a lot of settings that you can tweak like in Postgres (and Oracle and DB2). I generally use Firebird first.

If I'm in the situation that I need to tweak the performance, then I go with Postgres (or Oracle or DB2). So why not just go with Postgres? The usage between the two os obvious when your using it. Firebird is so simple. Its just easier if you don't need the control. As always, use the right tool for the job

Re:MySQL vs Firebird (3, Insightful)

LurkerXXX (667952) | more than 7 years ago | (#18788397)

Why would you go with MySQL over Firebird?

Easy. The nimrod who wrote the application that you want/need to run didn't make the app database agnostic, so you are stuck with MySQL because "it's more popular". It's a catch 22. Until more folks start writing database agnostic apps, lots of us will get stuck using MySQL in places where we might prefer other databases. And so that will perpetuate MySQL being seen as being more popular... Argh.

Re:MySQL vs Firebird (0)

Anonymous Coward | more than 7 years ago | (#18788603)

In my own experience (which may be a bit outdated because of the newer versions out there) I prefer Firebird by miles for software development (ie, custom database applications) while I prefer MySQL for websites. AFAIK, MySQL has higher performance with SELECTS and lower with INSERTs and UPDATEs, i.e., more focused on presenting the data, than Firebird. Firebird has had most (if not all) the advanced features like stored procedures, triggers, good & fast relational, custom SQL functions, etc since it was Interbase 3 or 4 and it is fast and lightweight. It's 'stored programs' language has always felt a bit odd though.

I think it's more a 'what ya wanna do?' thing. For web, MySQL is great, it comes preinstalled pretty much everywhere, is easy to manage for small DB's. I would not feel comfortable building a 'serious' software application with it though - but I might be biased there, because featurewise, I don't think MySQL is far behind. These days I use an abstraction layer that does away with all the SQL as well (ofcourse I CAN use it if I need to do something overly complex - though so far I have not needed that yet for webdevving), so I wouldn't know how the newest MySQL compares SQL-dialect wise.

What's your opinion (3, Interesting)

CastrTroy (595695) | more than 7 years ago | (#18786863)

I would like to know what slashdotters think is a good level of stuff to be putting into stored procedures. I've seen organizations where every single select query is put into a stored procedure. I've also seen places that avoid it like the plague. Personally I like to keep as much business logic as possible out of the database, but I realize it can speed up things considerably, so I use it where speed is critical. Is there any hope that stored procedures will become cross platform and work on all databases, at least to the level of SQL, so that there's some kind of standard, or do they just push us towards vendor lock-in?

It isn't about speed (4, Insightful)

Tony (765) | more than 7 years ago | (#18787195)

Stored procedures should be used to enforce data integrity and data access. The only "business logic" that should be there is the logic that applies to keeping your data whole, complete, and coherent.

Middle-tier application layers are great to help pull data together, present model-specific views of data (say, OO/R mapping, if that is what gives you a chubby), and provide update interfaces. They can even do a first pass at making sure the database will accept the data.

But.

The data logic should be stored firmly in the database logic itself, using stored procedures and triggers and rules. RDBMS engines are *designed* to keep your data in good shape. Use them in that fashion, and you will be plenty happy. Don't, and you risk losing data integrity, and you sacrifice security.

Re:What's your opinion (5, Insightful)

sammy baby (14909) | more than 7 years ago | (#18787221)

Oh man, talk about a can of worms.

On the one hand, folks who are trained to think in terms of data relationships and database development are liable to want to put as much as possible right in the database.

On the other hand... well, forget it. There is no other hand, it's more like a shifting mass of appendages, like Shiva [wikipedia.org] on an acid binge. The other extreme are people like David Heinemeier Hansson, who want to use database to store information and otherwise stay the hell out of the way - as I believe he put it once, to be as dumb as possible.

That may be a minority view, but the minority isn't exactly vanishingly small. eBay, for example, not only skips out on transactions (you know, the thing people like to beat up on MySQL for not having years ago), but doesn't even enforce referential integrity in the database. Stored procedures are right out. (warning: reference is a PDF [singleobject.org] .)

My own personal, under-informed and probably worthless view: I like enforce referential integrity where I can in the DB, and transactions are a good thing, but stored procedures are better left to maintenance and administration functions than anything involving business logic.

Re:What's your opinion (2, Interesting)

PRMan (959735) | more than 7 years ago | (#18788943)

I'll probably get completely trashed for this advice, but I find the following run counter to "the standards", but to be essential for good database performance.

  1. CRUD procedures are often bad for performance. People will send 100 individual queries to the server to do something that could be done in 1. This is common. Or, the middle tier will send 100 queries to the database to do the work of 1. Whatever. In either case, it's bad.

    As long as the Stored Procedures/Middle Tier are dealing with single records instead of what the application requires, it's written wrong. But I have seen many database admins that throw up CRUD procedures and then turn off all access to the database, leaving developers no choice. And requests to add additional Stored Procedures are denied because "there are 400 Stored Procedures already".

  2. Transactions are really bad for performance. Really bad. They should be avoided whenever possible. Any Stored Procedure call or single batch sent to most SQL Servers automatically have an implied transaction put around them anyway. Why do you want to hold up the entire database when someone accidentally pulls the network cable on your PC?

    Get in and get out as quickly as possible if you want to scale your application well. Make sure the Stored Procedure or Middle Tier does everything you want and does it using a single DB call, not 100 calls to CRUD procedures.

  3. Dynamic SQL has its place sometimes. And there's no substitute for it when it does. You have to know when it's necessary and it should be less than 1% of your total SQL calls. Don't make excuses to be lazy. But when you need variable numbers of columns based on what the user selected you can achieve massive performance gains over any other method.

Re:What's your opinion (1, Insightful)

Anonymous Coward | more than 7 years ago | (#18787497)

It's not a matter of opinion, its a design decision and to some degree a political one. The correct answer depends on your specific situation.

My approach is ususally to get a somewhat abstracted view of the data out of a stored procedure and then if there is heavyweight processesing of data required send it to an application for processing.

Putting logic in stored procedures makes it easy to fix problems and add new features or customizations without having to recompile source code in the field. However that same logic unless you take steps to encrypt stored procedures (This is supported in Oracle and some other systems) also gives away parts of your application, in some instances very vital parts.

For simple pulls of data I might use stored procedures for abstraction purposes or if its application specific a simple select statement generally works the best across different platforms provided you need to support several RDBMS systems.

Sometimes I don't use them for technical reasons such as when issuing updates that need to have their row counts checked for consistancy reasons. I ususally don't put that in stored procedures because of inconsistancies with various RDBMs and database drivers.

We have procedure converters which automatically convert the syntax of a common procedure syntax to various RDBMS platforms. Its quite a complex proposition in some cases but investments like these can pay off for large projects where procedure use can actually be a benefit for supporting multiple RDBMS targets.

On to my take on MySQL... I flat out refuse to support this platform. Its less than a joke, its **DANGEROUS**. Who needs DRI, sane quoting rules that screams injection attack or any reasonable notion of field validation. I'm sure like the text ODBC driver its great for a multitude of purposes. Just not mine..not ever in the forseeable future. I honestly do hope they continue to improve their product. One day I have no doubt it will be less of a joke.

If my MySQL is really the most popular open source database platform then IMHO its a sorry reflection on the community.

Re:What's your opinion (2, Insightful)

arivanov (12034) | more than 7 years ago | (#18787781)

Nothing.

I am fully aware that I would be flamed to death by ACID zealots, but I will again repeat nothing.

We are in 2007. Using non-OO fortran-like obscenities dated 1993 instead of looking at the calendar and getting a grip with reality gives you exactly that - nothing.

There is a reason why banks, govt, etc has been looking more and more at middleware layers on top of databases. You get the same you get with stored procedures while still having modern data and code representation.

Similarly, the little engine that could should have actually looked harder at making things that matter like isolation levels, foreign keys, etc work with all of their engines and not just one. Which they lost to Oracle anyway and which had no means of online backup (within the same license). They did one right thing at one point by introducing stored Java, C++ and Python procedures. It is a pity they did not expand on that and degraded into the fortran age instead.

Re:What's your opinion (1)

F1re (249002) | more than 7 years ago | (#18788141)

fortran? wtf? That doesn't make any sense. Did you mean cobol?

Re:What's your opinion (2, Insightful)

shatfield (199969) | more than 7 years ago | (#18788555)

My opinion? I'm glad you asked :-)

Rule #1 of Software Development in the 21st century:

Don't put any business logic into sprocs.

Why?

It's very simple really, and very logical.

1) Only CRUD operations should be in sprocs. These sprocs are designed to allow someone to create/read/update/delete data in the tables in the database without giving the user access to or knowledge of the underlying table structure. Use application roles (approles) to allow access to the tables. This keeps people from loading up your tables with Excel and walking all over your data. Also, this prevents SQL injection attacks, as your sprocs will choke all over them and nothing will get trampled. Any time that you put SQL statements in a form, you are opening yourself up to SQL injection attacks, unless you have specifically guarding against them, which requires even MORE work. Not giving your users knowledge of the underlying table structure makes it easy for you to change that structure, should you (or the DBA) need to.

2) The second that you place a business rule into a sproc, the users will want the rule changed. This means that you now have to go back to the sproc to update it. Why is this bad? Because sprocs are almost always under the control of the DBA. This sometimes isn't that bad, but most of the time there is a layer of politics involved between DBAs and Software Developers. What usually happens is that the dev will make the change to the business logic in the middle (or even worse, GUI) tier, thereby doing twice as much work. This happens way more often than you think it does.

3) Adding Database servers to a cluster isn't cheap. If you have a lot of users, and you have had to cluster your servers to accommodate more users, you'll have to add more database servers as you add more users. This is expensive, especially if you are using SQL Server or Oracle. Adding more middle-tier servers is much cheaper, being that you only need the underlying OS. Not to mention that there is a diminishing point of returns when adding database servers.

I hope that this makes sense to you, and helps you build arguments in the future for NOT allowing devs to put business logic into the sprocs!

-Steven

case study (1)

kpharmer (452893) | more than 7 years ago | (#18788915)

> I would like to know what slashdotters think is a good level of stuff to be putting into stored procedures.

Here's an example:

On a current project I've got a separate department of users who have written a reporting app in actuate against a db2 reporting database (a datamart). Along the way we've found that this team is not highly skilled in sql and often had to rewrite some of the sql to fit it into their tool. Note that this can be relatively complex sql - involving temp tables, etc - in order to show trends. The results have been queries that returned incorrect data, that scanned 4 *trillion* rows over 6 hours rather than 1 million in 5 seconds, etc, etc, etc.

The solution that we settled upon was to encapsulate all of their sql within stored procedures. These procedures then:
1. validated all arguments - to ensure that they didn't mix them up and ask for the wrong data
2. logged each call along with argument values, rows returned, and time to return
3. returned the result set along with some useful metadata
4. processed everything in a highly consistent way

Now, I don't typically use stored procedures heavily - and often prefer to encapsulate the physical data model in views to save time. But in this case the availability of this option was really a life-saver. And note that these stored procedures are also allowing us to more easily change the underlaying data model, measure and tune each query, maintain the queries, etc, etc.

All good stuff, though your mileage may vary.

Re:What's your opinion (1)

nuzak (959558) | more than 7 years ago | (#18788937)

> I would like to know what slashdotters think is a good level of stuff to be putting into stored procedures.

That's like asking how much abstraction you should use in an app. There's no one right answer.

Ideally, pretty much everything that constitutes an "API call" should be a SP. Single inserts, reads, deletes, updates, especially when they have to use a transaction to coordinate across tables. Anywhere where you're using a cursor but don't want it leaking out to have an indefinite lifetime. Anything that a user can do with different privileges than their own pretty much has to be a SP.

Bulk inserts usually can't leverage SP's easily, and using SP's just to give names to ad hoc queries isn't usually appropriate (it's ok in if you put it in another user's schema, it just doesn't belong in the same schema).

If you're using an ORM like hibernate, there's no point in putting basic CRUD operations into SP's. In fact, most of the time, you just use the DB as a storage layer then.

Re:What's your opinion (1)

Just Some Guy (3352) | more than 7 years ago | (#18788941)

We use stored procedures where multiple tables need to be updated at once, and where that series of updates will be called by more than one application.

Basically, we use most of the same logic you'd use when trying to decide whether to refactor a function. If a query, however complex, is only called from one place, then we leave it be. However, we don't want to have the same set of queries copied-and-pasted through 6 apps in 4 different languages if we can help it.

Stred pocedures (5, Informative)

karavelov (863935) | more than 7 years ago | (#18786873)

"The major commercial RDBMS vendors -- including Oracle, IBM, and Microsoft -- could point to this deficiency as reason enough to choose their proprietary sstems over MySQL or any other open source system, such as PostgreSQL..." They could not point this because PostgreSQL has stored procedures for decades... fully tested, stable, offering e bunch of languages for writing functions/triggers, including Perl, Python, Tcl and their own PlSQL that is very close to Oracle's variant.

Re:Stred pocedures (1)

Just Some Guy (3352) | more than 7 years ago | (#18787415)

reason enough to choose their proprietary sstems over MySQL or any other open source system, such as PostgreSQL...

I can only think that was a poorly written sentence, and what they really meant was:

reason enough to choose their proprietary sstems or any other open source system, such as PostgreSQL, over MySQL ...

The way you interpreted it makes no sense at all, even though you parsed it correctly. I have to assume they didn't really mean it that way.

Re:Stred pocedures (0)

Anonymous Coward | more than 7 years ago | (#18787425)

Actually, stored procedures are slightly different than functions and triggers, a procedure identifies a set of commands to run which does not return a result or operate in a query.

While the end result between select dothisstuff(); and call dothisstuff(); may be the same, the way the system goes about doing it may be different thanks to the overhead of a select query that has to get results. Of course, in the grand scheme of things, this difference is negligible.

Stored procedures BAD... story (0, Flamebait)

Travoltus (110240) | more than 7 years ago | (#18786911)

I got my JOB as a database programmer long ago while questioning this as a software TESTER.

I warned my boss NOT to keep stored procedures and switch to front ends instead, and just after we switched to ActiveX front ends (a mistake in and of itself) we found we needed to move from MS Sql Server to Oracle. Well, that migration was about as complex as dumping the database into an ascii file and reimporting it into the new server. The front ends didn't even cough. Back up in 2 hours.

Had we kept the stored procedures? Holy downtime and bug infestation, Batman!

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

Re:Stored procedures BAD... story (1)

zappepcs (820751) | more than 7 years ago | (#18787027)

I too run several databases as warehouses, and consequently I don't have a crushing need for blinding speed or speed increases due to stored procedures. Consequently all our database can be dropped off a cliff, hardware replaced and warehouse rebuilt to usability in less than a week, with NO data loss as a result of the architecture. Stored procedures would make that much more difficult, unless for some reason I completely don't understand anything about data warehousing... it could happen

Re:Stored procedures BAD... story (5, Insightful)

drmerope (771119) | more than 7 years ago | (#18787135)

The purpose behind stored procedures is not speed. It is about data integrity. Trigger functions are used to maintain additional coherence rules that are not easily expressed by unique, not null, referential integrity, etc. You place these rules in the database so that every consumer application of the database goes through the same logic--receives the benefits of the logic and enforces a coherent logic.

Re:Stored procedures BAD... story (1)

guruevi (827432) | more than 7 years ago | (#18787639)

Still, if you're developing a system only YOU as developer should have access to it. I have seen few such projects where that is the case and you still need Stored Procedures, Triggers or the like. It is nice to have if you (need to) let others have access to your database and you have a very archaic structure, but if your datastore is well thought out from the beginning and your programming not too complex, I would not fiddle around with it.

Re:Stored procedures BAD... story (2, Interesting)

gnuman99 (746007) | more than 7 years ago | (#18788725)

How about data mining? You need access to the data. Developers are NOT the only people with access to the database. If they are, why the hell use SQL? There are much faster databases.

Re:Stored procedures BAD... story (5, Insightful)

dedazo (737510) | more than 7 years ago | (#18787041)

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

Rest assured that if you were one of my developers you'd be out the door in no time. The application you're describing here cannot be anything north of trivial if you were able to just switch a connection string and all your messy inline SQL statements continued to run without any changes whatsoever (which I seriously doubt). Not everyone writes trivial applications.

There are reasons other than "fast" to using stored procedures. I've seen enough misuse of SPs, functions and triggers to fill up a book, but when done correctly they are simply superior to the alternative in just about every way.

Re:Stored procedures BAD... story (2, Informative)

RedElf (249078) | more than 7 years ago | (#18787189)

Mod parent up!

Stored procedures have added benefits such as additional security, and forcing application developers to implement database functionality properly, not sloppily.

Re:Stored procedures BAD... story (0, Redundant)

Shados (741919) | more than 7 years ago | (#18787429)

I would fire that guy in no time too. However, I'd probably fire YOU too. If you think the only way around stored procedures is "messy inline SQL", you haven't done anything beyond trivial, OR you worked for a large corporation where the software architects haven't looked anywhere beyond what they originally learned in over a decade.

there are douzans of ways to use dynamic SQL in maintainable, efficient ways. Stored procedures have quite a few advantages that can't be brushed off either, so it really comes to the people in charge to make an enlightened decision, but its NOT as simple as "SP good, everything else bad!". Both sides of the spectrums have their good points, and everywhere in between (a mix of both methods).

Re:Stored procedures BAD... story (5, Funny)

Anonymous Coward | more than 7 years ago | (#18787621)

And if I were a manager, I'd fire ALL OF YOU for wasting time on a /. DBA pissing match instead of working.

Re:Stored procedures BAD... story (1)

dedazo (737510) | more than 7 years ago | (#18788095)

You know, I used to think that as well. Specifically when dealing with parametrized queries that require optional parameters and customized ORDER BY statments depending on context and things like that. I've never been very good with SQL dialects. I think it's the fact that they're not really programming languages or whatever, but I've never been comfortable with them. So I used to think a little bit like you in the sense that even though I prefered SPs, there would be times when I would have no choice but to use inline SQL.

And then a few years ago I had a developer in one of my teams that was a freakin' SQL guru. I mean, this guy was just fantastic. He showed us how to do the most amazing things with SPs and functions without sacrificing speed, integrity or maintainability. I'm really grateful that I had the (humbling!) experience of working with him for a year, because I learned a hell of a lot about a topic that previously I had found mostly fastidious. Trust me that whetever we were paying him at the time it wasn't nearly enough.

I'll give you an example where inline SQL is not only problematic but simply just flat out impossible. At a previous project I was in charge of a rather large application for a financial services company that shall not be named at this time. Aside from actually designing and writing the thing, part of the mandate was to be able to pass a SOX audit with flying colors. The database for this app was secured so that the confgured identity we were using only had permission to execute SPs and views. No direct table access. This enables the architect (moi) to assert that there is a clear audit trail in the form of source control for the SPs and the database changelogs that can tell them (the auditors) at what point who decided that writing or reading from/to TableX or TableY was a good idea. In a scenario like this, not only would inline SQL simply fail for lack of permissions, but it would cause me a few week's delay and a good chunk of the client's budget (and likely my job thereafter) to fix because I'd fail the audit.

The world of enterprise corporate software development can be a bitch, but it pays very well =)

Re:Stored procedures BAD... story (1)

Cyberax (705495) | more than 7 years ago | (#18787053)

SP are OK for complex tasks like building reports or complex searches. You won't be able to do it DB-independently, anyway. And the speed difference between SP and processing on client can often be measured in orders of magnitude.

Re:Stored procedures BAD... story (4, Insightful)

Tet (2721) | more than 7 years ago | (#18787055)

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

I'm glad I don't work for you, then. Stored procedures are a tool, like any other, that come with their own set of pros and cons. In some situations, the ability to quickly migrate to an alternate database outweighs the benefits that stored procedures may provide. In other situations, it doesn't. The decision to use them or not should be based solely on business requirements, rather than the irrational hatred you seem to be using as a guide.

Re:Stored procedures BAD... story (1)

shaitand (626655) | more than 7 years ago | (#18787345)

'In some situations, the ability to quickly migrate to an alternate database outweighs the benefits that stored procedures may provide. In other situations, it doesn't.'

Doubtful. There are no shortage of individuals who find vendor lock to the worst possible scenario. There is nothing irrational about avoiding technology until it becomes standardized. Now that open applications implement them, the open applications will collaborate and develop a standard. Then there will be a certain level of standards compliant SPs you can use and still not be locked into a vendor. There will still no doubt be proprietary choices as well, the young and stupid will use them for their whizzbang features, the wise will avoid them until they become ubiquitous.

Re:Stored procedures BAD... story (2, Interesting)

Tet (2721) | more than 7 years ago | (#18788039)

Now that open applications implement them, the open applications will collaborate and develop a standard. Then there will be a certain level of standards compliant SPs you can use and still not be locked into a vendor.

Demonstrably false. The ANSI standard for stored procedures already exists. MySQL has merely implemented this standard. You can port stored procedures to any other database that supports the standard (which admittedly didn't give you a lot of choice last time I looked). PostgreSQL initially took the "be compatible with Oracle route" with PL/pgSQL, so moving stored procedures between the two would be easy. They've since added the ability to have SPs in alternative languages, so it should be easy to add a PL/ANSI to PostgreSQL, assuming someone hasn't already done so (I haven't checked).

Re:Stored procedures BAD... story (1)

shaitand (626655) | more than 7 years ago | (#18788599)

'Demonstrably false.'

Okay, there is a standard... being adopted by open programs that will collaborate to implement that standard. This means choices and the ability to avoid vendor lock. I wasn't aware of the existing (unimplemented) standard but that doesn't change anything else I said. It is usage by a number of open, robust, and secure applications that makes it safe to adopt technology. Having the backing of a standards organization is just a bonus.

Re:Stored procedures BAD... story (0)

Anonymous Coward | more than 7 years ago | (#18787057)

I'd fire the department head that hired your ass. Then I'd fire you. Then I'd hire a real engineer. :)

Re:Stored procedures BAD... story (2, Insightful)

DavidpFitz (136265) | more than 7 years ago | (#18787081)

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

And you'd get sued shortly thereafter for unfair dismissal ... some manager you are proving yourself to be! A more pragmatic person in a management position would know that you use the right tool for the right job.

There are plenty of good reasons to use stored procedures in a database - they simply wouldn't exist in every serious database if they were not useful. Speed isn't the only reason to use a stored procedure, they can also make some inter-application integration much easier and allow someone in control of the data model to abstract the data collection routines from the underlying data structure - which can be very, very useful. I don't want some Java programmer thinking he knows the best way to get at data in Oracle - using a stored procedure to pass complex data structures back allows tweaking of the select statements, indexes etc... without any testing required of the app layer.

D.

Re:Stored procedures BAD... story (0, Offtopic)

sfkaplan (1004665) | more than 7 years ago | (#18787537)

>> As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."
>
> And you'd get sued shortly thereafter for unfair dismissal

Ummmm, what? Do a little research on our `at-will employment' system. There are only a few reasons that an employer may *not* use in firing you (e.g. member of a protected group like minorities, women, etc.). Otherwise, your employer is welcome to fire you because he thinks your voice is too high, that pick you pick your nose too much, or that you are a Dallas Cowboys fan. A manager could fire you for using stored procedures, and the `unfairness' of his evaluation doesn't even begin to point towards a law suit.

That said, I agree that any manager who categorically rejects the use of a useful tool without consideration of the context is a twit for whom I would not want to work either.

Re:Stored procedures BAD... story (0, Offtopic)

MBGMorden (803437) | more than 7 years ago | (#18787677)

And you'd get sued shortly thereafter for unfair dismissal ...
His statement was stupid/arrogant to say the least, but any manager that says "I hate stored procedures, don't use them!" is on perfectly fine ground regarding unfair dismissal if someone uses them anyways and he then fires them.

This is beside the fact that many, many states are "right to work" states now. Translated: in many states there's no such thing as unfair dismissal. They can fire you for ANY reason. You bought a standard transmission car and your manager thinks good programmers only drive automatics? Fired, and legally.

Re:Stored procedures BAD... story (1)

Tim Browse (9263) | more than 7 years ago | (#18787917)

This is beside the fact that many, many states are "right to work" states now.

Also, I hear tell that some people work in places that are not 'states' at all!

(And I'm not talking about D.C.)

Stored procedures and data integrity (2, Insightful)

Tony (765) | more than 7 years ago | (#18787093)

The point of stored procedures is not to make access easier, or anything like that. The point from the RDBMS perspective is to keep your data coherent. Data integrity is THE MOST IMPORTANT role of a database. That's why 3rd normal form is important. That's why stored procedures and triggers and rules are important.

As a database engineer, I would *definitely* fire anyone who didn't use these tools to maintain data integrity.

Re:Stored procedures and data integrity (1)

CastrTroy (595695) | more than 7 years ago | (#18787253)

That's what they want you to think. Although I'll agree that data integrity is important, it's not the most important thing to everyone. RDBMS's are a very generic tool. And that is the reason that they are as complex as they are. They're basically the be-all-and-end-all data storage for programs. Apart from flat files, major RDBMS's are all we have, and it doesn't seem like there's much room for anything in between. Data Integrity is the most important part of many database dependant apps. However, I'm sure there's a lot of people out there who trade some data integrity for other functions.

Re:Stored procedures and data integrity (1)

jaydonnell (648194) | more than 7 years ago | (#18787987)

"The point from the RDBMS perspective is to keep your data coherent. Data integrity is THE MOST IMPORTANT role of a database. ... . That's why stored procedures and triggers and rules are important."

I agree with you in general, but isn't it just as easy to ensure data integrity via application logic as it is via triggers and stored procedures? Assuming you are only accessing the db from a single code base, I'd argue that it's easier to write/debug/test/version control/maintain ruby/python/php/java than it is to write t-sql or whatever. I know that my assumption is a big one, and that it's not true for many, but at the same time it is applicable to many people. Myself included.

Re:Stored procedures and data integrity (1)

LurkerXXX (667952) | more than 7 years ago | (#18788557)

It is a big assumption. How do you know at no time in the future another application won't be trying to access the database directly?

I write/debug/test/and version control my stored procedures just fine, thank you very much.

Re:Stored procedures and data integrity (1)

Electrum (94638) | more than 7 years ago | (#18788681)

I agree with you in general, but isn't it just as easy to ensure data integrity via application logic as it is via triggers and stored procedures?

No. Database constraints ensure data integrity. Application checks can only hope to get it right. Suppose you have a table:

    CREATE TABLE foo (x int NOT NULL CHECK (x BETWEEN 1 AND 5));

With that constraint in place, you guaranteed the column contains 1-5. Even if your application is small, you may update that table in multiple places. Are your checks correct in all of those places? What if the constraint needs to change later? What if someone runs some SQL manually and accidentally uses the wrong value?

Re:Stored procedures BAD... story (0)

Anonymous Coward | more than 7 years ago | (#18787113)

"As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster.""
This is the dumbest thing i read here for a while. Good luck with your manager job, you sure ain't a techie. Stored procedures are great vs dynamic sql in many ways (speed, security etc).Stored procedures are not always right but your comment is just stupid.

Re:Stored procedures BAD... story (1)

CastrTroy (595695) | more than 7 years ago | (#18787127)

In some instances, it really can speed things up. I would recommend that they not be used, except where speed is actually an issue. Most of the time you don't need it, and it definitely shouldn't be used for every query, but there are situations where it is necessary to keep the application at a usable speed.

Re:Stored procedures BAD... story (0)

Anonymous Coward | more than 7 years ago | (#18787133)

And you would never be a manager in the company I work for or any of the previous companies for your short sighted behavior.

1. Dismissing technology out of hand for the simple reason of portability ignores significant advantages of what stored procedures offer. Instead of throwing more hardware at a problem, the work can be split among the frontend and backend.

2. You would have been fired also for the significant downtime you experienced in the migration.

If you are migrating platforms more than once in a blue moon, you have bigger issues.

Re:Stored procedures BAD... story (3, Insightful)

PCM2 (4486) | more than 7 years ago | (#18787141)

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

So answer me this: Now that you've made the investment to move to Oracle, can you reasonably foresee moving back to SQL Server? Seems unlikely to me. So if you'd chosen the right tool for the job in the first place, the migration wouldn't have been a problem and you would have been free to use other right tools where appropriate, e.g. stored procedures -- right?

Really, stored procedures have their place. Oracle tends to over-sell them, but to ignore them completely seems like one more step backward in the last n years of best practices and lessons learned.

Re:Stored procedures BAD... story (1)

LurkerXXX (667952) | more than 7 years ago | (#18787251)

And I would fire any moron who refused to use them. There are numerous reasons to use them. I find it scary that anyone employs you as a manager. You must be one of those PHBs from Dilbert.

Re:Stored procedures BAD... story (2)

nuzak (959558) | more than 7 years ago | (#18787339)

> As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

And your boss would fire you pretty fast. But I doubt you've ever fired anyone over it or even threatened it. Pretty much everyone who pulls out this "I would fire anyone who xxx" gem is full of shit, has never managed anyone, never should, and thankfully never will.

Re:Stored procedures BAD... story (2, Insightful)

RedElf (249078) | more than 7 years ago | (#18787413)

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."
As a database engineer I would refuse to work for such an incompetant manager who clearly doesn't understand the technology he is managing, but chooses to micromanage the implementation.

Re:Stored procedures BAD... story (1)

Chicken04GTO (957041) | more than 7 years ago | (#18787469)

well, you certainly sound like a manager.

You decide an entire methodology for development is stupid because of one bad experience and you would fire anyone who dare challenge your AUTHORITAY!

Re:Stored procedures BAD... story (0)

Anonymous Coward | more than 7 years ago | (#18787505)

I don't get this prevalent urge to be able to "easily switch to another RDBMS". Each RDBMS has its own quirks and moer importantly its own strengths and weaknesses in terms of performance and available features. If you use MySQL and need performance you'll probably do without transactions and referential integrity, do atomic single table updates and try to avoid deletes. If you use Oracle you may try to avoid autocommitting minimal updates and instead collect them into a bit larger transactions. How can you then easily switch from one to the other? Oracle is wasted if you run it with a database designed for mysql and MySQL lacks the features to run a database designed for oracle.

Pick a database suitable for your task and use it as optimally as possible; easy portability be damned.

Re:Stored procedures BAD... story (1)

doom (14564) | more than 7 years ago | (#18787757)

I don't get this prevalent urge to be able to "easily switch to another RDBMS". Each RDBMS has its own quirks and moer importantly its own strengths and weaknesses in terms of performance and available features. If you use MySQL and need performance you'll probably do without transactions and referential integrity, do atomic single table updates and try to avoid deletes. If you use Oracle you may try to avoid autocommitting minimal updates and instead collect them into a bit larger transactions. How can you then easily switch from one to the other? Oracle is wasted if you run it with a database designed for mysql and MySQL lacks the features to run a database designed for oracle.

Pick a database suitable for your task and use it as optimally as possible; easy portability be damned.

A few scenarios where on might worry about portability: (1) you're working on a system that you're hoping to get to work with whatever database the user has access to already, (2) you've gotten tired of being raped by Oracle's licensing, and you'd like to hold the door open to switching to another database, if only as a bargaining maneuver to push for discounts/reduced prices.

On the other hand, if you actually trust the culture of the developers working on the database you're interested in, you might very well choose to embrace the special features of that database, because the need to switch to another one at some point in the future seems very unlikely to occur.

(And if you ask me, this means "Postgresql". Trusting to Mysql culture does not sound like a great move to me.)

Re:Stored procedures BAD... story (1)

barjam (37372) | more than 7 years ago | (#18788709)

I was working for a startup in 2002... we had to cut costs anywhere we could so we dumped Oracle licensing. Me and another guy were able to convert the entire website and all our internal applications over the course of a day.

We were using EJBs without stored procedures and the only reason it took as long as it did was because some of the more exotic queries we were using at the time.

Re:Stored procedures BAD... story (0)

Anonymous Coward | more than 7 years ago | (#18787559)

OK, first it sounds like you need better planning from management. You don't change the front ends, then change the back-ends, do it all at the same time. Also, if your queries worked without change from one RDB to another, they weren't that complicated and would have survived some search-and-replace to make the transition.

Here's why stored procs are bad, it includes some information like stored procs don't even run faster:
http://weblogs.asp.net/fbouma/archive/2003/11/18/3 8178.aspx [asp.net]

And my response to that article

Ad-hoc SQL Script is brittle
"changes to a relational model will have always an impact on the application". so you could update your procs which are all in one place, or search for and hope you changed all of your ad-hoc queries. Either way you have to change something, the only benefit is not involving a DBA.

Security
Instead of applying security through stored procs, do it through roles. Oh yeah that's basically the same thing too? Oh I didn't think of that. What did you say about Parameterized queries? Totally secure? then this guy's an idiot.
http://dotnetjunkies.com/WebLog/chris.taylor/archi ve/2004/10/13/28370.aspx [dotnetjunkies.com]

Performance
So, procs have their execution plans cached like dynamic SQL? Great, that's the third thing that's basically the same. Oh yeah and stored procs are bad because of cursors. (wtf?) And a database with 100 tables, with 7 fields per table. "You can't create stored procedures for all possible combinations either, that would require 100*7! procedures." Yeah, I can do it in SQL and C# and ASP 3.0 and probably some other stuff. Who writes their own add/update/delete procs these days?

So his argument is Don't use stored procs because dynamic SQL is exactly the same. And disregard SQL injection, that's not important, it's much better to spend time validating all of your input. And apparently his DBA doesn't like him. And he changes his data model far too often, he needs better design discipline.

The only part of this I agree with is the comment:
I would hate to run into this type of code personally. I mean, you are basically writing a stored procedure at that point and hard coding it into your app.

Re:Stored procedures BAD... story (1)

Hemogoblin (982564) | more than 7 years ago | (#18787647)

Do you happen to be a government employee? [worsethanfailure.com]

"Our security consultant uninstalled all the stored procedures. They were too insecure."
"Uh, I can reinstall the procedures, I have the SQL Server CD with me."
"Get OUT."

Re:Stored procedures BAD... story (1)

Bob9113 (14996) | more than 7 years ago | (#18788863)

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

It is possible, and in some cases effective, to split a system's logic into presentation logic, business logic, and data access logic. If you do that, it is possible to write a good three tier system that has some of the logic in the database (I'm referring to the data access logic - for the slow folks in the crowd).

I'm not saying that every database backed system should be this way. Nor even that every database backed system will benefit from it. But there are some systems that can benefit from it. This is particularly true when the ability to migrate between RDBMSs is a very low priority (or claimed to be a non-priority), and the decoupling of the data access logic from the business logic is a very high priority.

Rules of thumb are nice. Absolute laws are the enemy of pragmatic system design.

Deciding if MySQL is an option (3, Insightful)

moore.dustin (942289) | more than 7 years ago | (#18786965)

if((estimated_rows_in_table($table) < 100000)) {
return true;
}
else {
return false;
}

Re:Deciding if MySQL is an option (5, Funny)

gusx (898415) | more than 7 years ago | (#18787019)

How about...

return (estimated_rows_in_table($table) < 100000);

Re:Deciding if MySQL is an option (0)

Anonymous Coward | more than 7 years ago | (#18788107)

How about dropping the paranthesis and using a constant?

return estimated_rows_in_table($table) < MAX_ROWS;

Re:Deciding if MySQL is an option (1)

suggsjc (726146) | more than 7 years ago | (#18788601)

I'm a little confused. I thought that this post should be modded insightful for offering shorter more efficient code, whereas the GP was the one actually making the joke (although there may be some truth in it).

Re:Deciding if MySQL is an option (1)

ak3ldama (554026) | more than 7 years ago | (#18788789)

Welcome to the modern era of programmers taught by 'How to code in C like it was a different language for Dummies!' With every student that goes to the university now and learns C# and Java primarily it will only get worse. There is a certain level of syntactic sugar that does make code easier to read but you can guarantee that committees will write up coding standards documents that will bloat your code like a beached whale.

Re:Deciding if MySQL is an option (4, Insightful)

coyote-san (38515) | more than 7 years ago | (#18787699)

Even that is generous. If you want to use a database as a simple store (and if they've fixed the tendency to hiccup and corrupt the database), MySQL may be acceptable.

If you want a DATABASE it's not even on the table, no pun intended. The whole point of a RDBMS is that it isn't a simple store, it's a mechanism for ensuring that your collection of data is always in a sane state even if you've brought in some clueless interns for the summer or you have a disgruntled employee. Or even if you just have two developers (or one developer with a poor memory).

ACID and referential integrity are two items that are absolute requirements. Calling something without these features a 'relational database' puts you in the same pointy-hair territory as Dilbert's boss saying that he heard the 'mauve' databases were best.

Attribute constraints, triggers and stored procedures aren't as necessary, but they're still extremely powerful ways of ensuring the sanity of your data. Use a trigger to update a 'updated_on' field, don't just trust the developer to always update it. Use an attribute constraint to ensure that you color field is always 'R', 'G' or 'B' (or just use referential integrity to point to a color table).

Finally, to address a question asked elsewhere stored procedures are extremely powerful security tools. By now everyone should know that using string concatenation to prepare queries is a Very Bad Idea. A potentially "don't let the door hit you in the butt on your way out" Bad Idea. Prepared statements are better, but how can you enforce it?

Stored procedures give you an alternative. Drop INSERT and UPDATE rights and force everything to go through a corresponding stored procedure. It's a little more work but it should eliminate any risk of SQL exploits. (It's not a 100% guarantee since you can't eliminate the risk that the database itself can be compromised by carefully selected parameters.)

Re:Deciding if MySQL is an option (1)

markhahn (122033) | more than 7 years ago | (#18788323)

your examples are telling. mysql provides a nice builtin mechanism for handling several: it will auto-update timestamp fields (updated_on), and the builtin enum type is far nicer than attribute constraints...

I don't follow your argument on security - using the values() syntax is an easy way to avoid concatenation. the premise of trusting the DB to handle all security seems terribly mistaken to me.

Glad some database noobs modded this up (0)

Anonymous Coward | more than 7 years ago | (#18787943)

So I can laugh at them...
 
    I run a huge mysql database with billions of table rows. Such an ignorant comment suggests you know about indexes and how to optimize databases in general. My select speeds on a billion row table in Mysql? less than 1 tenths of a second returning 10000 rows. Get back to me when your done fiddling around with your wordpress and phpbbs. Thanks!

The management

Re:Deciding if MySQL is an option (2, Informative)

LordLucless (582312) | more than 7 years ago | (#18788045)

Actually, if all you want is a big table, MySQL is probably a good choice. It's generally very fast. However, as soon as you start getting a complex database schema, you're probably starting to look at other RDBMSs. MySQL provides a lot of advanced features with InnoDB, but then, InnoDB has just been bought by their biggest competitor. If you're needing any of those features (foreign keys, transactions, etc), then I'd be going with PostGreSQL, because, really, I don't think the InnoDB team is going to be releasing any fixes/upgrades to their MySQL engine any time soon.

Re:Deciding if MySQL is an option (4, Informative)

consumer (9588) | more than 7 years ago | (#18788079)

I'm currently looking at a table on a production MySQL db with 176 million rows in it. It has no problems at all, other than being a huge amount of data. Your ideas about MySQL's limitations are not based on fact.

Re:Deciding if MySQL is an option (3, Informative)

kpharmer (452893) | more than 7 years ago | (#18788771)

> I'm currently looking at a table on a production MySQL db with 176 million rows in it. It has no problems at all,
> other than being a huge amount of data. Your ideas about MySQL's limitations are not based on fact.

Without partitioning you face the choice of selecting data only via a btree index (only typically works if you need to select less than 3% of the data) or scanning it all. With a more typical alternative (certainly db2, oracle, etc) you can partition the data. This can result in 10:1 differences in performance between mysql & db2/oracle for typical reporting queries.

Without query parallelism you're single-threading all of your queries, and unable to take advance of those extra processors. Since oracle/db2 get near-linear performance benefits from parallelism, you're again suffering a 4:1 performance penalty on a four-way smp.

Without a robust optimizer you will choke on complex queries against even moderate data - so a query that joins a dozen tables together will inevitately go into the ditch with poorly chosen nested-loop joins. Performance penalty? could easily be 100:1 in some situations.

Of course, this doesn't mean that you can't keep 200 million rows in msyql - you certainly can. Just don't plan to get the same kind of performance out of a lot of typically complex queries against it - that you would get from oracle/db2/informix or even sql server. Not unless you spend 20x as much on the hardware anyway.

> Your ideas about MySQL's limitations are not based on fact.
Unfortunately, your ideas about mysql's capabilities are based upon insufficient experience

Re:Deciding if MySQL is an option (0)

Anonymous Coward | more than 7 years ago | (#18788099)

Huh? You do realize that millions of records in a simple table is exactly where MySQL shines, don't you?

"any other open source system"? (0)

Anonymous Coward | more than 7 years ago | (#18787317)

> The major commercial RDBMS vendors -- including Oracle, IBM, and Microsoft -- could point to this deficiency as reason enough to choose their proprietary systems over MySQL or any other open source system, such as PostgreSQL.

What does MySQL's lack of features have to any other open source system to do?

"Oh. Since MySQL doesn't have stored procedures, you shouldn't use PostgreSQL!"

Good one Ploppy (2, Funny)

Tim Browse (9263) | more than 7 years ago | (#18787993)

it was nice to see him poke some fun at the database heavyweight, in his choice of sample code to demonstrate the my_replace() function:
my_replace( 'We love the Oracle server', 'Oracle', 'MySQL').

The long Winter evenings must just fly by.

PostgreSQL is not the one-size-fits-all (0, Troll)

Karaman (873136) | more than 7 years ago | (#18788021)

Girls, please, stop the fud! PostgreSQL is far from being the best and the one-size-fits-all RDBMS! It still has its issues! Yes, it has many advanced and modern features, but why do you point it as a cure for MySQL! It is a very good product as it is, and a bad replacement for other RDBMS!

Pain in the ass to debug (1)

not already in use (972294) | more than 7 years ago | (#18788575)

While they are certainly useful in certain situations, they are a pain in the ass to debug. It would be nice if they could add some sort of vendor specific command - anything - that would allow me to see output in a console while a stored procedure/function is running. They are useful at times, but i dread writing them.
Load More Comments
Slashdot Login

Need an Account?

Forgot your password?