MySQL Stored Procedure Programming

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 MySQL Stored Procedure Programming author Guy Harrison and Steven Feuerstein pages 636 publisher O'Reilly Media rating 9 reviewer Michael J. Ross ISBN 0596100892 summary A comprehensive guide to developing MySQL stored procedures, functions, and triggers.

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.

Comments

MySQL aren't trustworthy

[ShieldW0lf]

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.

Re:MySQL aren't trustworthy

[bahwi]

"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

[Anonymous Coward]

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

Re:MySQL aren't trustworthy

[Overly Critical Guy]

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

[Anonymous Coward]

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

[XorNand]

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

[morgan_greywolf]

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

[CoughDropAddict]

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.

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

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

Re:MySQL aren't trustworthy

[LizardKing]

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.

Re:MySQL aren't trustworthy

[Blakey Rat]

Even worse is license confusion.

MySQL's dual-license scheme is about the most complicated thing on earth. Do I have to pay a licensing fee for this install? Who knows! Maybe a highly-paid lawyer, but no mere mortal can figure it out.

I dunno, maybe this was a few years ago, but it was definitely a huge problem then. Since the DB had to be (basically) embedded, and was going to stay small and mostly static, I actually opted for SQLite instead. No confusion about SQLite's license.

Re:MySQL aren't trustworthy

[zapov]

Use

CREATE OR REPLACE FUNCTION answers_get(
in _review_id int, out positive text
, out negative text
) RETURNS setof record AS $$
DECLARE
rec record;
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;

Re:MySQL aren't trustworthy

[ShieldW0lf]

My point is that the people behind MySQL have no problem telling you that their software is suitable for a purpose even when it isn't.

They've come a long way, but unless I'm misinformed, they've still got problems with automatic data conversion breaking data integrity rules.

It wasn't that long ago they were pushing the worst possible practices as gospel to people who didn't know any better, all while labouring to catch up with the Jones because they knew they were behind.

They are deceitful.

Put it this way, which is more useful as a means of conveyance, a standard car that you don't know how to drive or a rocket car with one button, no steering and no breaks?

MySQL is a suitable tool for servicing trivial data at very high speeds. I don't knock that. My issue lies in the real fact that the team behind MySQL does not have a history of being forthright about the limitations of what they've built.

I don't think much of the parallels their corporate structure and licensing has with the whole Project Mayo/DivX debacle, either. It's just the sort of thing that leaves a sour taste in your mouth.

Re:MySQL aren't trustworthy

[Sylver Dragon]

I've not worked with MySQL outside the hobbyist PHP/MySQL personal website, so I really didn't get into the guts of MySQL.
Looking at those links...Holy Hell! Someone willing uses this thing for anything more than the PHP/MySQL home page? I can see where one could forgive the lack of foreign keys. The complaints listed against them are mostly valid. Still, outside of very basic applications the extra checks on the program logic are well worth the extra overhead. This could put the database team in a situation where they may have to waste hours unraveling the after effects of a bug in an update statement; where a simple foreign key constraint would have stopped the bug cold. Most business's databases are mission critical these days, suck it up and buy a better server for it.
Then, for the sake of speed, they have sacrificed transactional integrity. Fark that! On a small, non-critical database, sure. I don't work with any of those.

Re:MySQL aren't trustworthy

[swusr]

Since PostgreSQL 8.1:

CREATE OR REPLACE FUNCTION
answers_get(_review_id INT, OUT positive TEXT, OUT negative TEXT)
RETURNS SETOF RECORD AS $$
SELECT POSITIVE, NEGATIVE
FROM ANSWERS
WHERE REVIEW = $1
ORDER BY ID DESC
$$ LANGUAGE SQL;

Re:MySQL aren't trustworthy

[Anonymous Coward]

You know what it is? It's lack of trust.

I'm not going to argue with you there but it means nothing to me considering its main competitors (oracle, ms) are just as trustworthy if not less so.

Re:MySQL aren't trustworthy

[Anonymous Coward]

"Only recently has it become superior. "

Boss: Dickens! We need an RDBMS, draw up a matrix of features for MySQL and Post...gres, ahh, whatever it's called!

Me: Right away sir, I'll get the latest features!

Boss: No, dammit! Compare some old versions, I want this to be accurate!

Re:MySQL aren't trustworthy

[JAlexoi]

Really sorry, but I find PostgreSQL documentation easier to read through than MySQL's.
If you want really archaic documentation try FirebirdSQL/Interbase.

Re:MySQL aren't trustworthy

[lawpoop]

"There are so many problems with FOREIGN KEYs that we don't know where to start."

These problems with foreign keys -- are they encountered when you are trying to develop them in the database you are making, or when a user actually uses them? I suspect that the developers were talking about the former.

"Gosh, it's so hard to program these damn foreign keys. You know what? We really don't need them, after all. Here's why..."

Re:MySQL aren't trustworthy

[cyphercell]

Then you, as someone that knows databases and has used MySQL without issue, trusts the hype and then there's no reason for you not to say that MySQL is a decent database. Making you look like an ass, because the reality of the situation is that MySQL is/was so damned immature that it's hard to believe. I've studied MySQL a little more in depth and I think you can set things up (mostly) properly, but the default/recommended install is terrible and has been for years. I wouldn't discredit someone that was maybe MySQL certified AND understands the nature of the beast, but I would be very leery of anyone's opinion whose primary function isn't as a DBA.

Re:MySQL aren't trustworthy

[umeboshi]

I suspected the same thing, as I have been using foreign keys for a long time in postgresql now, and I have never come across those problems. I read the section right above this and went pale. I was told that mysql got support for foreign keys a couple of years ago, but I now understand what they consider "support" for foreign keys.

The real kicker for me was this sentence.

The only nice aspect of FOREIGN KEY is that it gives ODBC and some other client programs the ability to see how a table is connected and to use this to show connection diagrams and to help in building applicatons. I always believed that relational integrity was the major "nice aspect" of foreign keys. I guess this really sets the record straight, and gives some of the best insight into the capabilities of the developers in making a good relational database.

Re:MySQL aren't trustworthy

[godefroi]

For comparison, in MSSQL:

create procedure answers_get (@_review_id int) as
select
  positive,
  negative
from
  answers
where
  review = @_review_id
order by
  [id] desc

Re:MySQL aren't trustworthy

[putaro]

