Monthly Archives: January 2015

Stored Procedures: critiques and defences

I've gathered the main critiques of MySQL / MariaDB stored procedures, and will try some defences.


The critique:

SQL/PSM is the standard 4GL and it was the work of Andrew Eisenberg. Andy based it on ADA. Unless you are military, you have never seen ADA. Be grateful it is dead.
-- Joe Celko, reminiscing about his days on the SQL standard committee

Actually I believe Mr Celko likes SQL/PSM, which is the standard that MySQL and MariaDB follow. Here at last is your chance to see some Ada code, and compare with MySQL code ...

declare a: integer;

  a := 0;
    a := a + 100;
    exit when a = 200;
  end loop;
  if a /= 300 then
    a := 400;
    a := 500;
  end if;
  case a is
    when 600 a := 700;
    when others a := 800;
  end case;

  declare a integer;
  set a = 0;
    set a = a + 100;
    if a = 200 then leave x; end if;
  end loop;
  if a <> 300 then
    set a = 400;
    set a = 500;
  end if;
  case a
    when 600 then set a = 700;
    else set a = 800;
  end case;

The trouble is, Ada isn't even among the top ten programming languages according to O'Reilly's count of book sales, and the choice of only 1.6% of programmers according to the Language Popularity Index. So programmers aren't used to seeing syntax like the above. And I, who am just as good a psychologist as I am an Ada programmer, explain: they're not familiar with it, so they call it ugly.

The defence:

Look at the first alternative: UDFs. Percona posts point out that UDFs can be faster. of course.
But look at the example in Managing & Using MySQL Second Edition. Do you understand it without reading the long commentary?

Now look at the second alternative: External stored procedures. There's a worklog task for stored procedures in other languages, of course.
But it's moribund. An implementation on launchpad, "External Language Stored Procedures for MySQL", exists but doesn't seem to have been updated since 2009. Are you going to go to the trouble of downloading and adjusting it?

If you answered yes to either of the above questions, please check the length of your nose now.


The critique:

"Migrating a stored procedure is much more complex than rewriting one because the relevant standards of various vendors differ greatly. In this situation, users have no choice but stick to one database vendor rigidly. There is not any room left for users to beat down the price if database vendors overcharge them on upgrading their servers, storages, and user license agreements."
-- Couchbase, "Alternative to Difficult Stored Procedures in Big Data Computation"

The defence: That's not true. There's only one relevant standard, there are multiple migration paths, and there's no charge.

The other DBMSs that follow the ANSI/ISO SQL/PSM standard are: DB2, Mimer, SolidDB, Sybase iAnywhere (Advantage Database Server).

Selective quotes from a case study about "experiences with porting stored procedures written in SQL PL (DB2) to MySQL":

To find out how "standardized" the MySQL implementation of the SQL/PSM specification really is, we tried to port all our DB2 stored procedures to MySQL. First, we ran into DB2 non-standard extensions of SQL PL that were used in our existing procedures. Actually, there was only one such extension [SQLCODE] ... So we first rewrote our DB2 procedures --in DB2--, making sure not to use SQLCODE anymore. Instead we had to introduce the corresponding continue handler(s), thereby introducing an additional "flag" variable. This worked out fine: the new procedures ran perfectly in DB2. ... Now we observed some syntactic differences, luckily not in the body of the procedures but in the optional clauses specified just before the body ... The only option clause which we could keep was the "LANGUAGE SQL": required in DB2, optional with MySQL. After these small modifications, the CREATE PROCEDURE statements from DB2 worked on MySQL!
But did they run properly? To verify this, we had to create identical tables on both systems, have the same test data in both, and migrate then run the unit test programs from DB2 on MySQL. And indeed: it turned out that MySQL worked exactly as expected!
-- Peter Vanroose (ABIS), MySQL: stored procedures and SQL/PSM

