I would think that one of the most common questions that people ask on SQLServerCentral is how to optimize some query. Regularly people are looking for help with queries, and often trying to make them run a little faster. We have some great experts that help out here, like Jeff Moden, Lynn Pettis, Jack Corbett, GSquared, Lowell, and many, many more. Apologies for those people I've missed, but off the top of my head, I often find these individuals posting code for someone else to try.

As people try to get better performance, occassionally I'll see posts that say the order of the tables matter, or I'll see someone suggesting that a rewrite fo the query in a better "order" will get better performance. It's a myth and legend that has been around for a long time, and this past week I was glad to see a blog from Conor Cuningham that addressed this. The short answer is that it doesn't matter, but I'd encourage you to read the blog.

Conor doesn't spend a lot of time addressing this, but he does say that if you rewrite the query, changing orders around, and get better performance, there's a word for that situation:

Luck.

You got lucky, and made a mistake in assuming that the coincidence of you rewriting the query actually affected the performance of the query. There likely isn't any causality. That's not completely true as Conor does mention that there are a few places where the optimizer is missing a type of plan and didn't convert the original and rewritten queries into equivalent execution trees. I suppose this means that there are times you stumble onto a better rewrite, but I bet more often than not it's just luck you got a better plan.

I see people doing this in all areas of technology, somehow making a chance and coincidentally seeing a change in the performance of a system. Often the coincidence comes when someone else is making changes without your knowledge and without a good central logging system. A little communication and documentation would go a long way toward preventing these issues.

But it does also mean that as you make changes, as you rewrite code or implement things in a new way, you need to carefully measure and test the results. Ensure that your efforts are repeatable, and not just "lucky."