Top-Down SQL Tuning Course Outline

 

Taught by Dan Tow, dantow@singingsql.com

 

·        Introduction

-          Why tune SQL?

-          Can you tune SQL?

·        Basics

-          Caching in a relational database

-          Data layouts in a relational database

-          Data access methods

-          Optimizing single-table queries

-          Join methods

-          Optimizing individual joins

·        Diagramming Simple SQL Queries

-          The virtue of a simple problem representation

-          The full join diagram

-          Exercises

-          The simplified join diagram

-          Exercises

-          Expectation of simple queries

·        Deducing the Best Execution Plan

-          Robustness in execution plans

-          The heuristic join-diagram method, simple cases

-          Exercises

-          The heuristic join-diagram method, complex cases

-          Exercises

·        Complex SQL Queries

-          Abnormal join diagrams

-          Extending the diagram to subqueries

-          Extending the diagram to views

-          Other complex queries

-          Exercise

·        Why the Heuristic Works

-          Why to use nested loops

-          Why to drive from the best filter

-          Choosing where to join next

-          Accounting for unequal per-row costs

-          Accounting for benefits of later joins

-          Joining upstream early

·        Special Cases

-          Outer joins in general

-          Indexing to combine filters and joins

-          Flawed indexes

-          Unfiltered joins

-          Outer joins to views

-          Unsolvable problems

·        Outside-the-Box Solutions to the Unsolvable

-          Queries returning too many details

-          Queries running too often

-          Queries having multiple filters with so-so selectivity spread across the join tree

-          Queries aggregating many details