Hmmm...I built my very first database application on top of Postgresql back in about 1997/98. I even wound up writing a JDBC driver to talk to Postgres to get things going. We ditched Postgres after getting funded for Oracle for both performance and credibility reasons (back in 1998 basing your company on Open Source was not an attractive thing for VC's) but I never had a problem figuring out things and I don't think I ever asked a question on a mailing list or forum.

However, I have found over the years that one of my key talents is being able to read documentation and figure things out so maybe the documentation was really hard for other people.

MySQL vs Firebird

[BuR4N]

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

[PCM2]

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

[otis wildflower]

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

[Anonymous Coward]

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

[LurkerXXX]

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

[gnuman99]

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

[aled]

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

[Anonymous Coward]

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.

Re:MySQL vs Firebird

[kpharmer]

> 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

[newt0311]

in postgres:

\d <table_name>

pretty easy.

Re:MySQL vs Firebird

[Anonymous Coward]

"can be found in the Ahbrams tank."

Is that the same Ahbrams tank that was too heavy to go across half the bridges in Europe? The Ahbrams tank with the hatches too small for a man wearing standard Army issue winter clothing? The Ahbrams tank that was going through an air cleaner a day in Kuwait? That Ahbrams tank?

Just askin'

Re:MySQL vs Firebird

[turbidostato]

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

Yes. But here the problem seems to be to decide what direction the water goes.

I'll rework your question: If you expect to develop in any reasonable future a database-driven app that might need proper RBDM functionality -- which arguably goes for most of the people earning a life on the "informatics thingie" -- why would you choose to thrash away time learning a tool you will have to abandon in the near future when the "proper" tool (ie: postgresql or firebird) will serve you in your hard tasks as well as in your easier ones with any drawback?

Re:MySQL vs Firebird

[PCM2]

Fair enough. But I would answer it this way: Anybody who spends a lot of time fretting over whether to learn Java or C# is missing the point. If you understand programming, it's not hard to learn both. And while you're at it, you should also pick up C and C++, in addition to Python and/or Perl. And whatever else you feel like. Experiment with things like functional languages while you're at it. These are all just tools. Some university-level computer science programs don't teach any programming languages. The language you use to implement your ideas is a secondary concern.

Similarly, the field of database design is choked with so-called DBAs who learned their trade using one tool to the exclusion of all others. Most of them can be forgiven for this, because that's how many database classes and books approach the problem. You're not taught databases ... you're taught SQL. And since a lot of SQL is nonstandard, that means you learn PL/SQL or T-SQL. I'd argue that this is the wrong way to do it. There's a lot of science behind relational databases dating back decades, and DBAs too often ignore a lot of the best practices because they don't really understand relational theory and how databases work.

So back to your point: Sure, anybody who really understood database theory might have such a strong adverse reaction to MySQL that they'd never use it for anything. Then again, if they really know what they're doing that well, then they'll probably understand the application they're trying to build well enough to know if it's likely to grow beyond the capabilities of MySQL. If it's not likely to do so, then they might make the conscious decision to use the more popular tool because of its wide availability, strong community and ready support. To me, an Oracle DBA who doesn't also learn how to use MySQL is just being narrow-minded. (He should also understand the drawbacks and advantages of DB2 and MS-SQL, too, whether or not he ever intends to contract out to work on them.)

Or, to put it another way: When the only tool you have is a hammer, every job starts to look like a nail. And treating them that way might work, superficially, but there are all kinds of nails out there. It's not always about bringing the heaviest hammer you can find.

Re:MySQL vs Firebird

[shutdown -p now]

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.

The bigger problem is that there's no such thing as a "database agnostic application". Even setting the differences in SQL syntax aside, the semantics differ as well, sometimes a lot, so JDBC won't save you either.

Re:MySQL vs Firebird

[ceeam]

The only reason to go with MySQL instead of FB is that you have to grok different kinds of transactions in Firebird whereas in MySQL you can just push your luck. MySQL will (almost) never show you something about conflicts or deadlocks. Yes, you will end up with mess in your data but at least your users won't see an exception/error thrown at them, nice, eh? All other things - like performance, capacity etc - Firebird beats MySQL or is at least on par.

PS: Oh, and Firebird used to have slightly less effective transport protocol to remote hosts (over TCP/IP) but it has been worked on and I have no fresh data on present state of it. MySQL just sends an SQL query to the server and gets the data back. In best case it's just 3 IP packets (query, confirmation/answer_data, confirmation), FB is a bit more complicated. Anyway - it's not a biggy.

PPS: And MySQL has some basic full-text search built-in. For FB you need to code it yourself or get a third-party solution. If you need it.

Re:MySQL vs Firebird

[Weedlekin]

"MySQL has some basic full-text search built-in. For FB you need to code it yourself or get a third-party solution."

Firebird has Interbase's LIKE, CONTAINING, and STARTING WITH clauses, all of which are specifically designed for pattern-based full text searches. For example:

"select * from people where last_name like 'Ma%' "returns all records where the "last_name" begins with the letters "Ma".

"select * from people where last_name starting with 'Ma'" is functionally equivalent to the above.

"select * from people where last_name containing 'Ma'" performs a case-insensitive search for records where the two characters occur anywhere in the "last_name" field.

The above clauses can be combined using AND and NOT to perform fairly sophisticated text searches. Interestingly, CONTAINING can also be used with non-text fields as well, and the same may be true of the other the other two (I'm not sure if this is actually the case, though).

Re:MySQL vs Firebird

[Bastard of Subhumani]

Nope. That would be the Abrams.

Re:MySQL vs Firebird

[Bloke down the pub]

the same Ahbrams tank that was too heavy to go across half the bridges in Europe?

Easily solved.

1) Crew get out, except the thinnest one
2) He drives it over the bridge
3) ....
4) Remainming crew walk across bridge one at a time & get back in tank

Re:MySQL vs Firebird

[raxx7]

Doing that is awfully slow for a database that is bigger than tiny. To get decent performance you need some sort of indexing, which is what I think the other poster was refering to.

MySQL has a fulltext index feature (only for MyISAM tables currently) that does just that. Most other RDBMS also have similar.
Firebird still hasn't any, thus applications need to build and keep their own indexes, which is often slower and requires more storage space than the RDBMs integrated fulltext search indexes.

Re:MySQL vs Firebird

[Anonymous Coward]

> Fair enough. But I would answer it this way: Anybody who spends a lot of time fretting over whether to learn Java or C# is
> missing the point. If you understand programming, it's not hard to learn both. And while you're at it, you should also
> pick up C and C++, in addition to Python and/or Perl. And whatever else you feel like.

Sure, and assuming an infinite amount of time - learn every single language. Or, if time isn't infinite skip the lame, completely obsolete or useless ones.

The same applies to databases:
    - learn about oodbms (few of these left around today)
    - learn about hierarchical dbms (just a couple of these left around & xml)
    - learn about container persistence/serialization (sucks for reporting, can't share data)
    - olap (essbase, etc)
    - learn about relational dbms
        - don't bother with the lame ones:
            - ms access
            - xbase (dbase iv, foxpro, clipper)
            - adabase (now maxdb, now owned by mysql)
            - mysql (only possible reason to learn it is in case you get stuck with it)
        - perhaps you might not want to bother with the less popular ones
            - informix (otherwise a great database)
            - sysbase (a stinker)
            - firebird (good, but not much market share)
            - ingres
        - which probably leaves you with:
            - oracle (market leader)
            - db2 (not market leader, but strong contender)
            - sql server (easy to build windows stuff on, sucks for maintenance in some ways)
            - postgresql (best of the open source options)
            - teradata (for slinging the largest volumes)

> Similarly, the field of database design is choked with so-called DBAs who learned their trade using one tool to the exclusion of all others.

There are also quite a few that have expertise in a half-dozen databases. Perhaps these are the ones that are most annoyed by mysql - by the company telling people that you shouldn't use transactions or referential integrity, etc, etc.

> When the only tool you have is a hammer, every job starts to look like a nail.
Yeah, but that metaphor doesn't apply here - we're talking about why bothering with the broken hammer when there are plenty of good ones out there.

Re:MySQL vs Firebird

[Weedlekin]

"Doing that is awfully slow for a database that is bigger than tiny"

I have two real-world application with pretty large databases that return result sets from text searches on blobs in a second or so. One is an application running on an old Interbase 4.2 with a fairly large number of simultaneous users and around 1/2 million records which has been in daily use since the 1990s, and is constantly being updated from several simultaneous news feeds, so the contents are constantly changing. Another heavily deployed medical system from a company I'm involved with uses Firebird with a fairly complex (several hundred tables, some with more than 100 fields) database. The type of searches being performed (case insensitivity is important) mean that the "CONTAINING" predicate is in all cases, and this could explain the fairly good performance -- "LIKE" for example is _much_ slower (although I haven't bothered to benchmark the differences on these data sets because I'm a programmer, not a reviewer).

"which is what I think the other poster was refering to"

If that's what he meant, then he should have said so in his post.

"MySQL has a fulltext index feature (only for MyISAM tables currently) that does just that"

Unfortunately, it's implemented sub-optimally. The system works well if the entire text index fits in RAM and no result sorting is required, but degrades significantly when this is not the case (sometimes _very_ significantly). As with many other things in MySQL, it's an excellent mechanism for those whose requirements are within a set of fairly narrow boundaries, but falls well short of being a generic solution for high performance text searching.

"Most other RDBMS also have similar"

Oracle has this facility, SQL-Server, Informix, and DB2 don't (SQL-Server and DB2 use external tools to build and maintain indices, one via the OS services, the other with a supplied proprietary program). If 1/4 counts as "most", then you are correct that "most" other RDBMS systems do indeed have this facility!

"Firebird still hasn't any, thus applications need to build and keep their own indexes"

My experience indicates that performance is more than adequate without them for records containing text data of 64K / field (document size before being placed in the database itself -- IB / FB have compressed large VARCHAR / text blob fields for well over a decade) in real-world applications with a significant data-set using CONTAINING. Performance will obviously degrade notably as field sizes grow, as of course would be the case with queries across several fields and / or tables because the volume of data that needs to be searched is obviously greater, and as I said previously, LIKE is reportedly a _lot_ slower.

"applications need to build and keep their own indexes, which is often slower and requires more storage space than the RDBMs integrated fulltext search indexes."

While this is true, the same can be said for those using Informix, SQL-Server, and DB2, and indeed MySQL installations whose text querying requirements are beyond the capabilities of the internal system to handle efficiently (or handle at all). Thus, while MySQL's text indexing is certainly a point in its favour, the limitations and performance caveats of its implementation mean that unlike for example Oracle's CTXRULE and CONTEXT indices, it doesn't always eliminate the need for an external text indexing and search system.

What's your opinion

[CastrTroy]

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?

Re:What's your opinion

[sammy baby]

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

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

[Anonymous Coward]

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

[arivanov]

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

[F1re]

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

Re:What's your opinion

[shatfield]

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

Re:What's your opinion

[nuzak]

> 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

[Just Some Guy]

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.

Re:What's your opinion

[PRMan]

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

[Anonymous Coward]

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.

Sometimes this is just the DBA's HAVING A FUCKING CLUE, and the developers pushing back.

I'm technically not a DBA, but I'm often the de-facto DBA on smallish projects.

Making changes in the DB is much different than making changes in a single application. The DB is the foundation upon which all the applications sit, and errors in the data are basically PERMANENT. Errors in applications are usually cosmetic or cause a crash. Bad data sits in the DB forever, and then new data comes on based on the bad data, it can seriously fuck up a business (I've seen it happen).

When I make a change in, say, the DB of a busy ecommerce site, it usually sits alongside the previous version for 3 months, or more. I'm working on a site right now that has a mess of coldfusion, perl, and PHP running against the DB, on various servers, running various unknown cron jobs, plus the client is accessing the DB directly to make changes that haven't been implemented in the front end yet. When I change something, even a column name, I put a view in first with the change, document carefully, then I write new code against the new view, test everything, WAIT SEVERAL MONTHS, swap the original table and view late at night, test everything, WAIT SEVERAL MONTHS, rewrite all the old code I can find to use the new table, test everything, WAIT SEVERAL MONTHS, drop the original table, test, wait... you get the picture. This is as "agile" as a DBA can get (and I'll admit, many won't even do this, but I hope you understand where they are coming from).

Most developers simply don't UNDERSTAND data modeling, they think that creating a class in their code is modeling.. it's not.. it's a "object view" on the model, that's all. It's not THE model. The model lives in the DBMS. 10 years from now, the frameworks and apps will be different, but the DB will still be here. Unfortunately, developers never stick with a project long enough to see what happens to a badly-designed DB after 10 years.

Just recently I was working with XML interfaces that suddenly changed schema without any warning. This is the mentality of the current crop of developers.. just change a published interface and let the consumer "figure it out". Sorry, this attitude is not welcome near the DB. This is not "politics", this is construction workers trying to tell the architect where to put the doors. Ain't gonna happen.

Also, anybody who calls stored procedures "sprocs" must be some kind of asshat, stop that.

Re:What's your opinion

[Anonymous Coward]

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

You mean, people who actually know something about data modeling?

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.

Well, sonny, this might be before your time, but do you know how they wrote apps in the 60's and 70's? That's right. Everything was in the apps. Everybody was creating their own little toy DBMS layer and calling it something different. Data integrity was nowhere to be found. Application independence was a joke. There was no relational model to tie together and generalize the network and hierarchical models.

Now in 2007, we see developers doing things the same way. Of course, instead of "network DBs" we have "object DBs", and instead of "hierarchical" we have "XML". The previously unnamed pseudo-DBMS layer is now called "ORM layer" or "persistence layer" or "middleware". Instead of general queries, we get XML-RPC interfaces and navigational, element-at-a-time retrieval. People think that pulling out objects in a "for loop" is somehow superior to asking the DBMS for what you need in a single query, letting it optimize the query, and retrieving it all at once.

As far as geniuses like you are concerned, the last 3-4 decades of data management research just didn't happen. Except maybe the functions are all lowercase now, so you can make fun of old-fashioned all-uppercase syntax.

Congrats. You've come a long way, baby.

Re:What's your opinion

[Sylver Dragon]

I don't know about trashing you, but I don't really agree with you.
Well written CRUD procedures will make everyone's life better. The application developer doesn't need to know the underlying data structure, and that structure can be changed to deal with modifications in the database, if there is a need. While it's nice to think that a database will be designed to handle all eventualities, this is not likely to happen in reality. If data access is handled through a standardized set of SP's, those can be updated and the applications tested, rather than having to update every application individually. In the end, the procedures should act as a data access layer, if they are not, then they need reviewing. Also, on this point, bad SQL is bad SQL. It doesn't matter where it is (code or SP's), if it is not taking advantage of the set based nature of a database, something is wrong.
Transactions are good, they protect your data. If data access is slow, take a good long look at your SQL code and database structure. If you have optimized everything correctly, and still are getting poor performance, get a better server; your data is worth it. Without transactions, one broken update statement and you get to either go back to the latest back, and lose any data between then and now, or you get to untangle a Gordian Knot of inconsistent data. No thank you! As you said: get in and get out as quickly as possible. But you missed something: don't trash the data while your in there.
I do agree with the Dynamic SQL being necessary some times. And when it's needed, it's probably going to be best implemented in code, as it is not likely to be reusable. This is much the same as cursors. They should be avoided if at all possible, but sometimes you just have to bite the bullet and use them.

At the GP post said, this argument is a can of worms. And I do find that how one answers this stuff tends to be based on one's background. I'm more database centric, having worked with and supported database applications my entire career. I tend to be very quick to jump to using a database, and am very protective of them.

Re:What's your opinion

[Tablizer]

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.

No wonder my #@$%! snowboard went to Nigera without a trace. "Referential integrity, we ain't need no stinkin' referential integrity". Yeah, tell that to my snowboard in Nigeria.

Re:What's your opinion

[PRMan]

Actually, you do really agree with me (and thanks for the civil discussion).

I am not against CRUD procedures per se, just that it's really bad performance if they are considered to be the be all and end all. Procedures should match what people are doing, not how databases are designed. Which is what I think you just said. I only add CRUD procedures when I need a specific one for a specific table. And in my experience, it's sometimes good to have C and U be the same one (insert or update logic). In a well-designed database, you shouldn't need CRUD on every table, because some tables will be key maps and such.

All applications must be re-tested anyway if SQL changes, even if it's in a data layer or stored procedure. And I can search throughout applications for a Table name as easily as for a Stored Procedure name. In fact, I need to anyway to see what needs to be re-tested. So, the whole updating argument falls a little flat in my experience since the net result is very similar: make a change, find all the apps that called that and where and then test those features to make sure you didn't screw up anything. Don't get me wrong, I do believe that stored procedures or a data access layer are good ideas, especially for security purposes, but they really don't help reduce the load when changes occur. People that think they do are the ones that everyone else hates for breaking their application because they didn't know it called that SP.

In my experience, CRUD procedures lead lazy programmers to bad SQL. And in my experience, attempting to fight human nature is a losing battle every time. They just call the CRUDs 100 times instead of doing something smart in a single SQL statement. This doesn't lead to horrible performance that you will notice, just poor performance that, when added together with the 100 other instances of this will lead to horrible performance, but benchmarks won't show you why because any given data point isn't that bad.

I am not arguing that the idea of a transaction is bad, I am just saying that for performance reasons transactions should be handled in a single SQL batch if at all possible. Making a database wait for something else you are doing in your app is a really bad idea, and the cause of almost ALL of the hardest performance bugs to track down, and usually in a place that you have no idea to look in because it's locking something it really doesn't need to. Again, we are saying the same thing.

I never use cursors, and I have already stated the case for Dynamic SQL. It has its place, sometimes. And make sure EVERY piece of data going into them is checked with Regular Expressions for exactly what you are expecting to see. But religious elitism against Dynamic SQL just leads to searches taking 5 seconds when they should come in under 1/2 second.

Which reminds me, something I was going to put into the original post:

In development (meaning full data but no load on the server),

1. 90% of your database calls should come in under .1 seconds
2. another 9% should come in under .3 seconds
3. 1% should come in under .5 seconds

In my experience, if you are exceeding these numbers, you are almost certainly writing your SQL poorly. Or you need an index. Or something. The slowest SQL I ever wrote was fixing someone else's stored procedure that was timing out because it was taking over 2 minutes to run. First, I immediately got it down to 45 seconds simply by adding an index on the field they were searching. But after rewriting it, I got it down to 4.5 seconds. It turns out that there were 40 million records in the table and it was running on a single 450 MHz machine (yes, people sometimes run corporations this way), so I forgave myself for being 9 times over my usual limit.

Re:What's your opinion

[nuzak]

> Transactions are really bad for performance.

Unless you're using postgres, in which case your performance goes to shit if you DON'T use one. My results with oracle have been mixed -- I suspect, as with all things oracle, it depends on the configuration.

> ... Why do you want to hold up the entire database when someone accidentally pulls the network cable on your PC?

What kind of brain-dead database would implement all transactions with a global lock?

As for Dynamic SQL, it certainly has its place, but when you need variable numbers of columns and you're not working with some crazy-ass 200-column table or you have LOBs or vertical partitions, you're usually better off just selecting * and picking out the columns you want. You are using a cursor, right?

Re:What's your opinion

[zurtle]

Ah I wish I had mod points. That's rather funny! :P

My 2c... I'm finding that MySQL stored procedures are going to help me with the increasing complexity of some queries I'm creating. I have arrive in a poorly-designed MYSQL-based project late in the piece... they've chosen a hierarchical DB structure which MySQL doesn't support all that well (cf DB2, Oracle). Turns out the reports they want don't match the data structure at all giving me the chance to execute approximately 10 kilobytes worth of queries just to create one table of about eight. That seems a bit silly. Looking at the query itself... it seems pretty obvious that there has been a balls-up. Alas the original DBA has flown the coop and the manager doesn't want to make my life any easier (much less the life of the schmuck who picks up support of my work when my contract finishes.

I need to find out more about PostgreSQL. But also looking at small databsaes for some telemetry work that I'm doing for a private project counting hedgehogs. (May do the K.I.S.S. and use grep as an earlier poster suggested)

Re:What's your opinion

[turbidostato]

"Don't put any business logic into sprocs."

Do put critical enforceable bussiness logic (and I mean *bussiness* logic, not application logic) as near as possible to the data (and that usually means SP).

"The second that you place a business rule into a sproc, the users will want the rule changed"

That's true. And that's an advantage of stored procedures: they won't be able to change them (that's why I talked about Critical-Enforceable-Bussiness-Logic).

"Why is this bad? Because sprocs are almost always under the control of the DBA"

Why is this good? Because sprocs are almost always under the control of the most data management knowledgeable techie round there.

"most of the time there is a layer of politics involved between DBAs and Software Developers"

And that's good because that refrain them from their need (either because management pressure or pure ignorance) to do the thing the Real Stupid Way just because it's the fastest/the only way they know.

"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"

So you say that such a bussiness logic should go into the middle tier instead of the RBDM because in that case the developer would do a stupid thing -like expressing it in the middle tier? This is both a circular argument and one more reason to put those kind of rules *out* of the reach of such a stupid developer.

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

Then you need to add more database horsepower. If you need to push or pull more data from the place the data is stored... well, you need it. No middleware is going to change that.

"Adding more middle-tier servers is much cheaper"

Yes. And buying a 1GB pen-drive is even cheaper than that, having in common with your "solution" that they both have nothing to do with the problem at hand. You either need to pull/push more data from where it resides or you don't. In the first case you always need more database horsepower; in the second you won't need it (and if cacheing is a good solution to your current problem then no, your problem is not that you need to push/pull more than provided and no, a cacheing solution is not a "middleware").

Resuming:
You will want stored procedures for your critical enforceable bussiness logic because:

*Developers tend to suck regarding data access/management (design, integrity or efficency); DBAs a little less so (it's the work they are focused on, after all).

*No matter how good your bussiness logic implementation on your new shiny app is, tomorrow it will be developed a new app that will access the same dataset (like the example you talk about people pumping data from Excel -presumably instead of through your pretty web app that tried to enforce this or that rule) and will surely break some bussiness logic or at the very least you will need to reimplement them again. If you don't want this to happen, enforce the rules as near to the data as possible so there's no way to overrun them.

*Your managers are crazy. It's much more probable that your new manager (or the old one if it happens to read the last shiny brochure from "PHB's IT") decides to rewrite all your .Net software to Java or the other way around than to move from Oracle to DB2 (if just because -as you already said, privative RBDM licenses and support are *very* expensive to play with them and they are buried more profoundly in the caves of the black teachies so they make for not as good PR as a new front end with a new brighter colorset and round corners). Go defensive and give your bussiness rules a sanctuary against crazyness within your RBDM (I now this seems to be against common wisdom: the most you program as SP, the most locked to the RBDM you are. But while that's true in theory, my experience in the Real World-TM is that more often than not it is the datastore the one that lasts longer than the apps over it).

*Anyway, your mileage is your mileage: think it hard, think it deep and reach your own conclusions.

Re:What's your opinion

[GoofyBoy]

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

For the most part DBAs see stored procs as part of the application and would love to have developers have full control and responsiblity over the apps stored procedures. They have more things to do than to fool around with app programming. Usually developers don't want to do stored procedures because they can't/won't/don't want to learn a new language.

I find when you see a DBA too controlling its because they've learned that when things go wrong that its way too easy for developers to push back to the DBAs. ("Oh, its part of the database. Ask the DBA, I need to go home early.")

Re:What's your opinion

[Samah]

I agree wholeheartedly on pretty much everything you've said here.

The major application I maintain has pretty much ALL business logic as PL/SQL stored procedures. Why? Because...

• As has been pointed out, having the logic near to the data provides better performance,
• Production hotfixes can be applied without an outage--in our situation the frontend is Java, which would require a jar rebuild and deployment,
• Triggers can enforce business logic,
• Errors in the business logic can be traced with a simple telnet/ssh session without needing access to the client (Citrix, in our case).
• ROLLBACK and COMMIT can be decided based on business logic within the one stored procedure rather than waiting from a request from the client or middleware (which means more network bandwidth).

That's the tip of the iceberg in ways of pros. I'm sure there are a bunch of cons too, but the points I've just listed are pretty compelling.
But yes, it all depends on your situation. If you find that it makes more sense to put the business logic elsewhere within your application, then by all means do so. This is just my opinion based on my experience with this application.
BTW this application is BIG with over 900 tables, 2000 stored procedures and 500 Java forms.

Re:What's your opinion

[shutdown -p now]

Transactions are really bad for performance. Really bad.

This was true for those RDBMSes which used locking, but now that more and more are moving to versioning (PostgreSQL, Interbase/Firebird, SQL Server 2005, and I believe Oracle), it changes. You get isolation level that is almost equivalent to ANSI SERIALIZABLE, and it's cheap as well.

Re:What's your opinion

[arivanov]

is somehow superior to asking the DBMS for what you need in a single query, letting it optimize the query, and retrieving it all at once.

Dear anonymous coward, you misunderstood me. Totally. You are a classic example of what I mean as a person stuck in the fortran age and obviously making a living of his proud brahiousaurusness.

Yes, I would rather have the damn database do that. I am also happy when it does it without getting into fortran-like stored procedure obscenities within a single statement. Do you like it or not, that is the real level of the stored procedures as defined by the ANSI SQL standard - 20 years behind the rest of software engineering, no object orientation, fully procedural with primitive fortran/cobol like syntax, no inheritance and without even all proper structured programming elements.

There is nothing to be proud in supporting this. I would much rather have proper java, python or even C++ embedded support for stored procedures developed further. This would have allowed to do the same, but within modern development practice (and theory). It is a pity to see MySQL which was the first database engine to introduce that - C++ (in 3.x and Java/Python in 4.x) to descend back into the Jurassic.

Re:What's your opinion

[jakerman]

As an app developer and our company's backup DBA, I say this isn't a black-and-white issue.

Here are some very good reasons why you'd use stored procs.....

*Speed is highly critical. Maybe you expect thousands of hits per minute; where poorly-tuned SQL could be lethal.

*Security is unusually critical. Maybe you're storing credit card numbers.

*Uniformity. Maybe an account balance can be changed from multiple apps, or multiple places in one app.

But there is overhead too.....

*Added layer of bureaucracy. Sarbanes-Oxley in the U.S. places heavier red tape on changes to the database (which stored procs count as). App changes are less regulated---for now. What business decision doesn't factor in regulatory burdens?

*Loss of flexibility. If you widen a column in the DB, most engines require all stored proc parameters referring to that column be adjusted too. When stored procs call each other, you may need to change each one in each possible call path. And you may need to change the apps to reflect the altered field size as well.

*Code-intensive. When writing SPs, count on writing code 3 times: first in the SP, second declaring the SP parameters in the SP header, and finally in the app, hooking up the SP parameters to values from your app. An unsung issue here is quality: the more you write by hand, the higher the risk of bugs...even if you are a DBA. Middleware such as JDBC or Microsoft's ADO/ADO.NET allow you to get the job done in fewer lines of code.

*Debuggers. My experience is in the Microsoft world, where app-side tools are vastly superior to SQL tools---which are barely above Notepad in functionality. Like it or not, this is a quality issue: testing is superior in the app world.

So, in sum, I'd say use stored procs where performance or security is of unquestioned importance. Also use them where multiple apps may do the same repetitive tasks---especially if those tasks carry importance.

But if your app fits one/more of these: is small, workgroup-specific, "lower-volume" (realistically this is decided by your hardware), or can be protected only by network security...who cares?

For those interested, here are a few "myths" about stored procs I've observed with Microsoft SQL Server......

*SPs run faster than equivalent embedded SQL. FALSE...with SQL Server 2000, embedded SQL is also precompiled and cached after first use.

*SPs are "safer". DEPENDS...if you must grant a user access to write their own queries, where reasonable just give them a read-only account. If you let them create SPs (ala "GRANT CREATE PROC"), they could write code in the SPs to alter data---hmmmm..."safe"?

*SPs are not vulnerable to SQL-injection. DEPENDS...Microsoft's ADO can be programmed in ways that leave even SPs vulnerable. The key here is to parameterize your statement and trap for escape characters, which is just as easily done with embedded SQL. Many middleware tools can trap injection automatically.

*SPs are "safer" II. DEPENDS...Top-flight security should involve an application role---even more than using SPs alone. Properly used, this prevents users hacking into the DB or SPs thru Access or something they downloaded. Nothing is more irritating than a DBA who thinks his data is safe, because of all his SPs, but then GRANTS EXEC TO THOSE SPs DIRECTLY TO THE NETWORK USER ACCOUNTS!

Stred pocedures

[karavelov]

"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

[Just Some Guy]

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

[Anonymous Coward]

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.

Re:Stred pocedures

[Tablizer]

At least proprietary vendors are smart enough not to name a sub-language PissQL (PlSQL). No wonder they don't let nerds into the marketing building ;-)

The parent comment is a classic example of FUD

[Andy Tai]

Does it tell anything concrete about MySQL 5? No.

Re:The parent comment is a classic example of FUD

[cyphercell]

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?

[faramir_fr]

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

Stored procedures BAD... story

[Travoltus]

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

[zappepcs]

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

[dedazo]

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

[Cyberax]

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

[Tet]

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

[Anonymous Coward]

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

[DavidpFitz]

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

[Anonymous Coward]

"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

[CastrTroy]

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

[Anonymous Coward]

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

[drmerope]

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

[PCM2]

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

[RedElf]

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

[LurkerXXX]

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

[nuzak]

> 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

[shaitand]

'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

[RedElf]

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

[Shados]

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

[Chicken04GTO]

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

[Anonymous Coward]

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

[sfkaplan]

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

[Anonymous Coward]

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

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

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

[Anonymous Coward]

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

[guruevi]

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

[Hemogoblin]

Do you happen to be a government employee?

"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

[MBGMorden]

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

[doom]

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

[Tim Browse]

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

Re:Stored procedures BAD... story

[Tet]

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

[dedazo]

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

[shaitand]

'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

[barjam]

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

[gnuman99]

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

[Bob9113]

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.

Re:Stored procedures BAD... story

[DavidpFitz]

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

Would you believe it, most places in the world are not in any of the states within the USA!

Try firing an employee without a previous written warning anywhere in the EU and you'll find yourself in a spot of trouble.

Re:Stored procedures BAD... story

[Anonymous Coward]

Well, I AM your manager. Get back to work, Johnson!

Re:Stored procedures BAD... story

[Shados]

Don't worrie, I am well aware of corporate software development. I am one of the primary developer for one of the largests non-software companies in the world :) We also have an army of DBAs and database-specific developers, and right now since it IS a stored-procedure-only environment, we have (just on the part I work with) several -douzans of thousands- stored procedure, that do everything you can imagine (including non-database related tasks). I've seen, or at least heard, most everything they can do, no worries :)

Optional parameters and parametrised order bys are the least of my worries here (though having a stored procedure with a dynamic amount of fields, multi-dimentational arrays of parameters, and dynamic order by clauses that allow to sort by a varying amount of fields end up being quite long to make, no matter the magic you apply to them. Easy to write, don't get me wrong, but...). And even with all that, I could just use, for example (on SQL Server), a CLR stored procedure, and even those problems would go away. Thats not the issue at hand.

The issue is that with a well thought out abstraction layer and automated scripts on top of the less critical parts of the applications, you can decouple your model and your database in ways that even a full layer (or two) of views and stored procedures simply can't do.

I mean, great: today my data is coming from 6 datacenters, using several different RDBMS, 2 different ETL technologies, and (thank god) only 1 OLAP system. Thats cute. If tomorrow I decide to change something in there....well, stored procedures aren't the most abstracted thing in the whole wide world. If I upgrade, let say, a SQL Server 2000 to 2005, well, the way to handle something as silly as filtering/paging efficiently changed. Now I have to go through the 4 thousand SPs that used the SQL Server 2k crippled way and optimise them? Hell no, that will probably be simply forgotten and never done. If it ain't broken don't fix it. If I have an SQL abstraction layer, I'll just change 2-3 functions, pass them through the unit and integration tests, and if everything comes up green the douzans of apps in my company will all take advantage of it by the next day.

On top of that, there's the notion that some things are simply easier to think of in an object oriented way, and it just feels real, REAL dumb to have 4 stored procedure for every god damn definition table (thousands!) in there. 90% of those won't be holding money amounts nor credit card numbers, and will be hiding behind locked down web services or remoting APIs -anyway-, so its not like anyone is getting direct access to the tables either. And if thats troublesome anyway (because of the risk of failure of these layers), you still have your views. And if thats still not enough, THEN of course (like in the example you gave), you go the stored procedure way, in which case the SP ends up being just an alternate datasource, the same way my ETLs and OLAP providers are. No biggy: even .NET's silly typed dataset allows you to switch between inline SQL and stored procedures completly transparently (though using inline SQL in a .NET dataset is unmaintainable, so thats probably a bad example :) )

What I'm getting at, is that (again, as your example shows), in corporate environments, doing without stored procedure is virtually impossible. No argument there! You -need- them. However, using ONLY stored procedures is simply a disaster waiting to happen, on top of being a hell of a waste of man hours. Some stuff is simply 10x faster to do using a well made SQL abstraction API. The important part is to always have a good way of using the stored procs that will come in.

Re:Stored procedures BAD... story

[Sylver Dragon]

Would you believe it, most places in the world are not in any of the states within the USA

Yes, but Pax Imperium will be coming soon to liberate a country near you.

Re:Stored procedures BAD... story

[Anonymous Coward]

And that works great if you have one guy working on a project. Once you get more than a handful of people on a project, that logic breaks down. Most programmers aren't DB gurus. We'll do stupid shit if we get to pass raw SQL to the DB.

Let the coders handle the coding, and let the DBAs and DB guys handle the SQL. Tuck it all away in nice stored procedures, turn off SELECT/INSERT/UPDATE/DELETE/etc, and wherever possible make people use the stored procs.

Even assuming you have programmers that are DB gurus, shit happens when you have more than 1 person working on something. The 5 senior guys who have been working on the project for years may know that they have to update x, y, and z every time they update a table, but who's going to tell the new guy? Do you want your data fucked when you could've just used a trigger or stored procedure? Why risk it?

Re:Stored procedures BAD... story

[Anonymous Coward]

As a manager and a professional engineer, I would fire you for calling yourself a database "engineer" unless you actually completed 4 years of engineering school then passed the professional engineering exam. If you did, then rock on brother. If not, please call yourself a database surgeon, database doctor, database attorney or some other meaningless aggrandizing title.

Re:Stored procedures BAD... story

[dedazo]

Those are very good points, and aside from my stubborn insistence that SPs are the One True Solution, I agree with you =)

Certainly, what works with one solution doesn't necessarily have to work with all the others. I've been in this business long enough that I recognize I sure as hell don't have all the answers, and more importantly, I have ceased to be amazed at the crap I see in companies of all types and sizes. The best tools for the job and all that.

Cheers.

Deciding if MySQL is an option

[moore.dustin]

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

Re:Deciding if MySQL is an option

[gusx]

How about...

return (estimated_rows_in_table($table) < 100000);

Re:Deciding if MySQL is an option

[coyote-san]

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

[LordLucless]

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

[consumer]

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

[Anonymous Coward]

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

Re:Deciding if MySQL is an option

[Anonymous Coward]

How about dropping the paranthesis and using a constant?

return estimated_rows_in_table($table) < MAX_ROWS;

Re:Deciding if MySQL is an option

[markhahn]

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.

Re:Deciding if MySQL is an option

[suggsjc]

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

[kpharmer]

> 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

[ak3ldama]

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

[Anonymous Coward]

Actually there were a few more zeros in the number when he typed it but MySQL dropped them.

Re:Deciding if MySQL is an option

[moore.dustin]

All of this is of course based on the scope of the project, but if you are serving up information to a browser, there is certainly a number of queries that will show MySQL's shortcomings. We can argue for ages about what can and cant work for this or that project, but all I was trying to convey was that for any project of significant size or importance, MySQL is not the best answer. It may be an answer, just not the best.

Re:Deciding if MySQL is an option

[Ant P.]

"Without partitioning"? What are you trying to say, that db2/Oracle doesn't have such a basic feature?

Re:Deciding if MySQL is an option

[Ecks]

Although I am a PostgreSQL proponent, I've got to say that mysql will happily handle > 100000 rows in a table. Especially if you properly normalize your tables. I ran a virtual inventory application on MySQL which tracked well over 100000 items (assigned and available IP blocks, atm virtual endpoints etc) for a medium sized ISP. I'm currently building a small decision support application. One of my tables has 8000000 rows in it. It's used twice in a query and that query takes no more then 0.1 seconds to execute under any conditions in MySQL.

-- Ecks

Re:Deciding if MySQL is an option

[Anonymous Coward]

You really shouldn't link to a page about MySQL's partitioning support without linking the following:

http://dev.mysql.com/doc/refman/5.1/en/partitionin g-limitations.html

Re:Deciding if MySQL is an option

[Anonymous Coward]

Is it even in stable relase yet?

Re:Deciding if MySQL is an option

[Anonymous Coward]

So what would you propose for an app like phpMyAdmin? Should it go through and automatically build all the
stored procedures when you install it in order to do simple updates and selects via it's web interface?
Doesn't that seem kind of counter intuitive to you? Maybe you are just saying , "in certain cases,
doing this thing this way is better". In which case you are just saying that engineers should write code,
because they have been trained to apply logic and engineering principles to apps they build.

In that case, I agree with you 100% ! If you want to build something, get a good engineer on the job,
and let him pick the right tool for the right task!

Re:Deciding if MySQL is an option

[Anonymous Coward]

20x the hardware cost for MySQL to get the same performance as oracle will probably still be less than a certified DBA and license fees

Re:Deciding if MySQL is an option

[Anonymous Coward]

Why should the number of rows in an undefined table be meaningful to anyone?

I'm glad MySQL works for you but your comment does not add to or detract from the comments of the parent.

Re:Deciding if MySQL is an option

[JAlexoi]

I am currently looking at a table with only 17 million records.
I know that there are inconsistencies, I am just afraid to search how many are there.
And that including the FACT that there were NO crashes EVER.
Fortunately NO ONE ever needed 100% consistency on that database, and people will probably "pay" for that.

Re:Deciding if MySQL is an option

[Dirtside]

Unfortunately, your ideas about mysql's capabilities are based upon insufficient experience

Ok, choke on this then: I work for a company that runs exclusively MySQL. We have 1,500 tables across our *primary* DBs (that's user-facing data) containing a little over four billion rows. Most of the queries are simple because they're primary-keyed returning single rows, but our largest tables contain 100-200 million rows and are subject to several thousand queries per second.

And we have fewer problems than we did when we were using Oracle with 1% as much data a few years back. Yes, that's right: We switched from Oracle to MySQL. And it was one of the best decisions we ever made.

Re:Deciding if MySQL is an option

[Anonymous Coward]

It sounds like the schema is very simple, and queries are not only simple but almost entirely comprised of reads. Also, given the numbers you're claiming, I'm fairly certain that you need to be running a cluster to handle the load even with the above restrictions. Given that MySQL doesn't have any kind of clustering solution whose performance won't absolutely implode with even a light write load while maintaining cluster-wide coherency, I would have to guess that strict coherency is not a requirement for your application. Considering all of the above, I would further venture to guess that while the data, in aggregate, is important to your enterprise, the importance of each individual transaction and/or record is very low (e.g., account records or browing statistics for a free portal site, or post and user preference records for a forum), which is why you can afford such lax restrictions on your data store. In short, your application fits neatly into the very narrow class of use cases where MySQL is actually a good choice. Unfortunately, that does nothing to dispute your parent's claim that MySQL is horrible in the general use case for an RDBMS.

Re:Deciding if MySQL is an option

[Slashdot Parent]

Last I checked, MySQL supports partitioning.

Last I checked, PostgreSQL does not.

Re:Deciding if MySQL is an option

[Anonymous Coward]

> It sounds like the schema is very simple

He just said there were 1500 tables. That is basically insane. I've seen whole ERP systems with less. I can't fathom how one would need 1500 tables unless they were entity beans for every last class in an app. Possibly most of those tables are some poor-man's partitioning scheme.

Re:Deciding if MySQL is an option

[Dalroth]

Check again: http://www.postgresql.org/docs/8.1/interactive/ddl -partitioning.html

Re:Deciding if MySQL is an option

[laughing rabbit]

Funny---when I went to the prior post link---the link you submit is plain to see---on several pages regarding partioning.

Re:Deciding if MySQL is an option

[00lmz]

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.

Ignoring attribute constraints is OK (I mean, it's a documented limitation so it's OK, right?), but SILENTLY ignoring them without even a warning is not OK.

That also applies for the one "limitation" (I'm not sure whether this is MySQL or InnoDB limitation) where you cannot put the foreign key definition inline with the field definition (e.g. OTHER_ID INTEGER REFERENCES OTHER_TABLE(ID)), but need to put it in a separate constraint definition (e.g. OTHER_ID INTEGER, FOREIGN KEY (OTHER_ID) REFERENCES OTHER_TABLE(ID)). By "cannot" I don't mean that it ends with an error (that would be preferable), but that it silently ignores it.

Re:Deciding if MySQL is an option

[Anonymous Coward]

> Last I checked, MySQL supports partitioning.

Then you must have been checking out their non-GA (general availability) releases.

Typical for mysql - they've been saying that they've got partitioning for over a year now - and they still don't have it stable.

So, go ahead and use it if you don't care about its hundreds of limitations or if you don't care about stability or reliability.

Re:Deciding if MySQL is an option

[consumer]

I cut my teeth on Oracle. I know what it can do, and I know it will be faster than MySQL at many things involving large databases. A good example of MySQL's shortcomings would be subqueries, where very little work has been done on optimization of query plans. However, a simplistic statement like "never use MySQL for a project involving more than 10000 rows per table" is obviously false.

Incidentally, MySQL has partitioning now and can parallelize queries across partitions.

Re:Deciding if MySQL is an option

[kpharmer]

> a simplistic statement like "never use MySQL for a project involving more than 10000 rows per table" is obviously false.

Sure, mysql can provide adequate performance for many apps with more rows than that - but only if you've got highly selective queries that the btree indexes can support.

> Incidentally, MySQL has partitioning now and can parallelize queries across partitions.

Not in a general availability release - you're dealing with non-production code if you use mysql partitioning. And you're living with about a hundred limitations.

Stored procedures and data integrity

[Tony]

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

[CastrTroy]

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

[jaydonnell]

"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

[LurkerXXX]

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

[Electrum]

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 and data integrity

[jaydonnell]

Your example isn't a trigger or a stored procedure. Those simple constraints should be in the db. I'm talking about more complex constraints that involve logic (i.e. programming).
"Even if your application is small, you may update that table in multiple places"

You shouldn't, but most code is terrible.

Re:Stored procedures and data integrity

[jaydonnell]

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

Do you have automated processes that run your test suite against your db code? Your db code is in subversion (or whatever)? I would love to hear about your setup. What db do you use and how do you fit tests and version control into your processes?

"How do you know at no time in the future another application won't be trying to access the database directly?"

I don't know it absolutely, but it's a risk I'm willing to take in some cases because I'm almost certain that no other application will access it. One reason is that I do web programming and I add api's to my app. If another application wants to work with the data it can use the api. No need to access the db at all.

Re:Stored procedures and data integrity

[LurkerXXX]

I mainly use MSSQL right now, but I've set up the same or helped others set it up similarly for Oracle and PosgreSQL.

I automate the tests with stored procedures which call the test scripts. Yes, everything goes into a subversion repository. I've got a DTS packages set up to move the code for everything to/from a working directory synch'ed with subversion.

It isn't about speed

[Tony]

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.

Names matter sometimes

[ClosedSource]

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

[Sloppy]

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

Re:Names matter sometimes

[Anonymous Coward]

CodecSQL? DiskworldSQL?

Re:Names matter sometimes

[Tablizer]

I think part of postgresql's problem is its awful name. Just about anything would have been better

How about PostNasalSQL?

At least with a dumb random name, your risk of trademark copyright infringement is very low. I really doubt there is a real company with a name like SnergFartSQL, for example.

Re:Names matter sometimes

[Anonymous Coward]

I really doubt there is a real company with a name like SnergFartSQL, for example.

Cisco owns the trademark on that, I think.

Re:Names matter sometimes

[Anonymous Coward]

SuperKarateMonkeyDeathSQL?

isnt it the same for ALL the stuff that took off :

[unity100]

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.

Not really FUD

[Anonymous Coward]

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

[elp]

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:Not really FUD

[cortana]

I have to say that you haven't checked out PostgreSQL for far too long. None of your criticisms apply to any vaguely modern version of it.

Re:Not really FUD

[gullevek]

the Postgres speed was true until the release of Version 8. There it might such a huge jump, that was just unbelievable. You should give it a try.

Re:Not really FUD

[ari_j]

Not only that, but I can't comprehend anyone who claims that MySQL is easier to administer out of the box than PostgreSQL. User management alone is proof of the opposite.

Re:Not really FUD

[Anonymous Coward]

You mean you actually use MySQL for something OTHER than testing your resources out?!

Re:Not really FUD

[elp]

Its been a while since I've worked heavily with postgres so I can't comment on the speed improvements but on Fedora 6 running postgresql 8.1 only local tcp/ip connections are enabled by default (no -i switch for the postmaster process) and autovacuum is still off by default. The performance in postgres can be really bad with out regular use of vacuum. I know a competent admin can fix all of that easily but your average newbie cannot. I manage a web hosting business with several thousand customers and my experience and every other hosting owner I've ever spoken to is that the demand for postgres is almost zero (less than 1 in 1000 customers).

Please understand that I am a whore and I will sell what ever my customers want but right now almost none of them want postgres. If the postgres devs spent more time improving the friendliness of their system and less time shouting down anyone offering constructive criticism they might start clawing back some market share.

Re:Not really FUD

[virtual_mps]

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. Of course, for non-trivial queries its been faster for some time. A few years ago when mysql 4 and postgres 7 were the latest and greatest, I recall being aghast at mysql's inability to use multiple indexes to optimize a complex join--but postgres did that just fine. Difference in runtime? Postgres could complete the query in under a second while mysql took 30-45s. I suppose the Mysql Way would be to break the complex query into braindead pieces and then do half the processing in the application. Yuck. So, yeah, mysql has traditionally been faster for pulling data values out by key--but you know what? a berkeley db is still faster if all you need is trivial queries.

Re:Not really FUD

[cortana]

It sounds like Fedora's packages of postgresql are fucked up. Autovacuum is enabled by default in 8.1.

Although, personally I think that disabling TCP/IP by default is a good thing.

"any other open source system"?

[Anonymous Coward]

> 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!"

Emulated SP's

[vivin]

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

[Ajehals]

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.

Re:Emulated SP's

[thePowerOfGrayskull]

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". Umm... that's not a SP at all, never mind being a 'true SP' or not. You basically made a PHP function; the whole purpose of SP is that the database server does the work, not the calling application.

Re:Emulated SP's

[vivin]

Thanks, Captain Obvious :) My point was that I was only able to emulate the interface. Not the actual part (obviously) of the database doing the job.

Re:Emulated SP's

[thePowerOfGrayskull]

What I don't understand, then, is why one would do this? What good is the interface, without the actual functionality ?

Re:Emulated SP's

[vivin]

The interface did provide functionality, albeit without the burden being placed on the Database. PHP constructed the queries. The app I was writing at the time had a bunch of queries that were similar, only differing based on the parameters. Based on the parameters, slightly different queries would be constructed. This is what is usually done inside an SP (in addition to other stuff). My function would parse the parameter list and then build the appropriate query. So no, of course it's not a "true SP". I only emulated a part, and not the whole deal. I thought I made that clear in my original post.

Wait, this sounds familiar...

[Mr. Underbridge]

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

Glad some database noobs modded this up

[Anonymous Coward]

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

Good one Ploppy

[Tim Browse]

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

[Karaman]

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

[not already in use]

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.

Re:Pain in the ass to debug

[Anonymous Coward]

Check out something called Visual Studio.....

You can debug your applications and step from code into stored procedures in MSSQL and Oracle.

Very Cool.

Re:Pain in the ass to debug

[not already in use]

That would be great, but I don't have thousands of dollars to pay for the array of licenses I'd need to do so.

case study

[kpharmer]

> 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:case study

[rapiddescent]

also, if you put this level of detail (see parent) into a stored procedure then it is much easier to unit test the database separately and hand over reponsibility of that unit test to the "database team". It allows the database element to have a version on it's own independant of the source code.

I find that (in big teams, where multi-tier applications are being produced) it is far better to maintain tier separation and avoid having sql code in the app servers or worse, the client - because any minor database change has a ripple effect time and cost impact on other parts of the team.

also; choosing this methdology makes offshore development easier where, perhaps for security reasons the database team is local and the application developers are somewhere else. I've seen this where the dba's and the sql coders where local because the client had risky non-functional requirements and preferred to keep the database people local.

At work I use DB2 on Z and Oracle (including RAC) most of the time. Sybase seems to be getting rarer in the finance industry nowadays. I've never seen mysql or postresql deployed in a very large organisation (yet!). It would be nice to get slashdotters to say if they're using postresql or mysql in large organisations.

rd

Learn data modelling

[Anonymous Coward]

I'm tired of this meaningless distinction between "business logic" and "data logic" or other logic.

First thing you have to understand is that DECLARATIVE constraints are ALSO business logic. Not just procedural code. Once you understand this, ask yourself the following:

1) Are your table attributes (column names) business logic? Yes or no?

2) Are type constraints (column types) business logic? Yes or no?

