My Love Relationship with Stored Procedures…

By -

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

Post a Comment

Popular posts from this blog

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

By -
Image
This is a continuation of this post , explaining how the sample app works. Caution : This sample isn’t meant as an example of best patterns and practices. If you see something in the code you would never do, then don’t. Solution File Organization The app has three Visual Studio Projects: the app, the agent, and the shared code library between them. One of the great things about Mango is that now the built-in Visual Studio Unit test functionality is available. By separating out the meat of the work into the SampleShared project, I can also focus my unit testing there. SampleApp1 The app has a MainPage, a ViewModel, and the WMAppManifest.xml file modified to note my ExtendedTask of BackgroundServiceAgent. The Agent is extremely simple with a call to the shared library, an update to the Tile, and a call to pop up Toast. The meat of the work is in the SampleShared library. The AgentMgr manages the background agent : add, remove, find, run now. It is only called f...
Read more

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

By -
Once upon a time, long long ago, I wrote 8-bit programs in Tiny-C with absolute addressing being the only reality.  Then we had Borland Turbo-C with relative addresses and the joys of relative assembly jumps. Enter Windows and the joys of locking and unlocking memory blocks that you must allocated and manage yourself. Then came Visual Basic which evolved into .Net CLR and decreasing awareness of what is happening under the hood.   With VS2010 including F# and enhanced parallelism in .Net4, we actually have to go retro a bit – back to the world of having to understand the hardware. CPU speeds are no longer increasing – I have an old laptop from six+ years ago that ran at 3.2 GHz. This year I updated one of my machines off-the-shelf from Costco.com, an I7-920 with 4 cores and hyper-threading (8 CPUs appears in Task Manager) and 12 GB of DDR3 RAM. This week I ordered from Costco a Phenom II X6 (Yes – 6 REAL CORES) for one of my daughters.  This CPU will (according to Yo...
Read more

Error : /ScriptResource.axd : Invalid viewstate.

By -
This issue is caused when the ScriptResource.axd querystring parameters can not be decoded on the server. This error can be caused from multiple issues with server configuration or the Browser's interaction with the server. The ScriptResource.axd contains all of the clientside javascript routines for Ajax. Just because you include a scriptmanager that loads a script file it will never appear as a ScriptResource.AXD - instead it will be merely passed as the .js file you send if you reference a external script file. In other words it is what the Microsoft Ajax libraries inject into your HTML to allow them to access their javascript functions they need for page manipulation. ASP.NET will use the machine key to encrypt the ScriptResource.axd (and webresource.axd) url's parameters(in querystring), and by default the machinekey is a randomly generated one which may involve the current time. By default there is a different machinekey on every machine, and it changes when the applic...
Read more