Cover image for Refactoring SQL Applications

Book description

What can you do when database performance doesn't meet expectations? Before you turn to expensive hardware upgrades to solve the problem, reach for this book. Refactoring SQL Applications provides a set of tested options for making code modifications to dramatically improve the way your database applications function. Backed by real-world examples, you'll find quick fixes for simple problems, in-depth answers for more complex situations, and complete solutions for applications with extensive problems. Learn to:

  • Determine if and where you can expect performance gains

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

  • Refactor tasks, such as replacing application code by a stored procedure, or replacing iterative, procedural statements with sweeping SQL statements

  • Refactor flow by increasing parallelism and switching business-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 you get the same results once you make modifications

Refactoring SQL Applications teaches you to recognize and assess code that needs refactoring, and to understand the crucial link between refactoring and performance. If and when your application bogs down, this book will help you get it back up to speed.

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