What was your coolest SQL optimization, on a slow performing query?

Question

Just speaking to a colleague of mine. He was walking with a hop in his step, on the way to the coffee machine.

I asked him "what's with the 'swarmy' walk?", he said, "I just reduced a two hour long query down to 40 seconds! It feels so good".

He altered a stored procedure, that was using cursors and introduced a temp table, that was refactored from the original dataset - I will email him soon, to get more info on actual implemetation.

But ultimately, he was buzzing.

Question is, what SQL, that sticks in your mind and has made you buzz, whilst optimising slow performing queries?

Answer 1

I have to say when I learned how to create and use covered indexes. Now, THAT was a performance booster.

Answer 2

Using SQL's BULKIMPORT to reduce several hours of inherited INSERT code to less than a minute.