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

Chapter 8. How It Works: Refactoring in Practice

Riguarda bene omai s ì com’io vado Per questo loco al vero che disiri, Sì che poi sappi sol tener o guado.
Observe me well, how through this place I come Unto the truth thou wishest, that hereafter Thou mayst alone know how to keep the ford.
—Dante Alighieri (1265-1321)
Paradiso, II, 124-126
(trans. Henry W. Longfellow)

IN THIS BOOK, I’VE TRIED TO EXPLAIN THE VARIOUS PATHS YOU CAN EXPLORE TO REFACTOR UNDERPERFORMING SQL applications. In this short chapter, I will briefly describe how I usually proceed when I am asked to look at a database application that doesn’t live up to expectations. I’ll try to be both concrete and abstract: concrete because I want to show refactoring in practice and abstract because I want to describe mental processes, not give a mock demonstration. Needless to say, you will find justifications and detailed explanations in the previous chapters, but not in this one.

Can You Look at the Database?

Very often, there is a vague rumor that something is wrong with an application, but you are asked to fix whatever you can without knowing exactly what the problem is.

If you are lucky, the most critical performance issues have already been narrowed to one process or task, and the behavior is the same on a test database as on the main production database. If you are very lucky, someone who knows the functional side of the application well has volunteered to help you.
In such a case, the best course to take is probably to turn ...