book

Refactoring SQL Applications

by Stephane Faroult, Pascal L'Hermite

August 2008

Intermediate to advanced

298 pages

9h 22m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Why Refactor?Refactoring Database AccessesWhat Can We Expect from Refactoring?How This Book Is OrganizedAudienceAssumptions This Book MakesConventions Used in This BookUsing Code ExamplesComments and QuestionsSafari® Books OnlineAcknowledgments
A Simple ExampleSQL Tuning, the Traditional WayCode DustingSQL Tuning, RevisitedRefactoring, First StandpointRefactoring, Second StandpointComparison and CommentsChoosing Among Various ApproachesAssessing Possible GainsFinding Out What the Database Is DoingQuerying dynamic viewsDumping statements to a trace fileExploiting trace filesAnalyzing Collected Material
Statistics and Data SkewnessAvailable StatisticsOptimizer TrapsExtreme valuesTemporary tablesIndexing ReviewA Quick Look at Schema IndexingA Detailed InvestigationIndexes That Blur the RulesBitmap indexesClustered indexesIndexes on expressionsParsing and Bind VariablesHow to Detect Parsing IssuesEstimating Performance Loss Due to ParsingCorrecting Parsing IssuesCorrecting Parsing Issues the Lazy WayCorrecting Parsing Issues the Proper WayHandling Lists in Prepared StatementsPassing the list as a single variableBatching listsUsing a temporary tableBulk OperationsTransaction Management
User-Defined FunctionsImproving Computation-Only FunctionsImproving Functions FurtherImproving Lookup FunctionsExample 1: A calendar functionExample 2: A conversion functionImproving Functions Versus Rewriting StatementsViewsWhat Views Are ForPerformance Comparison with and Without a Complex ViewRefactoring Views
Generating Test DataMultiplying RowsUsing Random FunctionsMatching Existing DistributionsGenerating Many RowsDealing with Referential IntegrityGenerating Random TextComparing Alternative VersionsUnit TestingComparing CrudelyComparing Tables and ResultsWhat to compareBrute force comparisonSQL comparison, textbook versionSQL comparison, better versionComparing checksums in SQLLimits of Comparison
Execution Plans and Optimizer DirectivesAnalyzing a Slow QueryIdentifying the Query CoreCleaning Up the from ClauseRefactoring the Query CoreUnitary AnalysisEliminating Repeated PatternsPlaying with SubqueriesSubqueries in the select listSubqueries in the from clauseSubqueries in the where clauseActivating Filters EarlySimplifying ConditionsOther OptimizationsSimplifying aggregatesUsing withCombining set operatorsRebuilding the Initial QueryNested LoopsMerge/Hash Joins
The SQL MindsetUsing SQL Where SQL Works BetterAssuming SuccessRestructuring the CodeCombining StatementsPushing Control Structures into SQLUsing aggregatesUsing coalesce() instead of if … is nullUsing exceptionsFetching all you need at onceShifting the logicGetting Rid of count()Avoiding ExcessesGetting Rid of LoopsReasons behind loopsAnalysis of loopsChallenging loops
Reorganizing ProcessingCompeting for ResourcesService time and arrival rateIncreasing parallelismMultiplying service providers at the application levelShortening critical sectionsIsolating Hot SpotsDealing with multiple queuesParallelizing Your Program and the DBMSShaking FoundationsMarshaling RowsSplitting TablesAltering ColumnsChanging the contentsSplitting columnsAdding columnsMaterializing views

Can You Look at the Database?Queries of DeathAll These Fast QueriesNo Obvious Very Wrong QueryTime to Conclude
Chapter 1Chapter 2Chapter 3Chapter 4Chapter 5Chapter 6Chapter 7 (MySQL)
mklipsum and lipsumHow to Build mklipsum and lipsumHow to Use mklipsum and lipsumRoughbenchHow to Build RoughbenchHow to Use RoughbenchThe roughbench.properties fileSpecifying optionsGenerating variablesGenerating integer or float valuesGenerating datesOutput

Overview

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.