Irrelevant thoughts of an oracle DBA

7 July 2008

Knowing the history of an application

Filed under: rant — Freek D'Hooge @ 1:30

The company I work for has many hospitals as client, and most of them use the same set of applications. One of those applications (which will be kept unnamed) is always giving us performance problems. Through time we discovered many design problems in its database structure, such as not using primary keys or foreign keys, storing dates in separate year, month, day fields,….
Also the developers seemed to be very into using cryptic names for the table and field names (all table names are like on150, on010, pt010, on070, zv054, … with field names as tgcde, innr1, crcmg, …), which is really handy when you have to tune a query. Another nice design decision they made was to include a column in each table storing the table name, and then include this column as leading column in almost every single indexes (including in the unique indexes).

A couple of days ago we were asked again to tune 3 custom queries, which started to take hours (days) to run after the application had been upgraded. Unlike other occasions, this time we did manage to receive a part of the db layout from the application vendor, including short descriptions for the tables and columns.

From the moment I looked at the documenation, things fell into place.
You see, I’m old enough to still had a cobol course at college, and the documentation layout remembered me very much to cobol key-sequenced files.
Apparently, when they switched to using oracle they just took the files and recreated them as tables. Without making any further change in the db design. Neither did their developers made the mentally switch towards “set oriented” programming (read: sql) to retreive data, but instead continued to “read” from the tables as they were still using files (In the email conversation I had with their customer support, they where actually still speaking of files instead of tables). Even the mystery of the column storing the table name was explained, as each “file” layout in the documentation had the sentence: “number of record formats in this file” next to it… .

Of course, knowing why the application has such a dreadful design does not help me much to rewrite the sql statements. But at least it stopped me from willing to kill the developers, as I now know that they just don’t realize that they are no longer working with key-sequenced files, but with a relational database instead.

Ok, I admit… I still want to torture them. But just a little bit

Advertisements

3 Comments »

  1. It appears that the developers are still maintaining the application – so they are being tortured :)
    Maintenance is nature’s way of punishing programming errors…

    Comment by prodlife — 8 July 2008 @ 19:26 | Reply

  2. Chen,

    Sadly enough the client is looking at me to fix the problem, because the application vendor saids the queries are custom queries that are not part of the application… .
    And of course the guy that wrote the query does not work anymore for the client and nobody can even tell anymore what the query is precisely supposed to do.

    Comment by dhoogfr — 8 July 2008 @ 21:22 | Reply

  3. […] – bookmarked by 4 members originally found by stuartnorfolk on 2008-10-31 Knowing the history of an application https://freekdhooge.wordpress.com/2008/07/07/knowing-the-history-of-an-application/ – bookmarked by […]

    Pingback by Bookmarks about Torture — 7 December 2008 @ 12:30 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: