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