Parting Shots

Are stored procedures cute? Well, they're a little Ada-like and wordy. Many things that you can do in C or Pascal, you can't do in SQL procedures. And most implementations are inefficient. And there is still a portability issue, though that's being resolved now that SQL:1999 has clarified all.

What should a procedure consist of? Answer: The entire transaction from inception to end. The reasoning here is that a stored procedure should enforce business rules, and a transaction is the performance of a set of business rules. It's common to see a transaction containing multiple procedures, but a pessimistic programmer will make stored procedures fairly large.

Should a procedure be public? Yes, but don't expect that the result will be that the DBMS keeps a single copy of the procedure in memory. Procedures may not be reentrant, or the logic and the meaning of unqualified names may not be the same for every user .

Should all SQL statements be in stored procedures? No, because of the bottlenecks discussed in this chapter, and because a program is hard to follow if all it does is CALL procedures.

Should all SQL views or constraints be in stored procedures? No, because the DBMS will optimize for views and constraints, and the code for that is compiled permanently. You are not going to get equivalent performance by replacing the DBMS's compilations with your own code.

Should stored procedures be used even if they harm performance? Yes. We haven't really addressed the security, clarity, and deployment issues herethat's beyond the scope of this book. But, together with performance, these issues add up to a powerful argument for using stored procedures.

The performance issue is a big factor. The judicious use of stored procedures, at the right times, in the right way, will make an application at least three times faster. Getting this part right is where the big payoffs are.