Sometime in 2004, I had to revise a well-established import process that was clogging up the production SQL server with gooey slowness. The size of the data we were loading had grown dramatically since the process was last modified, and the server was overburdened.
At the time, I really only knew SQL syntax and a handful of basic commands you might see in Access: SELECT, INSERT, DELETE, and maybe PRINT. Basically, I was no more educated in T-SQL than an Office power user. Armed with what little I knew, I had to make our server responsive while this process was running. I opened the script and began to search for something, anything, to tweak.
After a lot of staring and head-scratching, I found something. A command that would ease up on the gas.
I began throwing this command everywhere I could in the hopes it would throttle back the process. I ran the process again, and it worked beautifully. No longer was the server unresponsive while importing these large data sets. Outstanding.
What command did I use, you ask?
I found a single instance of WAITFOR, sitting innocently at the end of the loop. That’s right. In a loop. Assuming this to be a magical command, I proceeded to stripe the code with WAITFORs everywhere. Inside a loop, which was really a CURSOR. That sat inside a CURSOR. Which sat inside another CURSOR. (Are you vomiting yet? I’m nearly there myself.) Performance was abysmal — “We’ll let it run overnight, maybe it’ll be done by morning,” — but I had freed up the server.
I had traded a problem I couldn’t tolerate for one that I could.
At the time, I wasn’t really interested in learning T-SQL (or anything else about SQL Server for that matter). It’s therefore perfectly reasonable that I would be okay with exchanging one problem for another. Doing so required no development on my part. Solving the problem, however, would have meant learning about performance tuning and best practices.
I wouldn’t want to revisit that code; I know it’s awful (and hopefully no longer in use) and I wouldn’t get much satisfaction from rewriting it. I’m just happy knowing I don’t trade problems anymore. These days, I much prefer to solve them.