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

Results

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:

Conclusion

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.