Visual SQL tuning diagrams.
Why tune SQL queries? SQL queries are the primary mechanism for database
interaction. SQL query performance is essential for database and
application performance.
Why can SQL queries be sub-optimal? Impacted by poor SQL coding choices.
Change with evolving applications. Affected by changes in database
structure. Suffer from resource contention.
How to improve SQL query performance? Understand how databases store data.
Recognize what type of data SQL queries need to access. Locate data quickly
by using table indexes correctly. Use more efficient SQL coding constructs.
Five steps for efficient SQL tuning: (1) Identify problematic SQL query.
(2) Parse problematic SQL query. (3) Analyze relevant database objects. (4)
Display results in relationship diagram. (5) Determine possibilities for
optimization.
Components of SQL query analysis: SQL query text. SQL tuning diagram. Index
analysis. Table statistics. Column statistics and histograms. Plan guides.
View basic information: Tables and views, table joins, subqueries, relevant
columns, relevant keys, and relevant indexes.
View detailed information: Number of table rows. Number of table rows that
meet selection criteria. Table join sizes (number of rows that meet
selection criteria for both tables). All columns, keys, and indexes. SQL
text for objects and table joins. Objects within views. Nested subqueries.
View cardinality: One-to-one, one-to-many, many-to-one, and many-to-many.
Cardinality: Number of rows in one table that match rows in another table.
Used to detect and resolve loops.
View additional relationships: Cartesian join, implied Cartesian join,
indirect relationship, outer join, unique relationship, NOT EXISTS or NOT
IN join, and EXISTS or IN join.
Benefits of SQL tuning diagrams: View SQL query components. Understand
efficiency of SQL query paths. Find flaws in database schema designs.
Discover missing indexes. Accelerate SQL query troubleshooting.
