"Could you take a look at ticket 56789?" I nodded my head in the affirmative. I was already behind in my existing tasks so I was little annoyed.
I opened up the ticket in the system and read the following: "Customer says that procedure FooBar consistently executes in 5 seconds. Customer wants to know if there is any way we could make that a little bit faster. Customer feels this is not a priority but since they use the function multiple times a day they would save some time."
Our system has almost 4000 stored procedures and I didn't immediately recognize the FooBar procedure as something I've worked on. I went into our source control system and found FooBar. The last check-in was seven years ago and, surprisingly, it was by me. Even more surprisingly, the last check-in note stated:"Per Schlemiel, if you ever have to improve performance remove line 262."
What? I was baffled. Why would I write a check-in comment like that? After pondering the issue for a few seconds, I remembered! I shook my head, chuckled, remove line 162, checked-in my code, and went to find Schlemiel.
Flashback seven years ago...
I was still a new employee and was trying to make a name for myself. Our [[benevolent dictator|benevolent dictator]] at the time was Schlemiel. He has since been promoted to a bigger team with more responsibility. He was a well respected, godlike architect and I admired him very much. Schlemiel always saw something in me and I was his to-go guy when he had data issues.
Schlemiel asked me if I could look at the FooBar procedure because it was running a little slow. I asked him for the ticket number.
"12345. Just do this as a favor for me. A couple years back Schlomo wrote this FooBar procedure as a skunkworks project. We never thought customers would really use it so we never bothered to ensure it performed over larger data sets. Now it takes about 14 minutes to execute for most customers. Customers are starting to complain and Schlomo can't figure out what to fix. Take a look, make some recommendations, and we'll figure out how to prioritize."
I pulled up the ticketing system and opened up 12345. Sure enough: "Customer states the FooBar procedure is totally unusable. It takes about 14 minutes to execute. User state she clicks submit and then goes for coffee and bagel. Sometimes it's done by the time she gets back. Please fix it."
I pulled up the code for the FooBar procedure and began looking at it. Within 20 minutes I figured it out. One of the queries joined 12 different tables and one of the JOIN/ON conditions was missing. Effectively there was a cartesian product causing billions and billions of rows to qualify for the final result set. Thankfully the WHERE clause was filtering out those rows before they were sent to the client. Through dumb luck to query was working, but it was not efficient.
I added the JOIN/ON condition and began writing some quick unit tests. My new version proved to be functionally equivalent but took orders of magnitude fewer reads and executed in 500 milliseconds vs about 9 minutes on my test server. I immediately scurried off to tell Schlemiel.
Schlemiel was less-than-enthusiastic. "Now you've gone and gotten yourself a bit of a problem."
Puzzled, I replied, "Why? The tests are perfect, clearly this is a slam dunk. Simple oversight by Schlomo, if you will."
"I've no doubt you solved the problem, but the fact is, you made the performance TOO good. If you release this version of your procedure then management and the customer will never believe that such a dramatic increase in performance doesn't introduce too much risk. The change will never be approved. No one will ever believe that simply adding an ON clause could fix an issue like this. Instead, management and the customer will demand to see endless proofs and reams of unit tests before they have a comfort level. Meanwhile, you are creating agita for yourself and I frankly need you to work on other, more important issues."
As every word passed his lips I felt more and more deflated. "What do you suggest?"
"I would suggest adding a WAITFOR DELAY '00:00:05' somewhere in your code. Let's see that code." He peered over my shoulder, "Right here on Line 262 looks good to me. Once you've done that go ahead and check it in and note that I code reviewed and approved your work. And next time, try not to be so efficient."
The look of dejection on my face must have been overwhelming. Perhaps he noticed a stray tear running down my face. He tried to console me, "Dave, don't worry so much. You did a great job. You took a 14 minute procedure and go it down to 5 seconds. You're a hero. But just remember, today's hero can be tomorrow's goat. Someday you'll do something stupid and you won't be hero. At that moment just remember that you can always go back and remove that artificial WAITFOR DELAY and you'll be the hero again. Remember, you're only as good as your last success."
Flashforward to the present...
"Hey Schlemiel," I found him in the hallway in another building, "remember seven years ago when I tweaked FooBar and it performed so well that you wanted me to put a WAITFOR DELAY in the code? Well, we just got another ticket by a customer asking us to tweak FooBar again. I took out the WAITFOR DELAY and checked the code back in."
I could feel my chest was puffed out to the fullest, I was proud of my accomplishment. Schlemiel paused a second and then broke out in a terrific belly laugh. "You idiot! I can't believe you actually checked in that WAITFOR DELAY. I was kidding of course. No sane person would do something like that. Didn't you realize I was kidding?"
I walked away...again...dejected. I had two major successes, both times the customer was thrilled, and both times I was the goat.
sql server data architecture other