3) Are table names, view declarations, etc., business logic? Y/N?

4) Are SQL queries business logic?

5) Stored procedures business logic?

6) Procedural code within the application?

Of course, this is ALL BUSINESS LOGIC. Some of it is declarative, some of it is procedural, but that's irrelevant.

If it's not business logic, then I have a challenge for all you would-be "data management experts". Take your application, and point it at a different database than the one you wrote your application against. For instance, take your ecommerce application, and point it to the DB for your blog. Does it still run? If not, then you have some business logic in your database. THINK ABOUT IT.

Once you understand this basic concept of data modeling, suddenly the decision to split your model between database and application becomes a little more arbitrary, doesn't it? And you are forced to admit that much of what you might consider "best practice" is actually just convenience, programmer laziness, or a fundamental misunderstanding of data modeling.

Personally, I think people should be licensed before they are allowed to touch a database that has anything to do with the public, but that's just me...

Feature comparison checklists are so 1985ish

[ScrewTivo]

I'll have to put on my flame suit for this one only because so many interests are out here defending non-defendable territory.

Why does everyone love CSS for separating content from format .... then turn tail and try to put business logic into the database tier. Try moving a large MS-SQL app that utilized stored procedures to Oracle or MySQL. Tell the wife you'll see her next year!

Presentation - Logic - Storage. Clean and simple. Stored procedures and triggers are nothing more than DB-CRACK. Easy to use in a "pinch" yet keeps you addicted forever.

