Big wins at work

posted by Jeff | Tuesday, February 17, 2009, 7:14 PM | comments: 1

The single hardest thing for me to deal with at work is that, doing client work, we often have to deal with someone else's crap. In fact, I'd go as far as to say that it frightens me to see just how bad the code is being produced by most everyone. There is a lot of really bad stuff out there, and it's no wonder that software projects fail so often.

Generally speaking, it isn't my job to get into the weeds with code, though I have on totally new stuff, kind of to establish standards in design and coding. But that's usually the extent of it. However, I've had to dive in and take a closer look at one system in particular, where something we wrote has to interface with someone else's system.

While not what I would describe as being a high transaction system, the performance of it has been getting worse and worse, and we were blaming the other third-party, and they were blaming us. Some clues led me to believe that a huge part of the problem was the database itself, as built by the other party, so today I just logged into the production server and watched it.

I'm far from a SQL guru, but I understand how to troubleshoot performance issues, how best to index tables, etc. Again, I'm no DBA and not a SQL programmer. That said, as soon as I saw the trace, it was immediately obvious that the other party didn't index anything in their database, so every query, some running up to three times per second, was doing full table scans. I was in awe. No wonder the CPU's were maxed out most of the time.

So I did some indexing, and at worst, it may spike to 30-ish% on the CPU, with some slight disk thrashing, but in between, there's almost no activity. A batch process that was taking upward of 12 hours last night, ran in three minutes this evening.

I look like a big hero, and I'm willing to take that credit, but there are a lot of other things that still need to be improved. Indexing tables is hardly the work of a genius, and I can't believe it wasn't going on in the first place. I'm stunned.

What appeared obvious to me resulted in a 30-point font reply of "thank you" over and over from the client, which is pretty cool. I needed that, because my morale was not good.


Comments

Neuski, February 18, 2009, 2:05 AM #

I can't imagine working with a database without indexing because I never have! You don't get a lot of silver bullets like that when debugging. I don't blame you for savoring it.


Post your comment: