Eugene Elutin (8/15/2012)
First thing I would do is de-noodling the code so it can be readable:a.) Format with proper indenting of code between BEGIN and END
b.) Remove the hoard of commented out code - if you want see the history - use source control system... Don't leave the rubbish in the code, or it very soon will contain more commented out lines than executable lines.
This is some of the best advice I've ever seen posted on this forum. It is what I do every time I'm called upon to improve a query's performance. Figuring out what it is doing is half the battle, and is greatly assisted by these steps. If only people posting questions would take the time to do it before they posted, they'd probably find the answer before their posting (no offense meant to the OP when I say this).
You should see some of the junk I've had to process. Mostly stuff created by query builder or some reporting tool. The queries are totally impossible to read, let alone understand, without some pre-formatting.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St