The concept of moving trigger/stored procedure coding out of the database engine is the scariest thought for Oracle/MS/IBM....yet it works surprisingly well in the freebie MySQL.

This subject is so deep and broad it leaves the mega dollar database companies room for more FUD the HS in NYC in 1905 (check that one out in your database).

SCALE THAT IN YOUR BOARDROOM!

bullshit..

[Anonymous Coward]

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.

Ive joined many more than that and performance does not degrade, you provide no facts in your 12 table join argument other than say "it will get slow". The 'poorly choosen nested-loop joins' are the decision of the fucktard programmer. And if he is blowing his app out from fucked up loops how does the database lay blame for that?

Re:bullshit..

[00lmz]

The 'poorly choosen nested-loop joins' are the decision of the fucktard programmer. And if he is blowing his app out from fucked up loops how does the database lay blame for that?

Do you even know what a 'nested-loop join' is? Hint: It happens inside the RDBMS if the optimizer can't figure out a better way of joining two tables.

The primary purpose of stored procedures

[Colin Smith]

Is to lock the application to the RDBMS. The RDBMS becomes an application server rather than storage. There are some ancillary benefits but really they should be provided by middleware.

Re:The primary purpose of stored procedures

[LurkerXXX]

Bull.

Stored procedures are extremely useful for security, to reduce network traffic to the client or application server, to ensure consistency, to take advantage of database-specific features, and most importantly to ensure data integrity.

