Refactoring SQL Applications - Helion

ebook

Autor: Stephane Faroult, Pascal L'Hermite
ISBN: 978-05-965-5177-3
stron: 298, Format: ebook
Data wydania: 2008-08-22
Księgarnia: Helion

Cena książki: 126,65 zł (poprzednio: 147,27 zł)
Oszczędzasz: 14% (-20,62 zł)

Osoby, które kupiły tę książkę, wybierały także »

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.

Osoby które kupowały "Refactoring SQL Applications", wybierały także:

Windows Media Center. Domowe centrum rozrywki 66,67 zł, (8,00 zł -88%)
Ruby on Rails. Ćwiczenia 18,75 zł, (3,00 zł -84%)
DevOps w praktyce. Kurs video. Jenkins, Ansible, Terraform i Docker 190,00 zł, (39,90 zł -79%)
Przywództwo w świecie VUCA. Jak być skutecznym liderem w niepewnym środowisku 58,64 zł, (12,90 zł -78%)
Scrum. O zwinnym zarządzaniu projektami. Wydanie II rozszerzone 58,64 zł, (12,90 zł -78%)

Spis treści

Refactoring SQL Applications eBook -- spis treści

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