My Love Relationship with Stored Procedures…

Wayne cites a few issues which I respectfully take issue with:

  • Source Control: the problem he cites is an issue with the Source Control that he elects to use – it does not occur with all source control systems.
  • Deployment: this is again an issue tied to the tools (or lack of tools) that you are using.  I believe MSDeploy eliminates most of these issues.
  • Security: This issue was not addressed, and having TSQL, especially with MVC creates a much higher risk of vulnerability. True, using parameterized reduces it – but if you are working with a team of any size, this will not be enforced across all code without a lot of effort.
    • It’s easy to scan a project and detect if any command is set to text instead of stored procedure (SP).
  • Normalization of Code: By this I mean that there is just one occurrence of code to get data from the database, an SP. This code may occur in many places in the C# code. Each instance would be coded individually (in different methods in different classes). If there is a need to change the logic then you have to find all of these instances and change them. My preference is to use SPs as a contract mechanism.
    • It allows the database developer to do whatever he needs without impacting the C# developer.
    • It allows dummy data to be created to satisfy the C# developer and thus allow better parallelism with large development teams.
  • CleanUp: This is again a minor issue that is easily resolved if the SPs implement performance monitoring. By performance monitoring I mean that the start time and end time of the SP is captured by SQL Server and logged to a table with the SP Name.  It is trivial then to:
    • Do a join and identify all of the probable dead code
    • Identify TSQL code that may need refactoring (or refactor part of the database).
      • If the TSQL is placed in C#, the ability to refactor is greatly reduced – often resisted totally by the C# developer unfortunately resulting in a suboptimal product being produced.
  • Performance
    • While it does not impact actual performance, using templates or raw TSQL severely impacts performance tuning.
      • You give up the easy ability to implement performance monitoring of TSQL code.
      • You give up agility in refactoring the database.

I have had to take over C# web application that use templates or raw TSQL and I HATE them because often performance problems is in the database or the database needs to be modified to add a new feature.  With stored procedures, I can make the changes and then recompile all of the stored procedures. SPs that fail to compile are items that I need to refactor because of the database changes. With this code, I spend hours or days tracking down every query impacted and then have to do a complete test cycle to verify that I have caught them.  With a large team, you will have pure database dev and pure C# devs --- without a contract mechanism and isolation of development worlds; you will end up with a majorly frustrated team.

 

The above does not apply to a one man developer shop that does both sides and do not expect to grow to a larger development team. Once team size closes – the above become increasingly important.

Comments

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape