The Jonajo Blog

Using Stored Procedures to Improve Performance by a factor of 400.


Have you ever used Stored Procedures to build your web app? If you were a software engineer in the nineties, like I was, chances are that you have. But what about today? Are Stored Procedures still relevant?

I used to build significant business logic and entire web apps using nothing Oracle PL/SQL. Although it was server heavy, it was quite an efficient way to run code.

Today however, many coders are not very familiar with stored procedures, and instead issue dynamic SQL statements to the database directly.

Use case: PHP Cron Job

We were dealing with a PHP Cron Job which had very poor performance. Here are the observations we made:

  • Cronjobs overload the server
  • Cronjobs fail without warning
  • Cronjobs time to execute increases with more data (games, tournaments, matches)
  • Cronjobs may not finish in time and overlap
  • High CPU use and Network traffic
  • Low-powered servers

After inspecting the code, we found out that the code had various nested loops making calls to the DB over the network to issue dynamic SQL statements. This is what the architecture looked like:

This approach required a lot of time spent in network calls. Furthermore, the database could not optimize queries which really should be written as joins rather than as individual DB calls in a loop.

Solution: Move logic to Stored Procedures


Solution Benefits
Move data intensive logic from the PHP scripts in cronjobs, to stored procedures in MySQL
  • Large reduction of DB calls over the network.
  • Reduced cronjob server load
  • Allows DB SQL Optimization on the server
  • Cronjobs run faster.
Merge cronjobs that operate on the same data set.
  • Reduces dependencies between cron jobs
  • Allows potential Query Optimization
  • Users see data updates faster


By moving data intensive logic from the PHP Cron Jobs into Stored Procedures, we were able to reduce the running time of these cron jobs significantly:


Stored procedures



While stored procedures may be old technology, do consider them when you are dealing with relational databases, and you are concerned about performance.  At Jonajo Consulting, we will apply the most suitable technologies to solve the problem at hand.

About author View all posts

Kristian Widjaja

Kristian Widjaja is the Founder and President of Jonajo Consulting. He has over 20 years of experience in Silicon Valley companies such as Oracle, PayPal, and various startups.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.