Refactoring SQL applications / Stéphane Faroult with Pascal L'Hermite.

Format:

Book

Author/Creator:

Faroult, Stéphane.

Contributor:

L'Hermite, Pascal.

Language:

English

Subjects (All):

Database management.

SQL (Computer program language).

Physical Description:

1 online resource (304 p.)

Edition:

First edition.

Place of Publication:

Beijing ; Sebastopol, California : O'Reilly, [2008]

Language Note:

English

System Details:

text file

Summary:

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 perform

Contents:

Refactoring SQL Applications; Refactoring Database Accesses; 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; Code Dusting; SQL Tuning, Revisited; Refactoring, First Standpoint; Refactoring, Second Standpoint; Comparison and Comments; Choosing Among Various Approaches; Assessing Possible Gains; Dumping statements to a trace file; Exploiting trace files; Analyzing Collected Material; 2. Sanity Checks; Optimizer Traps; Temporary tables

Indexing ReviewA Detailed Investigation; Indexes That Blur the Rules; Clustered indexes; Indexes on expressions; Parsing and Bind Variables; 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; Batching lists; Using a temporary table; Bulk Operations; Transaction Management; 3. User Functions and Views; Improving Functions Further; Improving Lookup Functions; Example 2: A conversion function; Improving Functions Versus Rewriting Statements; Views

Performance Comparison with and Without a Complex ViewRefactoring Views; 4. Testing Framework; Using Random Functions; Matching Existing Distributions; Generating Many Rows; Dealing with Referential Integrity; Generating Random Text; Comparing Alternative Versions; Comparing Crudely; Comparing Tables and Results; Brute force comparison; SQL comparison, textbook version; SQL comparison, better version; Comparing checksums in SQL; Limits of Comparison; 5. Statement Refactoring; Analyzing a Slow Query; Cleaning Up the from Clause; Refactoring the Query Core; Eliminating Repeated Patterns

Playing with SubqueriesSubqueries in the from clause; Subqueries in the where clause; Activating Filters Early; Simplifying Conditions; Other Optimizations; Using with; Combining set operators; Rebuilding the Initial Query; Merge/Hash Joins; 6. Task Refactoring; Assuming Success; Restructuring the Code; Pushing Control Structures into SQL; 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; Analysis of loops; Challenging loops; 7. Refactoring Flows and Databases

Increasing parallelismMultiplying service providers at the application level; Shortening critical sections; Isolating Hot Spots; Parallelizing Your Program and the DBMS; Shaking Foundations; Splitting Tables; Altering Columns; Splitting columns; Adding columns; Materializing views; 8. How It Works: Refactoring in Practice; Queries of Death; All These Fast Queries; No Obvious Very Wrong Query; Time to Conclude; A. Scripts and Sample Programs; Chapter 2; Chapter 3; Chapter 4; Chapter 5; Chapter 6; Chapter 7 (MySQL); B. Tools; How to Use mklipsum and lipsum; Roughbench; How to Use Roughbench

Specifying options

Notes:

Includes index.

Description based on print version record.

ISBN:

9780596154950

059615495X

9780596551773

0596551770

OCLC:

609840914