Writing

Feed Software, technology, sysadmin war stories, and more.

Friday, January 27, 2012

SQL weenies and gaming quarterly reviews

I started figuring out that I was working in the wrong place when some of the people responsible for dashboards started telling me about what they did. At the time, my duties included managing the infrastructure used to actually run a bunch of tests, and the "dashboard people" were supposed to pull out that data and render it in meaningful ways.

For a little while, I treated them as a black box. This turned out to be a very bad idea. The more I learned about their frontends, the more I realized I was in hell.

My first encounter with one of these people was a chance event when we somehow managed to start talking about what he was working on. Apparently he was going to build something which gave you all results since the beginning of time. At least, that's what I thought it was based on his description of the SQL queries involved.

He was doing a SELECT which brought back every single row in that particular results table. I figured it was a necessary evil to build some kind of "everything we've ever run" view. Nope. Instead, he was just rendering the past two weeks and was throwing out everything else. I pointed out the insanity of this and he said "oh, but it's not that much data so it works fine".

Well sure, right then, it worked great, I'm sure. I had just built a separate server for our team, so yes, we only had a few weeks worth of data on board. Of course it would seem fast... NOW. I knew that eventually it would start bogging down and would need to be gutted and written properly.

His response was approximately "I'm really busy and that's a Q2 or Q3 thing". This was around the end of December. This guy was talking about stuff 6 to 9 months out! This kind of task should have taken under a week to figure out, write, test, and roll out. Instead, this guy was working on a glacial time scale.

Another time, after a reorg which moved all of our desks around, I was in the same room as two of these people on a daily basis. I had to hear them go back and forth and back and forth about the same banal SQL query stupidity. I tried to drown it out with my headphones, but enough of it slipped through to let me figure out what was going on.

They were designing a binary search with SQL WHERE statements.

Yeah. Okay. They had a mandate which basically said "give us all of the data for a given date and render it". They knew that all of the rows in that results table had a "row_id" column and that it went up as time went by. This is how they wrote their code, and no I am not making this up.

  1. Get size of table (to get the number of rows), call it "max row id"
  2. Figure how many days in the past we're looking - 1-7
  3. Multiply that number by a magic number (average rows per day up to that point, no kidding)
  4. Back up in the table by that much (max row id - that number)
  5. Pull out a row with that row_id, see if it's the right date, if so, back up until it becomes the day before, then stop.
  6. Otherwise go forward until it lands on the right date, then stop.
  7. Oh, and deal with gaps in the row_ids, since that happens too.

I couldn't take it any more. I said something about writing a WHERE clause involving the date column. They looked at me blankly, said nothing for a few moments, then basically ignored me and went back to jabbering aloud to each other about the same thing as if I wasn't there.

I raised my voice and jumped in again. They weren't going to drop this turd into a code base that I had to work on or run it on a server that I had to maintain. This was cosmically stupid and I would not stand for it.

The story ends up going off the rails here. They started making up excuses and other brainless arguments. I headed that one off at the pass by jumping onto the box and doing an "ALTER TABLE" to add an index on the date column. Then I told them it was there and to shut up, stop trying to be clever, and just use the WHERE on that column.

This accomplished little. They managed to convince the boss to let them do their little braindead approach "just for now", which of course stuck around indefinitely. The only thing which came out of it was a growing sense of resentment against me.

Months later, one of them snapped at me when he missed a call on his desk phone because its ringer had been lowered to zero. He just assumed I had done it. I managed to stay calm despite the fact that this huge man was angry at me and was situated between me and the only door to our office. Then I just said that no, I had not. He shut up and turned back to his desk.

He later apologized for it, but it had revealed plenty about his thought processes. I didn't need that crap around me.

Thinking about it now, I suspect some of these code time bombs were written on purpose. They'd work well for a while, then they'd start festering and would eventually explode. This would automatically create a relatively high priority issue for them to tackle. When the first guy said something about Q2 or Q3, maybe that's what he meant. Maybe it was set to "go off" about that far out?

I mean, look at it from the perspective of a company with quarterly objectives for each employee. You're supposed to identify things to work on, then work on them, then you get a grade. If you can rig the things which will appear there a few quarters out, then you can also rig the results! By planting the problem, you also get to deliver the glorious victory later on. That leads to kudos, bonuses, and promotions.

All it takes to do this is to not have a conscience. Just throw ethical behavior out the window and suddenly you can really go places!