Perhaps the title should be the disadvantages of sending SQL batches from client applications. Many people think that the primary advantage to stored procedures arises from the fact that they are optimized and compiled once and then remain in memory, hopefully, to be used again and again. While this can be an advantage, there are also problems. A stored procedure optimized for one set of parameters can perform poorly with a different set. We will deal with the problem of optimizing stored procedures in a future blog.
The greatest advantage to stored procedures comes from the reduction in network bandwidth required compared with submitting multiple SQL commands from a client application. Of course, individual commands themselves require network traffic, but there is also return traffic generated by SQL Server, much of which is not evident without a close examination. When a stored procedure is executed from the client, the SQL submitted by the client is little more than the name of the procedure and the values for needed parameters. If the stored procedure is designed correctly, the return from the server consists of little more than the zero value indicating successful execution.
In the world of IT, the costs of code development and maintenance cannot be overlooked. Here, too, stored procedures provide an enormous benefit. Well-designed stored procedures become tools in a shared library. Developers can build upon existing stored procedures, rather than duplicate code logic that has been developed and tested, but which is not available for reuse because it lies buried in the client-side code of some application. Unfortunately, many developers create code without fully considering the cost of code maintenance. In many organizations, the costs of code maintenance actually exceed the costs of initial code development. Stored procedures address this issue by centralizing the code in one place, on the server.
If SQL batch code embedded in client applications needs to be altered or upgraded, the client-side code must be modified, recompiled, and tested, and then the new version of the client application must be redeployed on many workstations. Difficult and time-consuming at best. If the client applications do nothing except invoke stored procedures, necessary changes need only be made to the stored procedure code itself. As long as the stored procedure code continues to expect the same parameters and return the same values, the client code need not be modified at all. Client applications will call the same stored procedure tomorrow that they called yesterday, oblivious to changes in the server-side T-SQL code.
Stored procedures also provide a means of security management that is both stronger and also easier, a fact certain to please database administrators. Ensuring that many lines of SQL batch code submitted by a client operate under appropriate security restrictions is time-consuming, and security holes can be easily overlooked. Stored procedures created with the EXECUTE AS statement can operate under security credentials different from those of the client, permitting administrators to fine-tune permissions to the "Goldilocks" ideal. That is, not too little, not too much, but "just right".
Conclusion
While the execution performance of stored procedures receives much attention, it is the reduction in network traffic that provides the largest and most important performance advantage. Even without a performance advantage the cost savings in development and maintenance, as well as superior security options, are compelling reasons for integrating SQL batch code into server-side stored procedures.
AUTHOR: Dan Buskirk
Related Training:
SQL Server
