Too Many Stored Procedures

Posted by Michael Doyon / on 3/27/2009 10:04:00 AM / /

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?".