Refactoring SQL Applications

Table of contents

1. Refactoring SQL Applications
2. A Note Regarding Supplemental Files
3. Preface
   1. Why Refactor?
   2. Refactoring Database Accesses
      1. What Can We Expect from Refactoring?
   3. How This Book Is Organized
   4. Audience
   5. Assumptions This Book Makes
   6. Conventions Used in This Book
   7. Using Code Examples
   8. Comments and Questions
   9. Safari® Books Online
   10. Acknowledgments
4. 1. Assessment
   1. A Simple Example
      1. SQL Tuning, the Traditional Way
      2. Code Dusting
      3. SQL Tuning, Revisited
      4. Refactoring, First Standpoint
      5. Refactoring, Second Standpoint
      6. Comparison and Comments
      7. Choosing Among Various Approaches
   2. Assessing Possible Gains
      1. Finding Out What the Database Is Doing
         1. Querying dynamic views
         2. Dumping statements to a trace file
         3. Exploiting trace files
      2. Analyzing Collected Material
5. 2. Sanity Checks
   1. Statistics and Data Skewness
      1. Available Statistics
      2. Optimizer Traps
         1. Extreme values
         2. Temporary tables
   2. Indexing Review
      1. A Quick Look at Schema Indexing
      2. A Detailed Investigation
      3. Indexes That Blur the Rules
         1. Bitmap indexes
         2. Clustered indexes
         3. Indexes on expressions
   3. Parsing and Bind Variables
      1. How to Detect Parsing Issues
      2. Estimating Performance Loss Due to Parsing
      3. Correcting Parsing Issues
      4. Correcting Parsing Issues the Lazy Way
      5. Correcting Parsing Issues the Proper Way
      6. Handling Lists in Prepared Statements
         1. Passing the list as a single variable
         2. Batching lists
         3. Using a temporary table
   4. Bulk Operations
   5. Transaction Management
6. 3. User Functions and Views
   1. User-Defined Functions
      1. Improving Computation-Only Functions
      2. Improving Functions Further
      3. Improving Lookup Functions
         1. Example 1: A calendar function
         2. Example 2: A conversion function
      4. Improving Functions Versus Rewriting Statements
   2. Views
      1. What Views Are For
      2. Performance Comparison with and Without a Complex View
      3. Refactoring Views
7. 4. Testing Framework
   1. Generating Test Data
      1. Multiplying Rows
      2. Using Random Functions
      3. Matching Existing Distributions
      4. Generating Many Rows
      5. Dealing with Referential Integrity
      6. Generating Random Text
   2. Comparing Alternative Versions
      1. Unit Testing
      2. Comparing Crudely
      3. Comparing Tables and Results
         1. What to compare
         2. Brute force comparison
         3. SQL comparison, textbook version
         4. SQL comparison, better version
         5. Comparing checksums in SQL
      4. Limits of Comparison
8. 5. Statement Refactoring
   1. Execution Plans and Optimizer Directives
   2. Analyzing a Slow Query
      1. Identifying the Query Core
      2. Cleaning Up the from Clause
   3. Refactoring the Query Core
      1. Unitary Analysis
      2. Eliminating Repeated Patterns
      3. Playing with Subqueries
         1. Subqueries in the select list
         2. Subqueries in the from clause
         3. Subqueries in the where clause
      4. Activating Filters Early
      5. Simplifying Conditions
      6. Other Optimizations
         1. Simplifying aggregates
         2. Using with
         3. Combining set operators
   4. Rebuilding the Initial Query
      1. Nested Loops
      2. Merge/Hash Joins
9. 6. Task Refactoring
   1. The SQL Mindset
      1. Using SQL Where SQL Works Better
      2. Assuming Success
   2. Restructuring the Code
      1. Combining Statements
      2. Pushing Control Structures into SQL
         1. Using aggregates
         2. Using coalesce() instead of if … is null
         3. Using exceptions
         4. Fetching all you need at once
         5. Shifting the logic
      3. Getting Rid of count()
      4. Avoiding Excesses
      5. Getting Rid of Loops
         1. Reasons behind loops
         2. Analysis of loops
         3. Challenging loops
10. 7. Refactoring Flows and Databases
    1. Reorganizing Processing
       1. Competing for Resources
          1. Service time and arrival rate
          2. Increasing parallelism
          3. Multiplying service providers at the application level
          4. Shortening critical sections
       2. Isolating Hot Spots
          1. Dealing with multiple queues
       3. Parallelizing Your Program and the DBMS
    2. Shaking Foundations
       1. Marshaling Rows
       2. Splitting Tables
       3. Altering Columns
          1. Changing the contents
          2. Splitting columns
          3. Adding columns
          4. Materializing views
11. 8. How It Works: Refactoring in Practice
    1. Can You Look at the Database?
    2. Queries of Death
    3. All These Fast Queries
    4. No Obvious Very Wrong Query
    5. Time to Conclude
12. A. Scripts and Sample Programs
    1. Chapter 1
    2. Chapter 2
    3. Chapter 3
    4. Chapter 4
    5. Chapter 5
    6. Chapter 6
    7. Chapter 7 (MySQL)
13. B. Tools
    1. mklipsum and lipsum
       1. How to Build mklipsum and lipsum
       2. How to Use mklipsum and lipsum
    2. Roughbench
       1. How to Build Roughbench
       2. How to Use Roughbench
          1. The roughbench.properties file
          2. Specifying options
          3. Generating variables
          4. Generating integer or float values
          5. Generating dates
       3. Output
14. Index
15. About the Authors
16. Copyright