Re-Relational
Given the rise of the internet in the mid 1990's, various events and companies led up to Adobe releasing Flash. Not to be out done, in the mid noughts, Microsoft created their own version called Silverlight. Somewhere down the road, Facebook, Instagram and others put forth React. These can sit on top of a webservice, like, for example,WCF to make it easier for web-facing programs to call home to interact with back-end applications to do useful things like display videos of cats being, well, cats. Occasionally, folks even attempt to use these tools to provide access to business applications.
Some time back, Fred became a hired-gun/consultant/architect to a small financial company to help them replace a dying 150K LOC Silverlight UI with a React front-end, and the underlying WCF API (named Rest.Services for some reason). This allegedly trivial task was budgeted to take three months. Ten months down the road, Silverlight and the underlying code base were way ahead on points while the battle raged on. Eventually, management acquiesced and allowed the entire UI to be rewritten from scratch. The back-end, however...
The application was a financial budgeting/reporting set-up. While it served its purpose from a business perspective, under the hood was a Cluster-O-WTFa that simply would not cooperate with any attempts at change. Additionally, over time, the system became more and more obstinate about providing answers to queries. One morning, Fred noticed that the overnight updates were still running and the CPUs were pegged at 100%. A quick spelunk into the SQL showed why.
Like every other financial system, there were multiple tables containing related data. One might expect each table to have a PK and the relationship-table to have a bunch of FKs.
One would be wrong.
Fred found multiple sets of tables, each with the following design pattern.
create table Aux_1 as ( pk_id bigint, field1 varchar(10), ... ); create table Aux_2 as ( pk_id bigint, field2 varchar(10), ... ); create table RelationshipAux1Aux2 as ( pk_id bigint, keys varchar2(300), ... )
In the keys field, there were delimited FK references to the other related tables. These were hand-crafted relationships in a relational database!
The system would delete the old auxiliary data records and insert new ones, creating new corresponding relationship records in the process. Without any sort of referential integrity, the old relationship records would be orphaned.
With no home-grown concept of cascading deletes to match this set-up, in several of the relationship tables, more than 18 million of 20 million rows were orphaned. Naturally, since the developers of this brillance didn't know to use FKs, they certainly didn't know to use built-in indices either.
Of course, that meant that in order to find anything, the database needed to grind through and pick apart tens of millions of orphaned relationship rows in addition to the few million rows of actual data. After several years of this Garbage Accumulationa, it reached critical mass and the system just couldn't grind through enough data to finish.
A careful delete got the system running again. Fred now spends his days adding indices and attempting to replace the home-grown FK mechanism with actual FKs.
[Advertisement] Application Release Automation for DevOps - integrating with best of breed development tools. Free for teams with up to 5 users. Download and learn more today!