Cover image for Refactoring SQL Applications

Book Description

What can you do when database performance doesn't meetexpectations? Before you turn to expensive hardware upgrades tosolve the problem, reach for this book. Refactoring SQLApplications provides a set of tested options for making codemodifications to dramatically improve the way your databaseapplications function. Backed by real-world examples, you'll findquick fixes for simple problems, in-depth answers for more complexsituations, and complete solutions for applications with extensiveproblems. Learn to:

  • Determine if and where you can expect performance gains

  • Apply quick fixes, such as limiting calls to the database instored functions and procedures

  • Refactor tasks, such as replacing application code by a storedprocedure, or replacing iterative, procedural statements withsweeping SQL statements

  • Refactor flow by increasing parallelism and switchingbusiness-inducted processing from synchronous to asynchronous

  • Refactor design using schema extensions, regular views,materialized views, partitioning, and more

  • Compare before and after versions of a program to ensure youget the same results once you make modifications

  • Refactoring SQL Applications teaches you to recognizeand assess code that needs refactoring, and to understand thecrucial link between refactoring and performance. If and when yourapplication bogs down, this book will help you get it back up tospeed.

    Table of Contents

    1. Special Upgrade Offer
    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. Special Upgrade Offer
    17. Copyright