Lately I feel like our systems are running like tops — so it gets harder and harder to identify areas to improve. 

I’m also finding that I have less patience with systems I get to “inherit”.  Everyone is probably familiar with “inherited” systems — these are the ones that were “outsourced” last year, or came in through acquisitions (and were written by folks with “little or no budget”).  And now they are reaching the limits of their scalability as they get loaded up with real production data or become more popularly useful.

A side note on developing without a “large budget”.  I recently met with a development group at another company and I was talking about how at my company we have a database engineering team (nee “Development DBAs”) that is responsible for all SQL and PLSQL work.  Our DB team is part of the overall development group and works closely with Java and Web developers to write all of the necessary database code.  We have roughly 5 DBAs to support about 80 developers.  In other words, our development organization is roughly 5% DBAs :-).

The company I was meeting with was very small — maybe 10 developers.  And one of their senior developers was aghast at the idea that Java and Web developers had to “wait” for Database developers to write SQL and PLSQL code.  “I’ve never worked anywhere that had such a LARGE team of Database developers”, he said.  “That doesn’t make sense — our Java and PHP people write their OWN SQL — why do you need Database developers?”.  Needless to say, I’ve been asked many times to provide advice on how to “fix” the database that their app runs on, since it’s not running very well. :-|

Anyway, back to “inherited” systems.

Maybe it’s because I’ve been spoiled by our environment where everything is fairly well modularized and just about everything is in PLSQL stored procedures — all SQL and PLSQL code is written by the database engineering team and their isn’t any SQL in the Java or Web tier — but, I’m amazed at systems that don’t do that and abuse other concepts besides.

I know I sometimes get “religious” about requiring all database access code in stored procedures.  But actually, that’s often just a “going in” position.  I can compromise — as long as the database access code is sufficiently modularized so that at some point in the future we can possibly move it into stored procedures.  You remember modular programming, don’t you?  You don’t?  You know — the idea that you write libraries of functions and “link” to them or call them through defined APIs?  I’ve been amazed at the amount of “inline” SQL I’ve been seeing lately — which leads to the “We can’t possible convert ALL of our SQL to stored procedures — we’d have to look at HUNDREDS of files to find all of them”.  Somehow this becomes an indictment of stored procedures — instead of invoking disapprobation for the poor non-modular programming practice in the first place.

I’ll close with a final example — this on resource usage.  Back in the day :-) I had the opportunity to work on client server systems — and everything was about latency and roundtrips.  I think today some people forget that even a web-based application which talks to a database still has client / server attributes.  The web server is a client to the database server (when it’s interacting with it).  As such, you should probably minimize roundtrips and latency between them.  I recently encountered a system which made an assumption that the latency between the 2 would essentially be zero and as such didn’t worry so much about reducing rountrips.  Simple actions on web pages would routinely require 100-200 roundtrips.  This would not have been tolerated “back in the day”.  And the one day when the database was relocated to an out-of-town data center, suddenly “data response time” became a problem.  :-|  Again, where was the outcry?  Over the profligate number of roundtrips?  No.  It was — “Why is the database so slow now?” :-)

(I used to advocate that developers work on systems that are slower and smaller than the ones customers will eventually deploy on — you’d be surprised at how fast and small the resulting code is.  Even today I prefer that my development databases have slower disks and CPUs than in production — keeps me working hard on writing more efficient code).


Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder