Leverage the power of stored procedures

January 10th, 2015 Posted by Technical No Comment yet

Abstract When tasked to develop an application we believe there are three options and you can pick any two ! Do it right Do it quick Do it cheap Option 1 should be a default but ‘right’ is a relative term!  And depending on experience if not chosen then it always has repercussions on maintenance.…

Abstract

When tasked to develop an application we believe there are three options and you can pick any two !

  1. Do it right
  2. Do it quick
  3. Do it cheap

Option 1 should be a default but ‘right’ is a relative term!  And depending on experience if not chosen then it always has repercussions on maintenance.

In this post I will demonstrate one way of doing it right. Occasionally I will refer to an application that my company recently and successfully developed using this approach, along with the reasons why it was successful.

Business Requirement

Most forecasting applications (like MRP or Asset Management) has an inadvertent need of creating orders … it could be either Sales Orders, Purchase Orders or Work Orders.

These orders are based on a group of setting. The screenshot below outlines the settings for a healthcare asset management product

blog-Leverage1

 

Some of the underlying requirements (asked during the design and analysis sessions) were

  • Settings will be added or updated frequently
  • Settings will be different for different entities (departments)
  • Business logic around each settings may be updated from time to time till
  • Performance needs to be optimized since real time orders are required.

Technical Solution (Database Only)

Given the requirements we encapsulated the entire business logic using stored procedures … instead of using In-Line SQL or LINQ. While Linq does have some advantages such as abstraction and support across multiple databases we went with the stored procedure route for

  • Ease of deployment – the code does not need to be compiled (and deployed) anytime a business logic needs a change (a key requirement).
  • Network traffic – sprocs need only serialize sproc-name and argument data over the wire while LINQ sends the entire query. This can get really bad if the queries are very complex… which is usually the case a continuously moving requirement as such.
  • Performance – using sprocs we can optimize the queries using HINTS, indices and other techniques to speed up the transactions. While this can be done in-line sql – testing each time will require a code compilation.
  • Maintenance – a set of stored procedures is a very easy way to inventory exactly what queries may be running on the system. Using in-line queries one needs to run a trace of that covers an entire business cycle, or parse through all of the application code.
  • Troubleshooting – error logging (in database tables) allows us to pin point the source of any issues and updating the logic is only a matter of updating the stored procedures instead of … well you get the picture J
  • Below depicts the database objects
    • Table for storing the settings

blog-Leverage2

  • Log table

blog-Leverage3

  • Stored Procedure – where all the business logic is written – currently at version 66 !

blog-Leverage4

Conclusion:

As you can see the sproc is under continual revisions. The decision to implement the solution using a sproc for business logic was the key. The sprints in agile process were quick and easy to roll-out.

Yes there are various ways to hold your nose … I’ve demonstrated a few below

blog-Leverage

 


Sam Banerjee

samSam brings years of Business Intelligence and Software System Analysis experience to Medullus Systems. Prior to being a partner/co-founder at Medullus Sam lead several scale projects in the BI world in big name corporations like Bristol Myers, Frasenius, and ADP Payroll. Sam brings new ideas to improve BI in companies, products and projects. Sam is a certified Microsoft BI Developer and holds a Master in Computer Sciences. When asked about himself, Sam says “If you can’t measure it, you can’t manage it. For this reason alone, cutting edge software, that fits your business, needs to be on your radar screen and my cell phone number on your speed dial!” On his personal life Sam is a proud husband and father to 2 boys and enjoys his “cutting edge” drum-set, rock-shows and the New York Giants.

No comments yet. You should be kind and add one!

Leave a Reply

Your email address will not be published.This is a required field!

You may use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

− 3 = 5