T-SQL Tuesday #021 – Inelegant Yet Educational

August’s T-SQL Tuesday is brought to us by Adam Machanic (Blog|Twitter). Actually, we have Adam to thank for every T-SQL Tuesday because he created it in the first place, however this month he’s hosting it as well. He’s also decided to have the event take place on a Wednesday rather than Tuesday, which is appropriate given this month’s topic, which is to reveal our “crap code.”

This seems easy enough because I’ve got plenty of examples. The first one that came to mind was when I was told to replace all cursors with temp tables and WHILE loops. However, I already discussed this in item #4 of last month’s post. No need to revisit that. So here’s two other crap code examples; one that died quickly and another that lives on.

Quickly Archived But Not Forgotten

I was directed to implement a soft-coded values solution that shoehorned generic objects into a relational model, with GUIDs as clustered keys and values in sql_variant columns. By the way, the data architect in this situation admitted not knowing anything about 1) the solution’s industry and 2) SQL Server.

I implemented the design. Now, you might ask, why write this code even though I knew it would be crap? Well, because I had already been labeled a naysayer by the new management team for raising objections on two other issues. I had also been shown negative emails that managers were circulating about me. So I decided to be a team player while preparing to get a pink slip, and along the way I’d learn about this type of design.

Guess what? I did learn a lot about soft-coded values design (e.g. when it’s appropriate, when it’s not, how far you can optimize it, and non-relational approaches). But in the end, a daily load of just one feed took 22 hours (and almost 100 feeds needed to be processed a day). Each consultant brought in to review my code said it was perfect. According to them, the solution was that we needed to buy much, much more expensive hardware to fix the situation.

New hardware was not possible, and the situation was desperate enough to ask this “naysayer” for ideas. I demonstrated a redesigned solution that could process a feed in several minutes. With that, the soft-coded values solution was replaced and sent off to the archive repository. Years later, my solution is still used to process large volumes of data. Even though the soft-coded values solution was only used for several months many years ago, I continue to be asked questions related to that experience.

Lesson: Data matters. Relying on technology and/or technical skills to allow abstraction or over-generalization of your data might not be a viable solution. I was able to design a superior solution in this situation because of my experience with data in this particular industry. This is why I like Buck Woody’s (Blog|Twitter) job title of Data Professional – it keeps me focused on what I consider to be the most important part of my work.

It Lives On

It was over eight years ago when I was asked for this particular hack. A new solution had been developed without considering that certain identifier values from the old solution needed to be maintained.

The manager of development asked if I could hack something together quickly to maintain these identifiers. It went something like “You can? Two days? Great! And, oh yeah, also create new identifiers that won’t collide with the old solution. This will be a temporary hack, don’t worry. In a couple of months a permanent solution will be built. Two, three months. Okay, maybe four or five since some people might go on vacation. That’s it. Probably. Well, maybe six months. Tops.

So I created my hack solution. Driven by nothing but the finest .bat scripts. Plus several tables, stored procedures and user-defined functions. Yes, I usually avoid user-defined functions, but this was just a temporary hack that I needed to throw together quickly, right? Since this was a temporary solution, I decided to stick these objects in their own database that could just be dropped when the hack was no longer needed.

I asked the DBA for a database, which he named DB_CorpCo (replace “CorpCo” with client name). I said this was a terrible name because it’s not doing something necessarily related to a specific client, rather it’s performing a certain function. The DBA said for now only one client, CorpCo, will use it, so that’s a fine name. I said that sometimes temporary hacks become permanent, so this database might end up being used for other clients such as AirCo, FruitCo, StoreCo, etc. The name will be confusing. It should not contain a client name. However, my argument was going nowhere, and I had a lot of hacking to do, so DB_CorpCo was created. Over eight years ago.

DB_CorpCo worked great. Six months came and went. Then most of the development team was laid off. DB_CorpCo was working fine and there were no resources to replace it, so it kept going. Pretty soon, it’s use was expanded to include other clients than just CorpCo. I got to explain over and over that, yes, it’s called DB_CorpCo, but all clients use it.

I left the company for a while, and when I returned I found that even with all the comments and documentation warning that no new functionality should be added to DB_CorpCo, it’s tables had been altered and stored procedures had been modified to add new, expanded duties that belonged elsewhere. Like a beast in a horror film, DB_CorpCo, with all it’s temporary, hacked together abominations, was now intertwined with new and old solutions serving multiple clients. And at the top of each source code file is the comment “Original version created by Noel McKinney.”

To this day, in a data center far, far away, on a lonely server in a rack, a database named “DB_CorpCo” is being updated and queried daily. It Lives (cue the horror music now).

Lesson: Temporary hacks can become permanent, unless you want to argue that eight years is not “permanent” when it comes to computer solutions.

I’m looking forward to reading the contributions to this month’s T-SQL Tuesday Wednesday and reading the examples that others were generous enough to share. Thanks to Adam Machanic for creating this monthly blog event and hosting it this month!

About Noel McKinney

Noel McKinney is the administrator of noelmckinney.com
This entry was posted in T-SQL Tuesday. Bookmark the permalink.