Refactoring SQL applications
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 databa...
| Autor principal: | |
|---|---|
| Otros Autores: | |
| Formato: | Libro electrónico |
| Idioma: | Inglés |
| Publicado: |
Beijing ; Sebastopol, California :
O'Reilly
[2008]
|
| Edición: | First edition |
| Materias: | |
| Ver en Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009627392906719 |
Tabla de Contenidos:
- 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
