Too Many Stored Procedures

Like many others before me, the code base I am working on has been in existence for many years. It is massive in scope and in actual source code. Behind it all lies a massive database structure with a single common means of accessing all of the data from the application: stored procedures (sprocs). While this may seem like a good idea, there is a point where things get out of control. Our current system has about 2,000 tables and nearly 14,000 sprocs. The only logical grouping of the sprocs is found in the naming conventions used, though this proven itself inconsistent as well. To make matters worse, there is far too much business logic in many of the sprocs. Now, I understand that number of lines of code is not a very reliable metric, but some of the sprocs are in excess of 2,000 lines with many of the sprocs at least 300 - 500 lines long.

I recently did a trace through a stored procedure that didn't appear too bad on the surface (it was only 640 lines long). The trace revealed a disturbing revelation. The shortest path through that stored procedure involved calls to 4 additional sprocs. That is acceptable, but that is to determine that it has nothing to do. The shortest functional path through involves calls to over 80 additional sprocs...one of which calls itself recursively. Needless to say, it was a painful experience to trace through to figure out what it is actually doing (oh, I forgot to mention that our specs and documentation are virtually non-existent).

So this all brings me to my current point. There is a time when there are too many stored procedures and any benefit gained by them are lost through the loss in maintability. With the exorbitant number of sprocs, most of my co-workers (myself included) will take a short look to see if one already exists meeting our needs, but typically just take the easy road and write a new sproc further propagating the problem. I would like to spearhead an effort to bring some sort of control to this situation and to bring it back to a maintainable state. The million dollar question is simply "How?".

Starting my blog...

I've been putting off creating my own blog for about a year or so. I've had good intentions but I never seem to give myself the time to do so. So, why now? What makes today so different? Nothing really. I've just become fed up with how I'm utilizing my own time. Life is too short to not go after the things you want in life. My driving motive for this blog is to provide a place for me to document lessons learned (often the hard way), my thoughts on new technologies, and provide a running dialog of my professional growth. I also intend to use this blog as one of many means to help improve my knowledge on any topics discussed while improving my communication skills (hopefully). I tend to be over-ambitious but we will see how this chapter turns out. My goal is to post new messages every few days. I think that is a reasonable expectation but only time will tell.