As for Oracle, its PL/SQL language is not standard, but read the Oracle documentation: "PL/SQL is based on the programming language Ada." Due to the common Ada heritage that PL/SQL and SQL/PSM share, I've been able to convert Oracle stored procedures to MySQL stored procedures at a rate of about a line per minute. I expect I'd achieve the same speed with NuoDB (NuoDB architect Jim Starkey once assured me that their stored procedures will follow PL/SQL), and with PostgreSQL (PostgreSQL's stored procedures are deliberately Oracle-like). There's also an SQL/PSM add-on for PostgreSQL, although I don't think it's popular.

Finally, there are some commercial tools that try to automate the migration process. I think the one from ispirer is the best known.


The critique:

Your stored routines are not likely to integrate well with your IDE. ... [SQL-oriented GUI tools] do not integrate (well?) with your source control ... While engineers are keen on writing unit tests for every class and method they create, they are less keen on doing the same for stored routines. ... MySQL routines have [no?] built in debugging capabilities ...
-- Shlomi Noach, "Why delegating code to MySQL Stored Routines is poor engineering practice"

The defence:

These are important considerations, but strictly speaking they're about what people should have written to go with stored procedures, not about stored procedures themselves. It's not the wiener's fault if there's no mustard.

Take versioning. For Oracle it can be done with a client GUI named Oracle SQL Developer. For MySQL there are open-source utilities like dbv. Either way, it's not a server task.

Take unit tests. Hmm, okay, you can't, but some people might be satisfied with MyTAP or maybe even STK/Unit. And did you notice in the quote above that one can make unit tests with DB2 tools and run them in MySQL?

Take debuggers. I'm aware of five, although I haven't tried any of them, except the one that I wrote myself. I am going to integrate it with ocelotgui.


The critique:

Stored Procedures ... do not perform very well
-- Percona employee, How can we bring query to the data"

The defence:

Supposedly there are more than 12 million installations and that means there are 12 million which are, as the old saying goes, "not Facebook". I haven't interviewed them all (only Monty Widenius can do that), but the ones that I've talked to are concerned more about the effects of badly-written queries or database design mistakes.


The critique:

It's easy to find articles and blog posts with titles like "Why I hate stored procedures", "Goodbye Stored procedures, It’s the time to move on", "Stored Procedures - End of days", "Why I avoid stored procedures and you should too".

The defence:

Usually those articles are about SQL Server. They are by people who got fed up with Microsoft's T-SQL language, or got enamoured of ORM (Object Relational Management). Inevitably some of the bad vibes get picked up in the MySQL / MariaDB community due to morphic resonance. Filter out the Microsoft material, and it gets harder to find such articles or blog posts, and it gets easier to find articles with more walking-in-the-sunshine outlook.

Besides, some of the alternatives might just be fads. As Winston Churchill said:

Many forms of Database Code have been tried, and will be tried in this world of sin and woe. No one pretends that Stored Procedures are perfect or all-wise. Indeed, it has been said that Stored Procedures are the worst form of Database Code except all those other forms that have been tried from time to time.
-- Winston Churchill, Hansard, November 11 1947

Okay, he didn't actually say Database Code or Stored Procedures, he said Government and Democracy. Close enough.


The critique:

"The following features have been added to MySQL 5.6: ... GET DIAGNOSTICS"
"The following features have been added to MySQL 5.7: ... GET STACKED DIAGNOSTICS"
-- MySQL user manual for 5.6 and 5.7

All right, you have to read between the lines a bit to see the critique here.
What MySQL/Oracle is implying, by omission, is: progress is glacial. As I said in a previous post I think GET DIAGNOSTICS is good to see. But the first MySQL 5.6 releases were four years ago. So, one significant new feature every two years.

What about MariaDB? Well, I did see a new trick recently in a blog post by Federico Razzoli: How MariaDB makes Stored Procedures usable, about using cursors even for SHOW statements. And I suppose that MariaDB's "compound statements" feature could be looked on as at least a feature that's closely related to stored procedures. Still, small potatoes.

The defence:

* The current implementation has pretty well all the significant matters required by the standard.
* There has been no official announcement that any significant stored procedure feature is deprecated.