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 |
|
| Merge cronjobs that operate on the same data set. |
|
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.
