Thursday, February 21, 2008

The good and bad of stored procedure

My work requires me to code SQL stored procedure because 90% the business process logic are in the server rather in desktop application. Subsequently, when the standard product has improved or many bug fixes has been integrated, a new patch will be released to the customer. This has created a nightmare for the developer when releasing those patches. Every customer's customization was done by developer; coding straight into the standard product's stored procedure without concern. Indirectly, this has created every customer has their own version of stored procedure. To let the customer's server has the latest fixes, every stored procedure is required to compare with the standard in order to find out what are custom code has been implemented, in order to bring them over to the new version.

Below are my stored procedure opinion.

The benefits:
1. Minimize network traffic - Example, processing large transaction table which required each transaction meet the business logic verification. There is no need for the data to travel from server to the client's application to verify and then back to the server again.
2. Better performance - Since the stored procedure sit inside the database, which is closest to the data, no doubt it would have better performance compared to client application.
3. Simplicity - Stored procedure is procedural execution, therefore it should be easy to read. There are some exclusion where one stored procedure perform 2 different functionality.
4. Easier deployment - Update the stored procedure will result every user has the latest fixes without having to setup on each user's machine. On the other hand, for web based application this does not count as benefit.

The drawbacks:
1. Duplication - Example, a stored procedure (A) with query M table to return 5 number of fields and you need to create another same stored procedure (B) on M table to return with 6 number fields. Nevertheless, (B) will have almost the same code as (A) except the extra 1 field. So (B) will duplicate of (A). On the other hand, I can execute (A) in (B) just to add the extra field but that will query the M table twice, which is a performance hit.
2. No version control - Stored procedure does not have version itself unless there is comment stated in it. 2 different customer having the same comment version number does not mean they have the same fixes. Developer increase the number once there is fixes or customization implemented.
3. Heavy maintenance for customization - If there are 100 customer and each of them have 5 different customization in their standard stored procedures, there will be 500 different store procedure to look into. Ideal state was to have the customization separate from the standard code; however, it is hard to make it so as the custom code is so tight up to the standard code.
4. Lack of design security (except oracle PL/SQL where the code can be hidden from customer) - The customer can read the stored procedure, therefore some of the design might leak out.

In conclusion, I would say stored procedure are for heavy processing task or for static business logic. Putting customization in stored procedure is definitely a bad move if there are a lot of client and each of them have their own custom code. To solve this problem, I would recommend to put the customization into client's application(preferably web application to save deployment effort) instead of stored procedure, so everybody living happily ever after. :P

For more opinion in stored procedure, check out the Who Needs Stored Procedures, Anyways?

Tuesday, February 19, 2008

Visual studio for free

It's hard to believe that Microsoft giving away their on-shelve-product for free (currently, it is limited to student for academic use only and will be available in 6 months). Free product including developer tools (e.g. VS2008 & VS2005), designer tools and platform resources (e.g. MSSQL 2005).

I wonder whether home user can be considered as student. Hopefully, they include home user as part of the program(therefore, I don't have to install multiple Express edition product in my machine). Nevertheless, it's a good news for student who wanted to learn about Microsoft technology.

For more information about the news, click here.