| By centralizing and then processing application logic on your database server, stored procedures and functions can yield significant operational improvements. This section looks at these potential advances, including when you should employ these features, and when you should not. For those situations in which stored procedures or functions make sense, you should keep a number of issues in mind to ensure that they deliver the anticipated performance benefits. Because the book's purpose is not to serve as a programming reference for stored procedures or functions, there is not a grand overview of their syntax. Before beginning, it's worth pointing out the basic differences between stored procedures and functions. Difference Between Stored Procedures and Functions These two MySQL features have much in common. Both accept parameters and can then take action based on these arguments. However, stored procedures and functions part ways in two significant manners: Stored procedures might or might not return a result; functions must return a result, and this result and its type must be defined in the function's body. This requirement means that the developer of a function works in a somewhat more constrained way than her stored procedure-writing peer: There is less runtime freedom about what to return, along with its type. Stored procedures can access tables; functions cannot. This limitation alone disqualifies functions from many situations, but there are still many cases in which they add value: The rich library of built-in functions provided by MySQL itself proves that point. One good example in which a function still makes sense is when you have a sophisticated algorithm that is used to help run your business. Even if this algorithm needs information from a table, you could retrieve the data in advance and simply pass it to the function, which would then perform its computations and return the results to the function invoker.
When to Use Stored Procedures or Functions There are many compelling performance-related reasons to employ these capabilities. They include the following: Specialization and consistency Stored procedures and functions lighten the workload on your application developers. Instead of each developer possibly "reinventing the wheel" each time a new operation is necessary, they are free to focus their expertise on writing the finest application code that they can. Meanwhile, the author of a stored procedure or function is better able to specialize and spend the time writing the best shared operations possible. This division of labor usually translates into tighter, cleaner, more consistent code in the client application as well as in the server-based procedure or function. In addition, this is less likely to cause performance problems or even fail. Reducing client workload If your client computers are underpowered, overworked, or linked via a slow network connection, anything you can do to diminish the amount of local data processing goes a long way toward amplifying your applications' responsiveness. Supporting disparate programming technologies By extracting key portions of your applications' logic and placing them into stored procedure or functions, you make it possible for application developers to work with a variety of different programming technologies yet still share access to these operations. Reducing application complexity By definition, consolidating application logic reduces the number of "moving parts," making it easier to develop and debug software. Decreasing network traffic Despite today's ever-increasing network performance, it's still a good idea to minimize traffic between your database server and your clients. By filtering out extraneous information prior to returning results, stored procedures and functions can reduce the amount of data flowing across your network (or the Internet) by orders of magnitude. Security and secrecy Your application might consist of proprietary algorithms that you want to keep as private as possible. A server-based stored procedure or function gives users and developers access, while restricting their ability to examine the actual logic, just as views can be used to hide underlying table structures.
When Not to Use Stored Procedures or Functions Like any powerful tools, there are times when stored procedures and functions are not an appropriate answer to your data-processing needs. These situations include the following: One-off operations Generally, creating and deploying stored procedures and functions makes sense if they will serve multiple users and purposes. On the other hand, if you are solving a single problem within a solitary application, it might make more sense to code your solution within your application logic. However, keep an open mind and plan for the future: If this logic will indeed be of benefit in multiple situations, it might pay to encode it now as a stored procedure or function. Overloaded database server If your database server is either overloaded or underpowered, adding additional processing work might only make things worse. In these types of circumstances, it's safest to plan on performing this work on the client, or spending the time or money to make the server more efficient. One compelling approach is to use MySQL's distributed computing technologies such as replication and clustering to off-load work from your server. Advanced logic needs Although MySQL's implementation of stored procedures and functions is powerful, there might be times that you need the extra capacity and flexibility of a procedural language, such as C, C++, Java, and so on. In these cases, you might find it easier to solve your problem by using your application's native language, or creating a user defined function (UDF).
Stored Procedure Optimization Now that you've seen when to turn to stored procedures and functions, as well as when to shun them, it's time to review a few important performance suggestions. Don't Create Unnecessary Procedures/Functions As you saw a moment ago, not every database operation is a candidate for adaptation to a stored procedure or function. Before you spend the time to make the conversion, and risk cluttering the server with unnecessary processing, think things over. Follow Good Table Design and Query Practices It might seem obvious, but a brand-new procedure or function that runs on your superfast database server will still fall flat on its face if the underlying table/index design is bad or you write subpar SQL. As you develop your server-side logic, make sure to run the EXPLAIN command on all query statements, looking for any of the warning signs that were discussed in Chapter 6, "Understanding the MySQL Optimizer." Watch for Extraneous Database Operations As you go about developing your server-side stored procedure and function logic, always be watching for ways to streamline your code and reduce the quantity and complexity of your activities. For example, a common stored procedure mistake is to encode a SQL statement within a loop when this statement could have easily been run only once outside of the loop. If the loop is run hundreds or thousands of times, it's easy to see the impact on performance of the unnecessary SQL calls. Watch for Loops Stored procedure developers can choose among a variety of looping statements, including the following: LOOP ... END LOOP WHILE ... END WHILE REPEAT ... END REPEAT
Like any other programming language, MySQL stored procedures are subject to the performance dangers of unnecessary looping. Before committing to running logic within a loop, be sure that there are no other, potentially more efficient ways to accomplish the same results. Use Existing Built-in Functions MySQL offers dozens of built-in functions, providing functionality ranging from mathematical to string to logical operations. Before you commence writing a complex stored procedure or function, see if any of the existing built-in functions can provide all or even part of the necessary capabilities that you seek. Handle Errors If your stored procedure encounters a problem, try to handle the error gracefully rather than aborting. To facilitate this behavior, MySQL offers two types of handlers: EXIT and CONTINUE, both of which are tied to MySQL error codes. You can create highly granular handlers that tie to specific error codes. As their names imply, an EXIT handler abandons the current BEGIN/END block, whereas a CONTINUE handler tries to press on. Armed with this capability, you can embed sophisticated conditional logic into your stored procedures, logic that previously could only have been delivered within a client-side programming language. |
