Playing Chicken With Common Sense

Syed Ali discussed stored procedures and why use them.  I posted my comments there but thought it was worthy of an entry in the blog.  There are some things I hate about them as well but I feel they have some very distinct advantages too so they are a necessary evil.  I don't see how you can argue against stored procedures.  There are drawbacks just as there is with any technology but the advantages outweigh the disadvantages.

Advantages

Advantage 1: Stored procedures are modular. This is a good thing from a maintenance standpoint. When query trouble arises in your application, you would likely agree that it is much easier to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.

Advantage 2: Stored procedures are tunable. By having procedures that handle the database work for your interface, you eliminate the need to modify the GUI source code to improve a query's performance. Changes can be made to the stored procedures--in terms of join methods, differing tables, etc.--that are transparent to the front-end interface.

Advantage 3: Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.

Advantage 4: Cached execution plans that boost performance.  You can't get performance out of sql buried inside your source code.  Any code that is sent over the wire will be smaller when executed as a stored procedure than sending it's content.  So it is also true that a stored procedure will understand the path through the stored proc before execution and cache that for better performance.

Advantage 5: Centralized security, administration, and maintenance.  There is nothing currently in place that can rival the tools built for the database in this area.  Nothing...  It just doesn't exist.

Now...  As a developer I can understand the desire to just stick the sql in the code and move on.  However, this is a bad practice for professional applications and it would lead to chaos on any development team that tried to follow it as a standard.  I say leave that to the hobbyist.


  • 1 Comments
  • E-mail
  • Kick it!
  • Shout it
  • Bookmark and Share

Comments

adnan adnan   United States 6/26/2009 10:16:10 AM #

Thanks for sharing your thoughts. Having worked on applications with and witout stored procedures for over a decade, my thoughts are:

1. With the use of dynamic queries and databases now caching execution plans regardless if they are coming from a client or stored procedures, the performance factor is no longer a big issue. Read here: stackoverflow.com/.../do-stored-procedures-really-boost-performance-in-ms-sql-net

2. If modularizing the code is the reason then Stored Procedures are just as good as isolating the queries in another location like a resource file, xml file, etc. so they are out of sight.

3. Permissions are best controlled at the table and database level. That should provide enough security on the data. Securing application code and queries is the job of the application and there are many ways of doing that in code.

My final opinion is that if the queries are part of the application, they need to be with the application and grow and change with the application and not in the database. The database's job it is to store and serve the data rather than perform business logic on the data. That is the reason some of the Database vendors do not even support Stored Procedures. It opens a hole for separation of business logic vs data access logic. Many times I have seen complex business logic creep into the stored procedures which certainly leads to maintainence nightmares.

Stored procedures have a good use for bulk operations, and when long T-SQL or Pl/SQL scripts are required that client applications are not able to perform. For everything else, use simple dynamic queries from your application and you should be good.

Control panel

RecentComments

Comment RSS