}

The Advantages of Writing Stored Procedures

4/4/2018

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.

colorful graphic of digital storage solutions

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

Dan Buskirk

Written by Dan Buskirk

"The pleasures of the table belong to all ages." Actually, Brillat-Savaron was talking about the dinner table, but the quote applies equally well to Dan’s other big interest, tables of data. Dan has worked with Microsoft Excel since the Dark Ages and has utilized SQL Server since Windows NT first became available to developers as a beta (it was 32 bits! wow!). Since then, Dan has helped corporations and government agencies gather, store, and analyze data and has also taught and mentored their teams using the Microsoft Business Intelligence Stack to impose order on chaos. Dan has taught Learning Tree in Learning Tree’s SQL Server & Microsoft Office curriculums for over 14 years. In addition to his professional data and analysis work, Dan is a proponent of functional programming techniques in general, especially Microsoft’s new .NET functional language F#. Dan enjoys speaking at .NET and F# user’s groups on these topics.

Chat With Us