Re:The primary purpose of stored procedures

[Colin Smith]

Yeah, thought I'd hear those old chestnuts trotted out. They're the ancilliary benefits I mentioned.

MySQL in the Enterprise

[BillGatesLoveChild]

Kudos to you Shielfw0lf. I worked on an enterprise project using MySQL. I'd used it before on hobbyist/home-project/web-sites and thought it was waaaaaaaaay cool. But when we started working on, man, we found it so painful. Often The indexing system is insane (only one index can be used per query), and it was very buggy. MySQL InnoDB was better, but even it suffered from 'epic rollbacks' that would take hours. Things often wouldn't work properly: You'd find simple expressions that wouldn't evaluate properly. When we reported them to MySQL they were very arrogant about it. After that experience, I'd never try and use MySQL in an enterprise application (defined as anything critical that your life or money hangs off). A friend of mine bought out another business. During negotiations the seller said my friend should buy 'so you have a backup when your databases go down'. Huh? said my friend, 'my databases don't go down. ever!' Turned out the seller used MySQL. My friend used Oracle. Of course Oracle has its problems (absurdly expensive and prehistoric tools), IBM DB2 and Microsoft SQL are both dogs. Firebird DB is nice, free and open source based on Borland's Interbase DB.

For small projects and personal web sites, MySQL is fine. You don't get cheaper than free and because it's so widely used never a problem finding people that know how to use it. If I had to do it all again: Firebird or Oracle.

