Monday, April 12, 2010

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.

No comments:

Post a Comment