SQL Optimization Techniques

  • Optimize access structures:
    • Database design and normalization.
    • Tables: heap or index-organized tables, and table or indexed clusters.
    • Indexes.
    • Constraints.
    • Materialized views.
    • Partitioning schemes.
    • Statistics, including a comprehensive refresh strategy.
  • Rewrite SQL statements:
    • Exclude projections that are not required.
    • Minimize the amount of work done more than once.
    • Factor subqueries that are used multiple times in the same statement.
    • Use EXISTS instead of IN because the former stops processing once it has found a match.
    • Use CASE and/or DECODE to avoid having to scan the same rows over and over again, especially for aggregation functions that act on different subsets of the same data.
    • Use analytic functions to do multiple or moving/rolling aggregations with a single pass through the data.
    • Avoid scalar subqueries in the SELECT-list.
    • Use joins instead of subqueries, as it gives the optimizer more room to play around in.
    • Say what you mean and pick the right join: if you only need an inner join don’t write an outer join.
    • Add logically superfluous predicates that may still aid in the search for an optimal execution plan, particularly for outer joins.
    • Avoid implicit conversions of data types, especially in the WHERE clause.
    • Write WHERE clause predicates with a close eye on the indexes available, including the leading edge of a composite index.
    • Avoid, whenever possible, comparison operators such as <>, NOT IN, NOT EXISTS, and LIKEwithout a leading '%' for indexed columns in predicates.
    • Do not apply functions on indexed columns in the WHERE clause when there is no corresponding function-based index.
    • Don’t abuse HAVING to filter rows before aggregating.
    • Avoid unnecessary sorts, including when UNION ALL rather than UNION is applicable.
    • Avoid DISTINCT unless you have to use it.
    • Use PL/SQL, especially packages with stored procedures (and bind variables) and shared cursors to provide a clean interface through which all data requests are handled.
    • Add hints once you have determined that it is right and necessary to do so.

About Hiren Kubavat

This entry was posted in MysQl and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s