Warning, rant follows. If you're not interested, don't read further.

So, yesterday my boss asked that I take a quick look at a couple of the
scripts for a rather major change to our one system that's going live
tomorrow evening. I wasn't overly concerned, because the code had already
been checked over.

Well...

The tables aren't properly normalised. I asked the lead architect about this
and his reply was 'We didn't think it was really necessary.' I have three
tables that are not even in first normal form. Most of the others aren't in
second. The only ones in third are the lookup tables that only have 3
columns. It's not a problem, the architect says. Except this afternoon we
picked up data integrity issues (one country with multiple ratings where it
should have one), and the system isn't even running live yet.

The code is, to put it mildly, an absolute abomination. If the developer had
specifically set out to violate as many principles of good sql code, and to
break as many of our coding practices as possible it wouldn't be much worse.
Select *, everywhere, even on the tables with 100 columns
multiple uses of distinct, even when retrieving result sets that include the
pk column
multiple procedures that do the same thing, just with different hard coded
values. Parameters anyone?
useless error handling (return @@error right at the end of a multistatement
proc)
no transaction usage in multiple updates/inserts/deletes
row-by-row inserts from an etl process.

The whole thing is a performance nightmare waiting to happen. I ran across
one index this afternoon that made me want to hit my head against a desk
repeatedly. A composite clustered index on a 500 character string field and
a 36 character string, neither or which are ever searched on. (the column
that is searched on didn't have an index at all)

There's a view that the developer said 'returns instantly' It takes minimum
37 seconds, on the dev server. That's without production load and with a
cut-down data set.

The person who wrote most of this is a contractor who no longer works for us
The second person is a front end developer who knows almost nothing about
SQL. I'm wondering at this point why I bother at all. I've spent the entire
day today and most of this evening checking code. The only way to fix this
is a complete redesign, from the ground up. But it has to be working in
production by monday morning, so that's kinda out of the question.

I'd offer to give SQL workshops to the developers, but I think I'd be the
only person who attends. After all, databases are easy and anyone can write
SQL. Right?

*sigh*

I feel a bit better now.

Post a reply to this message