SQL Optimization

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 to be 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.

Martin Berg Consult can assists in both identifying problematic SQL’s and the following optimization of the SQL.

The optimization may include any index creation or rewrite of SQL.