Oracle has a language called PL / SQL to compile procedures and functions on the server. These procedures and functions can be called directly in SQL. When they are written correctly, they usually allow a significant performance gain, in addition to being useful and enjoyable to use. Basically, the procedures and functions are an excellent way to provide additional layer of intelligence to your database server, allowing it to perform complex tasks without recourse to external scripts. It therefore saves the communication protocol between database and application. Well … It is also possible in MySQL!
Mini Reminder: Procedure or function?
If you’re torn between creating a stored procedure or function, remember that the only difference between the two is a function fetches a result (even going through tons of intermediate steps), while a procedure is to action. Basically, if you want to have a return value, you need a function. Otherwise, choose a procedure.
In which case use, and how?
You can turn to ProcStock (for “stored procedures”, the term is also often used for functions) wherever you run heavy computations treatments and / or large volumes of data. The huge advantage is that you will not have to repatriate large resultsets to treat them as PHP (for example), then put them in base: everything is done directly in one simple application, which will call the function / procedure.
The MySQL functions that you define are used exactly like built-in functions (although they are usually written in C and compiled with the server … it’s also feasible to gain maximum performance in MySQL, but this is a another story), such as AVG (which calculates an average over the values of a field). Without AVG (syntax: SELECT AVG (field) FROM table), it should retrieve relevant results, add them and divide them by their number: (1 +5 +6) / 3 = 4. AVG did it alone and refers directly 4. Obviously, it is not very serious to have to get 3 lines. But with 20,000 records, it’s different, and performance will be affected, particularly due to the use of RAM required to run the script.
It can also be very interesting to use procedures and functions on the server database when multiple applications frontend in different languages may have to perform the same: rather than writing (and maintaining …) common shares several languages, deport them all running on the SQL server and ask customers to only interact with stored functions.
Conclusion
Lean on your PHP applications, there are certainly lots of things you can veer to stored procedures. The performance gains should be quickly felt, for a minimal learning effort.
In a future post, we will go further with stored procedures, including using parameters, cursors, handlers and whatnot. The challenge is to find an interesting example