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

Content preview from Refactoring SQL Applications

About the Authors

Stephane Faroult first discovered relational databases and the SQL language back in 1983. He joined Oracle France in their early days (after a brief spell with IBM and a bout of teaching at the University of Ottawa) and soon developed an interest in performance and tuning topics. After leaving Oracle in 1988, he briefly tried to reform and did a bit of operational research, but after one year, he succumbed again to relational databases. He has been continuously performing database consultancy since then, and founded RoughSea Ltd in 1998.

Pascal L'Hermite has been working with relational databases in OLTP, production and development environments on Oracle Databases for the past 12 years and on Microsoft SQL Server for the past 5 years.