Wednesday, June 17, 2009

Stored Procedure Hell

I fancy myself as a very competent user of SQL (particularly T-SQL), and from school, on-the-job training and experience I've come to understand a comprehensive list of things SQL does well:
  1. Basic CRUD operations
  2. See number 1
  3. Some very basic data manipulation - some of the stuff found here.
And that's it. I've found a problem begins to occur when people start going outside this basic mold and attempt to put in real application and business logic into stored procedures. Say, calculating a custom coordinate by referencing a seldom updated flat table containing coordinate grid intersections. Or tracing a major road or utility network. It isn't too odd this happens - a lot more people know or can quickly learn SQL. Compiled on the database, stored procedures can have impressive performance. The inevitable result, however, is not pretty. Half of your application logic is living on the database, the other half hangs out on (hopefully) a real source control system. You become heavily tied to that database vendor, since they all seem to have their own SQL dialect, and undoubtedly you've had to use their more advanced and esoteric features to implement something. And I've found the stored procedures are rarely extensible for multiple applications. They can be, but in practice they are built for very specific purposes and are tightly coupled to the applications that need them. Example: a stored procedure that calculates a custom coordinate, like the example mentioned above, is entirely coupled to its table. There would be no easy way to change the data source to a more commonly updated table or, ideally, a standardized web service. Also, the calculation itself isn't broken up into discrete methods, which means modifying one part of the long procedure will quite easily break another part in ways not necessarily easy to discover.
It's for storing data, not making a calculator for it.