3 good reasons to use Stored Procedures

City of London, AKA “The City” –
Lots of banks, thus lots of stored procedures

Many developers and database professionals don’t like stored procedures. They often have good reasons for that. For example, procedures add more work into the database server, that is often the application’s bottleneck. This means that more CPU time is used, so procedures can increase the costs. It is not a coincidence that Oracle developed an incredibly advanced stored procedure system and they make us pay a price per CPU. And so on.

In PostgreSQL the situation is completely different, because Postgres has a very advanced implementation. This is the reason why this article focuses on the MySQL ecosystem. But the benefits that I will list are valid for all database systems.Are stored procedures always slower and not scalable?

Are MySQL stored procedures evil?

In the MySQL world, there are even more reasons to avoid stored procedures. The language is limited, verbose and not flexible. There is no native debugging facility. And they are dead slow. Run a simple loop from 1 to 1M – you won’t believe your system clock. The problem is about with queries – their speed will not change if you move them into stored procedures. But stored procedures code is slow.

Oracle developed a mechanism to be able to compile stored procedures in any language in the GraalVM. Even if being bound to a particular VM sounds irritating, the news is still exciting, right? Except that in practice it is not true. They advertised something that most probably will never be released.

Anyway, all these objections to stored procedures can be defeated in some contexts. For example, one can get used to the language – I did. And sometimes you don’t care too much about performance. An administrative stored procedure that runs once a week, for example. Or procedures for analytics.

But of course in most cases speed matters. But even in those cases, there are rare situations when a procedure improves the performance.

When stored procedures are faster

Let me list my points in favor of stored procedures:

  1. Less queries;
  2. Less results;
  3. Locks.

I will show how these things play in favor of better performance and more scalability – in some cases.

1 – Less queries

A stored procedure may contain several queries. Grouping queries in a procedure avoids some of the work that the DBMS does when it receives a query from a client.

Unfortunately, in MySQL and MariaDB, only the work related to the network communication is avoided. Procedures are not compiled, so the queries still need to be parsed (unless prepared statements are used).

Still, avoiding some network communication relieves some work from the server and the clients. It also makes slowdowns and failures less likely, if the network subsystems or the network itself have problems.

2 – Less results

This is potentially much more important. If our resultsets are big, sending them to the clients could be slow, and our network may suffer. This is also the reason why database professionals often insist that using SELECT * is not a good idea.

Usually, less results is also a motivation against stored procedures. If rows must be processed in a loop, that should happen in the clients, not in the database server – because, as we mentioned, the database server is often the bottleneck. Cursors are based on temporary tables, so they consume memory. If many users use temporary tables, the server’s memory could be insufficient.

But there are exceptions. For example, what if this elaboration can be done once a week at a very low-traffic time, and then cached into a table? Potentially, this is not a heavy operation. If elaborated data can be cached in a database, that is often a good idea.

I know people who will be horrified reading this. Many think that caching should only happen in proper cache technologies, like Redis pr Memcached. But there are many types of cache. If we need to join those cached data to other data stored in a relational database, we should store them in the DBMS itself – any other solution has an unjustified complexity.

3 – Shorter locks

When we access some data and afterwards we want to modify or delete them, there are two cases:

  • We just do it, hoping that no one will touch that data in the middle of the process. Developers understand the theoretical risks of course. But in practice, for various reasons, most of the times they modify data in an insecure way.
  • Use a transaction, and if necessary make sure that the SELECTs are locking.

These cases remain the same if we access data from a stored procedure. But there is a difference: instead of sending data to the client and waiting for the network overhead and client elaborations, we just do the elaboration immediately – and if we use locks, we release them immediately.

In the first case, we reduce the risk. This is a pleasant side effect, but I won’t argue that procedures are a solution. The solution is to use transactions properly – sorry if this sounds irritating.

In the second case, locks are held for a smaller amount of time. Don’t get me wrong: we will still see locks and potentially deadlocks. But the problem is alleviated. This is the same reason why developers should do their best to avoid retrieving data via HTTP or reading files in the middle of a transaction (yes, I saw this multiple times, and if you analyse your code you may find out that you do the same- that said, there can be good reasons for this).

Conclusions

Don’t get me wrong: in most cases, writing stored procedures is not a good idea. Especially with MySQL and MariaDB.

As I wrote, there are exceptions. I wanted to show the reasons why sometimes it is a good idea.

Use cases?

