Stored Procedures: critiques and defences

存储架构 2015-01-16

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


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.


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


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.


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

SQL And Its Sequels

责编内容by:SQL And Its Sequels (源链)。感谢您的支持!


OSX Tip Using mdfind to search your hard disc for ... As a long time Unix user, the lack of locate being automatically turned on in...
.NET Core 2.0 With Angular 4 and MySQL, Part 8: An... .NET Core 2.0 With Angular 4 and MySQL, Part 8: Angular Routing DZone's ...
从0到1学习网络安全 【MySQL基础-MySQL 安装】... MySQL 安装 所有平台的Mysql下载地址为:MySQL 下载. 挑选你需要的 MySQL Community Server 版本及对应的平台。 ...
OneProxy实现MySQL分库分表 简介 Part1: 写在最前 随着网站的壮大,MySQL数据库架构一般会经历一个过程: 当我们数据量比较小的时候,一台单实...
Mysql optimization with key definition I have following table. I don't know whether defining of additional keys should ...