Speaking of databases…

While I’m on the topic, here’s a little rantlet.

I can understand the reasons for moving processing closer to the data by using stored procedures.  It makes sense.  But, it’s a pain. 

  1. It ties me to a particular databaseODBC would let me switch databases at will (it defines a particular SQL grammar that every ODBC-compliant database must support, usually by doing translation in the ODBC driver, letting me switch between Access, Oracle, SQL Server, or even Zim at will) but as soon as you have stored procedures, it’s game over – each database has it’s own language or set of languages that it supports.

  2. It makes me learn a new language.  Okay, for my own stuff I don’t have any stored procedures so I haven’t had to really learn the language, but when I have to install or maintain or debug some other application, I end up in SQL code that makes my head hurt.

  3. You have to upgrade them.  It’s more of a deployment hassle. 

  4. It doesn’t really matter.  For a small site, the database is probably on the same box as the application anyway.  Good use of indexes and good query writing will make the site ‘good enough’ without having to get into Stored Procedure land.

I’d love to see a standard, like ODBC, that abstracted the differences in database engines away from me while still letting me have the benefits of stored procedures (like better control over business logic, and less bandwidth used sending around results that could have been processed inside the database).  Maybe the CLR hosting in SQL server will become that – although I doubt the MySQL and Postgres guys will be jumping at the chance to host the CLR.. Maybe the Mono project guys can make it happen.