Stephen Faroult on Tuning SQL
One of the main things I do to tune SQL is to encourage the optimizer to take path.
I often take the most important steps to do first and put them in a sub select. I’ve been meaning to write something up about this for years. Unfortunately all the great examples have been lost at customer sites happily left behind after my work was done. Only later did I kick myself for not having saved more. I have not taken the time or energy to come up with some good examples. Well, all is not lost because here is another great video from Stephen Faroult on this exact subject:
The ideas he lays out
1. take the highest seletivity steps and do them first (this is where I often use “inline views”)
2. sort the least amount of data , ie sort the key column and not all the data columns
3. Join in non-key columns as late as possible
To summarise, I look for the central data (what Stephen calls the “key”) and put this in an inline view , then sort, then finally join in any missing columns