You just gota keep DBA's out of critical path.

[HornWumpus]

Or you will blow every schedule you've got.

DBAs are administrators. They write backup scripts etc. A good one will be capable of writing scripts to update data structures without using tools like ERwin. (ERwin and the like are the better way to do it you understand what the tools are actually doing. Very few DBAs have that depth of knowledge.)

The DBAs also think they are the only people that understand how databases function. (Often they don't even understand, but when has that stopped anyone.)

They are also usually very anal. That's what makes them good admins.

If you need to update complex SQL on a regular basis you embed it in YOUR source, send it to the server and eat the repeated compile time.

The alternative is that your SQL becomes part of the DBAs schema (or worse yet data model) and you need an act of congress to be able to make any changes.

Granted there comes a point where everything has stabilized where it makes sense to let the DBAs pretend they're in control.

Tell us where you work?

[HornWumpus]

So we can not apply there and short your companies stock!

Asshat.

So, what to do about the frigging business logic?

[Anonymous Coward]

Somehow, I believe I will be told, that using SPs to avoid all the network traffic of:

SQL: SELECT [millions_of_rows]
App: [make checks and decide which ones to update]
Repeat:
SQL: UPDATE [hundreds_of_rows]
SQL: UPDATE [another_hundreds_of_rows_differently]
Until [lots_of_thousands_updated]

is way bad, because I shouldn't be selecting millions of rows anyway, nor updating thousands in such a manner.

However, I would like to see a better way of proving that, than simply declaring "I have never found an app where a [million_row_select] couldn't be reduced to [hundred_row_select]!".

MySQL vs PostgreSQL benchmark

[spatialguy]

http://tweakers.net/reviews/649/7 Tweakers is a dutch community of online tweakers that was deciding on new hardware. The above link leads you to some stunning performance graphs that show that not only postgresql 8.2 is faster then MySQL 5.0, it also scales MUCH better on heavy loads! This benchmark was discussed earlier on slashdot to some degree, but I think quite a few of the commenters above have not seen this. The tweakers community are longterm MySQL users and were new to PostgreSQL. However they even brought in a MySQL expert to configure it optimally. Still PostgreSQL turned out to be the better dbms

Re:MySQL vs PostgreSQL benchmark

[spatialguy]

http://tweakers.net/reviews/674/7
This is a more recent test where the scalability of MySQL and PostgreSQL to an 8-way opteron is reviewed.
Again PostgreSQL 8.2 outperformes MySQL 5 by far.
Interesting is that they discovered a small issue in the stable release of postgresql 8.2 which was subsequently patched by the postgresql developers.
It was still much faster and robust then MySQL which degrades terribly under heavy loads.
The version pg 8.2.1 did ship with these patches.
Another interesting subject is that they revealed issues with Solaris, which were investigated by Sun developers, who helped in configuring the Solaris installation.

Re:MySQL vs PostgreSQL benchmark

[spatialguy]

http://tweakers.net/search?DB=Reviews&item_catId=1 2&productCatId=&Query=postgresql&x=0&y=0

This link will give you a results page with links to many similar benchmarks of MySQL vs PostgreSQL on their site.
They are very thorough in their tests, are themselves MySQL users, and give insight in which optimizations they used to perform these tests.
A very convincing read.

Apologies for replying to my own posts.

Slow and painful

[chrysalis]

Hello,

I worked a lot with MySQL stored procedures lately.

While handy for small and simple things, MySQL stored procedures aren't that great for more complex processing.

- Procedures are difficult to debug. There's no debugger and no way to trace what's going on except by using horrible tricks.
- The language is somewhat limited. Doing very simple things can require dozen of lines, with juggling between local and thread-local variables. And since there's nothing like arrays or associative arrays, you have to use temporary or MEMORY tables to do the job, and it implies some more horrible tricks.
- It's slow. Slow, Damn slow.

This is why UDFs are still the way to go for any complex task.

That refers to a very old Mysql version

[theolein]

The links you provide are to a very old version of Mysql (3.2xxx), which was current around 6 years ago.

The newer Mysql version, depending on which storage engine you use, support foreign keys: Version 5.1

Very poor SPs in Mysql

[theolein]

I've used stored procedures in Mysql, and have to say that they are very limited for anything complex. There are a lot of limitations in both stored procedures and functions which make them almost unusable for real tasks. The limitations have mainly to do with what you can pass to the procedures and functions and what you can return.

Exactly

[theolein]

I ran into exactly the same problems as you did when trying to write SPs that would do maintenance on other tables. It simply was not worth the hassle. The fact that I had to resort to temporary tables because there are no associative arrays (Postgres, thank god, does have associative arrays), was the biggest problem.

Mysql is getting better, but I still think there's only Postgres as a real alternative to things like Oracle, mainly because the feature sets between the two are so similar.

definitely worth checking out

[felix]

It's definitely worth checking out if you use MySQL. It's well written and comprehensive. My own review was very close to this one. You can check it out on my blog (or amazon).

What what about mySQL Enterprise ED

[Anonymous Coward]

I have read a lot of the comments but nothing has been mentioned of mySQL Enterprise Ed.

Late response: Good points

[Tony]

Sorry this is so late, but it's really just:

Good points. That is very true. I deal with data-critical applications (used to be medical/financial, now it's satellite data). So, I do have a specific viewpoint.

RDBMSs are good general tools, and often just storing the data is enough. Sometimes I forget that.