Optimizing a SQL can require the use of several diciplines.
Formally it can be stated that optimizing a SQL in a structured manner must be done by first identifying the most significant time consumption internally in the SQL and then by corresponding corrective actions reduce the identified time consumption – no other actions will make the SQL run (significantly) faster.
Summarized there can be two causes for a SQL being too slow: Sub-optimal data structures and/or sub-optimal execution.
Sub-optimal data structures will cause excessive amounts of data being read from storage (the database) and sub-optimal execution will manifest itself by the SQL statement internally processing data, that is not part of the output.
Experience shows that sub-optimal execution very often is the cause of poor performance.
Sub-optimal execution can be analyzed in a structured manner by analyzing for “throw-away”.
This method can provide a quantification of the internal, but wasted, work in the SQL and thus provide a foundation for describing corrective actions.
The method can also provide an estimate of the potential improvement.