You may wonder about practical cases. Usually they are quite complex and strictly bound to the context.

A generic example is about means. MySQL only supports a built-in function for the non-weighted arithmetic average. MariaDB has it, plus PERCENTILE_CONT() and ita special case MEDIAN(). The mode can be found with a GROUP BY.

But suppose that we want to calculate a geometric mean over 10M rows. We don’t want to send all those rows to a client when we could send a single value. Instead, we can write a stored procedure.

In MySQL it will still be far from being optimal, because we will need a cursor – as mentioned, cursors are based on temporary tables. In MariaDB 10.3 we can use an aggregate stored function instead. I’ll show this in a future article.

Let’s talk!

Do you use stored procedures? Please tell me how.
Did you have some ideas after reading this post? Do you disagree with me? Please write a comment below, or a post in your blog with a link to this article.

I’ll be looking forward to read your opinions!

See also

Related courses:

Toodle pip,
Federico

Photo Credit

Comments (10)

  1. Here’s a real-world use-case for you:

    We have multiple different applications (some now legacy, but still critical to the business, and all still modifiable) written in different languages by various rock star devs (some who have since moved on), all needing robust database access to the same database. This is my challenge, and I’ve chosen to attack it by implementing a data access layer/API based on stored procedures and with a library for each of the relevant programming languages (Python and Java so far). I’m slowly integrating this into the different applications. It’s the “one ring to rule them all”, to use a phrase from Lord of The Rings. I want to prevent different applications from writing data in different styles, and I want to avoid breaking the applications when we modify the database schema, which is obviously critical in order to implement new features.

    I have a probably naive hope that some day soon the stored procedure implementation in MariaDB (or MySQL or both) will be improved. Particularly desirable for me would be optional parameters. Perhaps I can use JSON, like you talked about in another blog post, though it seems like a fudge in most cases. Unfortunately, not much has really happened in the MariaDB/MySQL stored program world for a long time, as far as I know. Anyway, for now my approach seems to work for me.

    1. Hi, Karl.
      My article focuses on performance, but I agree that there are are reasons to use procedures, and your case seems very reasonable. A more common solution is probably a REST API between the applications and the database. It would be easier to extend and maintain, but then you would add a lot of complexity and network communications. All this could be unnecessary, so… long life to stored programs.
      I think there is only one way to improve stored procedures implementation: talk to MariaDB, require some tasks, and fund them. The cost could be too high for a single company, but not for many companies who want better procedures. I know that it’s hard to convince companies to do such a thing – but this is the way to get what they need.

      1. Yes, long life indeed – you know what they say: applications come and go, whereas databases persist! 🙂

        I have actually raised the ‘optional parameter’ issue with MariaDB, and they gave me a quote for the work which was unfortunately more than I could hope getting approval for. Maybe I could start an online fundraiser or something? Maybe another option is to brush up on my C++ programming skills … 🙂

        1. A fundraising would be a good idea. But I’m not sure that fundraising websites allow you to collect money for something that you won’t do yourself. Also, I’d rather spend money to have faster stored procedures, or the ability to write procedures in external languages 🙂

  2. I very much concur with you on listed use cases.
    Another interesting use case I am applying is to use Stored Procedures in conjunction with Events as gives users/developer ability to run it as adhoc as well as scheduled with certain regularity.

    1. Hi Daniel.
      I tend not to use events, because the same goals can be reached with a cron job (unless you need something that is system-independent).
      A problem with events is that they are executed by one thread only. Suppose you have event A at 10.00 and even B at 10.30. If event A takes 1 hour, event B will be skipped.

  3. I think stored procedure also good for stronger security, client programs would interacts with underlying database objects through stored procedure, furthermore stored procedure protects and control what processes and activities are performed on underlying database objects…

    1. Hi Aftab. I agree that stored procedures are very good for security. But then there is a risk to sacrifice performance for security – which could be fine or not, depending on the cases.

  4. Just being able to read a dynamically generated query in your APM / Diagnostic tool, makes me loathe to use SPs.

  5. A use case with a big performance win. I had hundreds of queries that I turned into a Stored Procedure. The SP ran orders of magnitude faster. The reason is that the Server was on the other side of the country, so the network overhead was much less. Sure, I had to stand on my head to store some data in a couple of tables before the CALL. But the performance saving was well worth it. (Of course, I batched the INSERTs to minimize roundtrips for that part.)

Leave a Reply

Your email address will not be published. Required fields are marked *