The Sex and Cash Theory for Programmers

Whats up with Stored Procedures these days?

20. October 2008 14:22 by Scott in   //  Tags: ,   //   Comments (39)



When life throws you hard balls, you can either step back and take a swing or bunt the hell out of it.  I just recently went to an information session on T-SQL, Stored Procedures and now LINQ.  These folks discussed stored procedures and why they are so important in the world today.


They stated ideas such as:

  1. Stored Procedures optimize queries.
  2. They stop SQL Injection attacks.
  3. Stored procedures are concrete and cannot be changed.
I wanted to briefly state a counter argument against stored procedures and for T-SQL and LINQ:
  1. Stored procedures do not optimize queries.
    • A SP is not an optimized query, it does not save time.  SP’s used to save time and have been faster in the past, but more and more RAD development has been done without SP’s.  The development has caught up with SP’s and dynamic SQL has now caught up  and in some cases surpassed SP’s.  These days parameterized queries are now cached just like SP’swhich put them on an even playing field.
    • Microsoft LINQ and ADO teams both admit that SP’s are not faster than dynamic SQL.
    • Benchmarks have also been completed which explicitly show SP's are slower over small queries and tie dynamic queries over larger ones.
  2. Stored procedures do stop injection attacks, but you can also do it with parameterized queries. 
    • SP’s are good at this, but it can also be done with parameters.  Putting a value into a parameter means that it will not be a part SQL query string at all.
  3. Stored procedures are very concrete and are hard to change as a developer with a DBA.  This is the problem.
    • As a developer when you push a product out the door, you want to be fast at this.  Developers don’t want to learn two languages in order for their product to be pushed out the door and then go back to the database every time code needs to be changed.  They want dynamic abilities.  When SP’s are hard to change, it might be a bit harder and take longer when you have a DBA.  The inability for SP’s to be changed or created fast is the issue with this kind of development.
  4. SP’s mangle the three tier architecture designed for apps.
    • Instead of having a structure which separates logic from storage, you have storage and logic on the same tier.  This will cause potential problems down the road.
  5. Business logic in a SP does not scale.  Business logic should be in code and not in the storage center.
    • When you have multiple database servers, it is more difficult to keep the triggers and SP’s synced.
  6. There is no Version Control for SP’s.
    • In a place that can be disastrous if you screw up, it should have something.  This is one big reason why people like to avoid SP’s like the plague.  They run away and never move back into town.
  7. SP's are NOT cached and Are NOT Pre-Compiled
    • SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.
If you see the new technologies coming out of Microsoft now, there will be no need for SP’s in the future.  LINQ is taking care of most of this by allowing for a data layer cheaply and efficiently with very little effort by the developer. LINQ handles all these problems and if you truly like a confirmation on all the things I said, just search the internet for Stored Procedures v.s. Dynamic SQL.
The debate these days has two sides. Not “Republican or Democrat”, but “Progressive and Non-Progressive”.  The Progressive folks are not using Stored Procedures these days.

kick it on
If you liked this post, please be sure to subscribe to my RSS Feed.