I used to be a .NET Developer and DBA for an appliance manufacturer in a previous life.  The company was your basic good ole’ boys shop where maintaining the status quo was priority number one.  It was such a staunchy work environment that you could determine where an employee was on the ladder based solely upon their attire.  High Level Execs wore full-out suits, with ties, vests, and sports jackets.  Your typical salaried employee generally just wore a button-up shirt and tie.  Shift managers wore polos, while the lowly hourly assembly workers donned jeans, steel-toe boots, and ratty shirts.  Basically, this manufacturing company is stuck in 1976 and doubled as my personal hell.

When I started working for this firm, they had an existing DBA who fancied himself a pretty smart guy.  Shy of the countless double-negatives and mis-information he would spew on a daily basis, he was tolerable and he kept to his world of existing “black magic” processes.  He liked to refer to himself as “NULL Man” and act like a super hero when he was “fixing bad data”.  For the first 9 months of my tenure, I really had no idea what he did on a day-to-day basis, but had a feeling his primary goal was to figure out how to interject himself into as many business critical processes as possible in order to assure job security.

I was hired to bring up new apps and databases and do some .NET development as well.  After about 9 months on the job, the Director of IT called me into his office and asked if I could rework a financial report he needed to give our CFO for the upcoming year-end.  In previous years, NULL Man would require a dedicated week’s worth of work to compile this report and massage the data to get the “proper numbers”.  This seemed like an awful amount of time to compile a 12 page financial report,  so our Director asked if I could take a look at it.

Leaving the Director’s office, I decided I would just go over to NULL Man’s desk and let him know that I was required to get cross-trained on how to create this report.  Under this guise, my plan was to basically dissect what in-fact was going on and automate it as much as possible so I didn’t have to deal with this again in the future, as developing reports never really excited me.

Thankfully NULL Man had a “busy” month-end coming up and welcomed the opportunity to “finally unload some of this work to someone else”.  We started out going over the purpose of this report and eventually how it all worked behind the scenes.  It turns out that this report wasn’t 12 pages by chance.  It was “designed” that way; each page having it’s own dedicated query to retrieve, cleanse, and format the data with Excel basically just acting as a container app.  The queries I were given were actually all stored procedures (to help with performance, of course) and were on average about 500 – 800 lines of code.  At first glance, this report looked very complex until I began to dig in and see what was actually involved.

It didn’t take more than 5 minutes to determine the real reason for so many lines of code.  NULL Man in his infinite wisdom wasn’t really fond of set operations, or really any advantages a database gives you when storing data in it, and he was even more clueless when it came to Excel.  Sadly, the query for each page would look something like this:

-- Get Information for Page 1
SELECT Product, SubProduct, Cost, 0 AS NewCol01
INTO #tempTable1
FROM SourceTable
WHERE Product LIKE '%ProductLine1%'

-- Pull out information for Sub Product Line 1
UPDATE #tempTable1
SET NewCol01 = Cost
WHERE SubProduct LIKE '%SubProductLine1%'

-- Create a new Column
SELECT *, 0 AS NewCol02
INTO #tempTable2
FROM #tempTable1

-- Pull out information for Sub Product Line 2
UPDATE #tempTable2
SET NewCol02 = Cost
WHERE SubProduct LIKE '%SubProductLine2%'

...

-- Create a new Column
SELECT *, 0 AS NewColX
INTO #tempTableX
FROM #tempTableX-1

-- Pull out information for Sub Product Line X
UPDATE #tempTableX
SET NewColX = Cost
WHERE SubProduct LIKE '%SubProductLineX%'

-- Return Page 1 Information
SELECT Product, SubProduct, Cost,
    NewCol01 AS ProductLine1Cost,
    NewCol02 AS ProductLine2Cost,
    ...
    NewColX AS ProductLineXCost
FROM SourceTable
WHERE Product LIKE '%ProductLine1%'

UNION ALL

SELECT Product, SubProduct, SUM(Cost) AS TotalCost,
    SUM(NewCol01) AS TotalProductLine1Cost,
    SUM(NewCol02) AS ProductLine2Cost,
    ...
    SUM(NewColX) AS ProductLineXCost
FROM #tempTableX
GROUP BY Product, SubProduct

This is, of course, a generalized version of the SQL I had just been exposed to, but this was the basic methodology of every “page” in this report, and to my dismay just about every bit of SQL NULL Man ever created.  I asked him why he decided to take this approach, and to my utter bemusement, his response was, “It’s more efficient!”.  Ironic really, because any work being performed in the database at this point was a waste.

After verifying that every “page” of the report followed the pattern outlined above, I “optimized” the entire report into a simple query:

SELECT Product, SubProduct, Cost
FROM SourceTable

The bulk of the work was done in Excel with Computed Columns, Filters, and Grouping.  This whole process, from evaluation of the existing report to creation of the new one took a grand total of 4 hours, start to finish.  I sent the report onto our Director and the CFO and told them they could refresh the report at any time so they no longer needed to wait on us for up-to-date numbers.  NULL Man voiced his concern that the CFO might not fully understand how to use Excel, but eventually shrugged and said, “I told ‘em it’d be easier if we let Excel handle most of the work, but they didn’t listen.”  It was at this point I knew my future with this company would comprise of fixing and optimizing NULL Man’s work.