.net_2.0

SP or not to SP

There are plenty of opinions on the matter and the purpose of this entry is not to take one side or the other and instead explain why sometimes it is best to share the viewpoints of both camps.

While working on Sites-Easy  (my version of the original CSK 1.0 StarterKit), I introduced a bunch of new features.  One of which was actual stand-alone modules, that less some items such as membership lists and the likes, could be self contained.  This required duplication of tables with new names but essentially they contained the same info the original template table required.  To access the data, also meant duplicating all of the stored procedures.  In benchmark studies usually stored procedures are not used (such as PetShop for example) because the java critics would complain that since such and such database or application server didn't support them - it wasn't a one on one comparison.  However, incredible performance can be obtained from properly constructed inline generation of stored procedures. 

So I had a choice of either constantly changing stored procedures or creating new ones when I modified a table structure or generated a new table - or re-visit why I was generating the stored procedures on the fly.  The reasons to adopt dynamically generated stored procedures became a necessity as less work to maintain the stored procedures, and being dynamic I could use a single method to generate the updates to like tables based on parameters.  

"To get more usually means to code more..." 

This is true - initially, but re-usable code results in less code. For example creating a base reference class that is inherited by all of the DALs in the application can actually reduce code complexity and standardize parameters used throughout an application.   Using a SQLHelper class, can eliminate the redundancy of coding the same data access routines that are used.  The list can go on.... But what about performance?

The debate still rages to this day whether accessing stored procedures within SQL or dynamically built queries in C# / ADO are better performing.   I can tell you from the number of benchmark studies I have done or overseen, that either or can be equally as fast if constructed properly.  So, performance really is not the issue as code generated queries can still be cached by SQL Server and in some cases will benefit more from SQL optimization than hard coded stored procedures in SQL - especially when you have stored procedures that have a bunch of "if...then" statements to determine how to insert or manipulate data.  Remember SQL in order to optimize generates the execution plan based on everything in a stored procedure...

Code separation?  If you are writing n-tier applications, then you are isolating  database calls and therefore the notion that having all stored procedures results in greater security is actually false.  Many argue that it makes maintenance of database related functions in a application easier if all contained in SQL Servers - which was true back in the days that a DBA was only skilled in one area.  Today - DBAs better be cross trained and know how to code - as much as a coder should know the database end. Simply put - most of all the reasons are mute points.

Optimization tip:

Even if you use stored procedures you should get in the habit of explicitly assigning the SQL data type to any parameters passed in your applications code.  SQL suffers the same issues that the CLR does when it comes to boxing and unboxing and performance increase of nearly 12% can be seen if done - over just adding the parameter and setting the value.

Example

(Decent Performance):

cmd.Parameters.AddWithValue("@name", name);

(Best Performance)

SqlParameter PARM_NAME = new SqlParameter("@name" , SqlDbType.NVarChar,50);

cmd.Parameters.Add(PARM_NAME.Value= name); 

 Talk about simplifying?

One of the techniques to handle data is declaring SqlParameters and the type.  Then in code you can simply call the parameter and assign the value. But what if you want to pass a ILIST to a data access method you write and automatically generate the Update etc commands and based on the ILIST items assign the proper parameter to be used?  It can be done actually in a more robust fashion than what most SQLHelper classes actually do.  

One of the really decent aspects of using the ILIST is that they can be used like jagged arrays in a fashion, and are totally sortable.  Meaning that you can do adhoc queries on items in a ILIST and return a item and its contents with relatively little coding.  Here lies the flexibility of .Net 2.0 in that DAAB application blocks do not necessarily have to be utilized to the hilt - and that with a little bit of creative energy - building customized SQL queries in code - can be simple and yet handle very complex or long statements.  

For instance - any time we pass data from our BLL to our DAL the data will always be in the format:

• Type
• Name of Variable
• Value

Based on the type we can search through a ILIST of Parameters and grab out the actual SQL column name and SQL data type that matches the passed in parameters to our method.  From there we can call a method that builds the insert or update command text for us by looping through the passed in parmeter ILIST.  Sounds more confusing than it actually is I assure.  So lets back up for a second:

Say you have a form that updates 25 different columns in a table.  On one hand you COULD create a static string that contains the Update Statement which will be relatively long.  And because we are doing this in C# and not in SQL Management Studio - much more error prone when we need to add a new column to our table and form (the VS GUI is not condicive to building long strings visually IMHO).  A better method is standardize what the parameter names (variables) we define in our method.  Compare those names to our static list of defined sqlparameter strings and viola.  And while it can be argued that we are adding more code - we reduce our DAL layer to merely nothing because we allow the queries to be constructed on the fly.

Considerations and how to break this down:

Simple Operations: Typically these will be tasks such as "Update MyTable Set @MyValue = myValue Where this = that".  There is no real issue here as the task is simple. 

Multiple Staged Operations:  These operations will be built on top of the Simple Operation but require execution of possibly SQL functions or actual stored procedures in SQL. For instance inserting a new record requires another table to be updated as well.  

Complex Operations:  Typically where a transaction is required and multiple staged operations must occur on the database.

The advantage of passing a ILIST<Collection> versus declaratively adding the collection item by item.

 You see this code all the time:

public int AddSection
            (string mode,
            string name,
            string title,
            string menuTitle,
            string description, ...)

In a lot of instances this same code will be duplicated over and over again, throughout an application.  Fabulous technique for a clip and paster but what if I told you YOU DIDN'T have to program this way?  Would you agree that having 5 methods versus 100's of methods would give you more flexibility and allow for easier extension and maintainability of your application?  Would you argue however, performance may suffer?

Code bloat:  

When developing applications that are n-tier, you have the UI, BLL, IDAL, Factory, and DAL classes.  If the BLL needs to perform some kind of data operation - separate methods have to be created in the DAL which also results in having to modify the IDAL as well.  In the end - unless you have developed some overall generic data methods - I can assure you tons of redundant code is out there and to trouble-shoot development issues you have possibly 4 different classes that the error can be in.  In my own Sites-Easy project I have 68 separate projects and 40+ separate DALs (and corresponding IDAL and Factory classes).  While I have some 'short cut' methods that I could use - over time - you forget about them and end up just adding new methods..Code bloat in itself is a performance factor.  The more code there is - the greater the memory foot print and more time it takes for the CLR to find that method in particular. 

You can reference my other blog entry on Building SQL Queries Automatically in C# using Generics that illustrates how to create a single DAL that provides a code bloat solution when dealing with DALs.

In summary - there are instances where SQL stored procedures are definitely better.  But better has to be defined by answering these questions:

• Does it save development time?
• How does it impact manageability?
• Where does the greatest amount of flexibility get achieved? 

Based on your answers - depending on the base code implementations for handling data access functions - lies the answer if one approach or a mixture of approaches is better suited.  Sometimes corporate doctrines may restrict those choices - but if you have the freedom to choose - the old argument of issue of performance is debunked and similarly as well so is the idea manageability is limited.  Personally if the only code you have to manage is in the BLL to add a new parameter - then that is better management of code.  When everything is SQL - that management of changing data structures etc - impacts 4X  more the actual C# (or other language) code management tasks as many tiers will be affected.