Improving stored procedure performance
So far, you have seen how to write basic stored procedures as well as triggers in various languages. Of course, there are many more languages supported. Some of the most prominent ones are PL/R (R is a powerful statistics package) and PL/v8 (which is based on the Google JavaScript engine). However, those languages are beyond the scope of this chapter (regardless of their usefulness).
In this section, we will focus on improving the performance of a stored procedure. There are a few ways in which we can speed up processing:
- Reducing the number of calls
- Using cached plans
- Giving hints to the optimizer
In this chapter, all three main areas will be discussed.
Reducing the number of function calls
In many cases, performance is bad because functions are called way too often. I cannot stress this point too much: calling things too often is the main reason for bad performance. When you create a function, you can choose from three types of functions: volatile, stable...
