Playing Chicken With Common Sense

I read Paul Nielsen post on stored procedure. According to him stored procedures are good for the applications. He is not only talking about logical or business process stored procedure, but he also encouraged developer for CRUD stored procedures. I think, it is good idea. There is no harm to use them. If database server have that capability, then why not CRUD stored procedures. Here I am quoting his valuable opinion.

The only real solution is an abstraction layer that fully encapsulates the database. Every database fetch, insert, and update must go through this access layer. Just as SOA provides this encapsulation for processes, the database needs the same black-box API. If the database team wants to refactor the database to improve some feature, it’s free to do so. If a new feature is added, the database team can add that feature and modify the API. It’s very clean, easy to refactor, and the database is now an on-ramp to the corporate roadmap.

If we look at the history of database development language, you will find either PL/SQL or T-SQL, these languages are not changed. As Paul made a cogent argument about T-SQL that it is still same for the stored procedure, which written decade ago.

I will appreciate you comments. Feel free to share your advice.

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


Mattias Jakobsson Mattias Jakobsson   Sweden 6/18/2009 4:38:05 PM #

I did read that post as well. Me, however, am alot more for using a OR/M then using sp´s. I have expressed my opinion on my blog:

Adnan Ahmad Adnan Ahmad   United States 6/18/2009 5:43:04 PM #

I don't buy into stored procedures. They are so inflexible and diffucult to maintain as your data schema changes.

My solution is to have your SQL Queries in your code where you call your stored procedure. Having the query their instead of just a stored procedure name makes your code more readable and understandable as to what is going on.

Also, since my queries have business logic in them that is related to the application I thinks it makes more sense to have them in the application than in the database. The database should just store your data and not know anything about your application IMHO.

Brad Merrell Brad Merrell   United States 6/19/2009 8:40:14 AM #

The reason T-SQL and PL-SQL has not changed in all these years is that it is so hard to upgrade and debug the stored procedures and SQL queries.  

While you can do alot of fancy data manipulation in SQL, it is best to keep SQL simple as a storage and retrieval software.   I suggested limiting database programming to CRUD.  Since business is in continuous change mode, put business logic in the technology that is continuously change, the code.  Much like home remodeling, everything can be remodeled but the foundation remains unchanged. Let the database be foundation of the application.

Phillip Jacobs Phillip Jacobs   United States 6/23/2009 1:52:47 PM #

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.

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.  Leave that for hobbyist.

Control panel


Comment RSS