I forget when I first started working with Stored Procedures in databases, but it was probably back in 1996 when working on the Teletext Commercial System.
Back in those days we developed against Microsoft SQL Server (as far as I can remember this was version 6) and much of the business logic and database integrity was coded in SQL. I remember we had prefixed the name of the stored procedures with 'sys_'. That wasn't my choice, and I quickly discovered that only the in-built system stored procedures should be named as such. Still it was a small point and since it wasn't my choice and had no detrimental effect it was, by that time, best to leave alone. Regardless, this was a client/server application, and the stored procedures suited us particularly well.
My next position was on a multi tier project (Delphi-Java-Sybase) where we used stored procedures primarily for database integrity and serving the middle tier objects for data retrieval and persistence (that's being a little unfair, it did more than that, but this was the primary role).
On this project 95% of the business logic was stored in the middle tier. By contrast the database used stored procedures, via metadata enabled objects, to quickly retrieve and persist data reliably, and safely. The system, whilst not perfect, was a lesson in good design and worked extremely well. Stored procedures were used in many other places where the data was manipulated (reports spring to mind), after all, if you are relying on retrieving masses data to perform a task, sometimes it's more efficient if (at least the preliminary) processing is done near the source of that data without relying on factors such as network bandwidth getting in the way. Also, the fewer bits you have clogging up the network, the better, especially to a distributed user base and infrastructure.
The stored procedures may have also helped with replication, I don't clearly remember as it was a long time ago, what I do know is that it seemed to work very well, and whilst there was always debate between some of the middle tier developers and the database lead as to who should be doing what, mostly it was harmony between all.
In my next role I initially worked on a system where virtually all the business logic was contained in stored procedures. It was hideous, but this was mainly down to (in my opinion) very poor stored procedure design, and the fact the stored procedures themselves were written in an awful mid-way language (not quite SQL, but not quite any other programming language you've seen before) which was then compiled into very poor SQL code (imagine a stored procedure where the first several hundred lines may be blank lines and you'll get the idea).
The stored procedures in this code could run to thousands of lines and certain members of the team relished the fact they were very nearly un-maintainable. With better organisation (we call it refactoring these days) this diabolical situation could have been eased a little, the bad code wasn't entirely the fault of it being implemented via stored procedures.
This is of course my perception of things, although I doubt many would claim it was good.
The next company I worked for almost had a fear of using databases. Initially this upset me, but after a while you go with what you're given and don't rock the boat (again I say "if it works well enough, leave it alone"). We're talking here a situation where we were storing XML in the database as raw text. Yes, you did read that correctly. I'm going to almost certainly be unfair here (by which I mean I *may* be giving a misleading impression) by saying that one of the primary reasons given to me was that changes could be made to the code, without routinely having to alter the database schema. That's just lame. If one relies on a change in the other then so what? Mind you, this is the same mentality that said procedures running to several pages were "self documenting" and that comments in code were bad as they quickly become out of date (so surely part of making a modification is making sure they are updated... oh, never mind, I've posted about that before).
Essentially (and to cut a long gripe short) the project had moved from Paradox, so the team lead, in my opinion was both unaware of what databases could do (and how well they could do them) and was just plain scared of databases.
In my current position I'm working with, what appears to be, a good codebase. It's Java running against an Oracle Database. One thing has struck me though (and bear in mind I'm new to Oracle) is that I can't find any stored procedures?
I know the code uses Hibernate, with which I'm largely unfamiliar, so this may be why, but as an uneducated outsider I'm a little surprised (although not entirely saddened). If the implementation doesn't need stored procedures then that's fine (but where are the triggers? I know a lot of thinking sees these as evil too).
Oh well, new code, new concepts. That's what it's all about. I look forward to finding out more. Perhaps I'll search the web in a moment for Hibernate introductions.
To close with an aside, many projects are reluctant to use stored procedures as it ties you down to a particular back end technology. That's fair enough for some projects, for example I've written plenty of applications that needed to function against whatever back end they were placed against. The amusing thing is however, in the case of the Teletext system I wouldn't be surprised if the stored procedures outlived the application itself, which was coded in Delphi. I imagine that was converted to C# or Java some time ago (if it's still around). Of course I may be wrong, I've not been back since late 2000.
No comments:
Post a Comment