Refactoring SQL applications

283 Pages·2008·1.005 MB·English

by  Stéphane Faroult, Pascal L'Hermite

Refactoring
SQL Applications
Stéphane Faroult
with Pascal L’Hermite
Beijing • Cambridge • Farnham • Köln • Sebastopol • Taipei • Tokyo
Refactoring SQL Applications
by Stéphane Faroult with Pascal L’Hermite
Copyright © 2008 Stéphane Faroult and Pascal L’Hermite. All rights reserved. Printed in the
United States of America.
Published by O’Reilly Media, Inc. 1005 Gravenstein Highway North, Sebastopol, CA 95472
O’Reilly books may be purchased for educational, business, or sales promotional use. Online
editions are also available for most titles (safari.oreilly.com). For more information, contact our
corporate/institutional sales department: [phone] or [email protected].
Editor: Mary Treseler Cover Designer: Mark Paglietti
Production Editor: Rachel Monaghan Interior Designer: Marcia Friedman
Copyeditor: Audrey Doyle Illustrator: Robert Romano
Indexer: Lucie Haskins
Printing History:
August 2008: First Edition.
The O’Reilly logo is a registered trademark of O’ReillyMedia, Inc.Refactoring SQLApplications and
related trade dress are trademarks of O’Reilly Media, Inc. Many of the designations used by
manufacturers and sellers to distinguish their products are claimed as trademarks. Where those
designations appear in this book, and O’Reilly Media, Inc. was aware of a trademark claim, the
designations have been printed in caps or initial caps.
Java™ is a trademark of Sun Microsystems, Inc.
While every precaution has been taken in the preparation of this book, the publisher and authors
assume no responsibility for errors or omissions, or for damages resulting from the use of the
information contained herein.
This book uses RepKover™, a durable and flexible lay-flat binding.
ISBN: 978-0-596-51497-6
[M]
CO NTENTS
PREFACE vii
1 ASSESSMENT 1
A Simple Example 2
Assessing Possible Gains 19
2 SANITY CHECKS 37
Statistics and Data Skewness 38
Indexing Review 44
Parsing and Bind Variables 55
Bulk Operations 70
Transaction Management 73
3 USER FUNCTIONS AND VIEWS 75
User-Defined Functions 76
Views 103
4 TESTING FRAMEWORK 115
Generating Test Data 116
Comparing Alternative Versions 132
5 STATEMENT REFACTORING 147
Execution Plans and Optimizer Directives 148
Analyzing a Slow Query 152
Refactoring the Query Core 158
Rebuilding the Initial Query 176
6 TASK REFACTORING 179
The SQL Mindset 180
Restructuring the Code 185
7 REFACTORING FLOWS AND DATABASES 211
Reorganizing Processing 212
Shaking Foundations 233
v
8 HOW IT WORKS: REFACTORING IN PRACTICE 243
Can You Look at the Database? 243
Queries of Death 245
All These Fast Queries 247
No Obvious Very Wrong Query 248
Time to Conclude 249
A SCRIPTS AND SAMPLE PROGRAMS 251
B TOOLS 261
INDEX 269
vi CONTENTS
Chapter
Preface
Ma, sendo l’intento mio scrivere cosa utile a chi la intende, mi è parso più
conveniente andare drieto alla verità effettuale della cosa, che alla
immaginazione di essa.
But, it being my intention to write a thing which shall be useful to him who
apprehendsit,itappearstomemoreappropriatetofollowuptherealtruth
of a matter than the imagination of it.
—Niccolò Machiavelli
Il Principe, XV
T
HEREISASTORYBEHINDTHISBOOK.IHADHARDLYFINISHEDTHEARTOFSQL,WHICHWASN’TON
saleyet,whenmytheneditor,JonathanGennick,raisedtheideaofwritingabookabout
SQL refactoring. SQL, I knew. But I had never heard about refactoring. I Googled the
word.InafamousplaybyMolière,awealthybutlittle-educatedmanwhotakeslessonsin
his mature years marvels when he discovers that he has been speaking “prose” for all his
life. Like Monsieur Jourdain, I discovered that I had been refactoring SQL code for years
withoutevenknowingit—performanceanalysisformycustomersledquitenaturallyto
improvingcodethroughsmall,incrementalchangesthatdidn’talterprogrambehavior.
Itisonethingtotrytodesignadatabaseasbestasyoucan,andtolayoutanarchitecture
andprogramsthataccessthisdatabaseefficiently.Itisanothermattertotrytogetthebest
performance from systems that were not necessarily well designed from the start, or
whichhavegrownoutofcontrolovertheyearsbutthatyouhavetolivewith.Andthere
was something appealing in the idea of presenting SQL from a point of view that is so
often mine in my professional life.
The last thing you want to do when you are done with a book is to start writing another
one. But the idea had caught my fancy. I discussed it with a number of friends, one of
whomisoneofthemostredoubtableSQLspecialistsIknow.Thisfriendburstintorighteous
vii
indignation against buzzwords. For once, I begged to differ with him. It is true that the
idea first popularized by Martin Fowler* of improving code by small, almost insignificant,
localized changes may look like a fad—the stuff that fills reports by corporate consultants
who have just graduated from university. But for me, the true significance of refactoring
liesinthefactthatcodethathasmadeittoproductionisnolongerconsideredsacred,and
intherecognitionthatalotofmediocresystemscould,withalittleeffort,domuchbetter.
Refactoringisalsotheacknowledgmentthatthefaultforunsatisfactoryperformanceisin
ourselves, not in our stars—and this is quite a revelation in the corporate world.
IhaveseentoomanysiteswhereITmanagershadanalmosttragicattitudetowardperfor-
mance, people who felt crushed by fate and were putting their last hope into “tuning.” If
theeffortsofdatabaseandsystemadministratorsfailed,theonlyremainingoptionintheir
view was to sign and send the purchase order for more powerful machines. I have read
too many audit reports by self-styled database experts who, after reformatting the output
of system utilities, concluded that a few parameters should be bumped up and that more
memoryshouldbeadded.Tobefair,someofthesereportsmentionedthatacoupleofter-
riblequeries“shouldbetuned,”withoutbeingmuchmoreexplicitthanpastingexecution
plans as appendixes.
Ihaven’ttoucheddatabaseparametersforyears(thetechnicalteamsofmycustomersare
usually competent). But I have improved many programs, fearlessly digging into them,
andIhavetriedasmuchasIcouldtoworkwithdevelopers,ratherthanstayinmyivory
tower and prescribe from far above. I have mostly met people who were eager to learn and
understand, who needed little encouragement when put on the right tracks, who enjoyed
developing their SQL skills, and who soon began to set performance targets for themselves.
When the passing of time wiped from my memory the pains of book writing, I took the
plungeandbegantowriteagain,withtheintenttoexpandtheideasIusuallytrytotrans-
mit when I work with developers. Database accesses are probably one of the areas where
thereisthemosttogainbyimprovingthecode.Mypurposeinwritingthisbookhasbeen
togivenotrecipes,butaframeworktotrytoimprovetheless-than-idealSQLapplications
thatsurrounduswithoutrewritingthemfromscratch(inspiteofaverystrongtemptation
sometimes).
Why Refactor?
Most applications bump, sooner or later, into performance issues. In the best of cases, the
success of some old and venerable application has led it to handle, over time, volumes of
data for which it had never been designed, and the old programs need to be given a new
lease on life until a replacement application is rolled out in production. In the worst of
cases, performance tests conducted before switching to production may reveal a dismal
failure to meet service-level requirements. Somewhere in between, data volume
* Fowler,M.etal.Refactoring:ImprovingtheDesignofExistingCode.Boston:Addison-WesleyProfessional.
viii PREFACE
increases, new functionalities, software upgrades, or configuration changes sometimes
revealflawsthathadsofarremainedhidden,andbacktrackingisn’talwaysanoption.All
ofthosecasesshareextremelytightdeadlinestoimproveperformance,andhighpressure
levels.
The first rescue expedition is usually mounted by system engineers and database adminis-
tratorswhoareaskedtoperformthemagicalparameterdance.Unlesssomeverybigmistake
has been overlooked (it happens), database and system tuning often improves performance
only marginally.
Atthispoint,thetraditionalnextstephaslongbeentothrowmorehardwareattheappli-
cation. This is a very costly option, because the price of hardware will probably be com-
pounded by the higher cost of software licenses. It will interrupt business operations. It
requires planning. Worryingly, there is no real guarantee of return on investment. More
than one massive hardware upgrade has failed to live up to expectations. It may seem
counterintuitive, but there are horror stories of massive hardware upgrades that actually
ledtoperformancedegradation.Therearecaseswhenaddingmoreprocessorstoamachine
simply increased contention among competing processes.
The concept ofrefactoring introduces a much-needed intermediate stage between tuning
and massive hardware injection. Martin Fowler’s seminal book on the topic focuses on
objecttechnologies.Butthecontextofdatabasesissignificantlydifferentfromthecontext
of application programs written in an object or procedural language, and the differences
bring some particular twists to refactoring efforts. For instance:
Small changes are not always what they appear to be
DuetothedeclarativenatureofSQL,asmallchangetothecodeoftenbringsamassive
upheaval in what the SQL engine executes, which leads to massive performance
changes—for better or for worse.
Testing the validity of a change may be difficult
If it is reasonably easy to check that a value returned by a function is the same in all
cases before and after a code change, it is a different matter to check that the contents
of a large table are still the same after a majorupdate statement rewrite.
The context is often critical
Database applications may work satisfactorily for years before problems emerge; it’s
often when volumes or loads cross some thresholds, or when a software upgrade
changes the behavior of the optimizer, that performance suddenly becomes unaccept-
able.Performanceimprovementworkondatabaseapplicationsusuallytakesplaceina
crisis.
Databaseapplicationsarethereforeadifficultgroundforrefactoring,butatthesametime
the endeavor can also be, and often is, highly rewarding.
PREFACE ix
Refactoring Database Accesses
Databasespecialistshavelongknownthatthemosteffectivewaytoimproveperformance
is, once indexing has been checked, to review and tweak the database access patterns. In
spite of the ostensibly declarative nature of SQL, this language is infamous for the some-
times amazing difference in execution time between alternative writings of functionally
identical statements.
There is, however, more to database access refactoring than the unitary rewriting of prob-
lem queries, which is where most people stop. For instance, the slow but continuous
enrichment of the SQL language over the years sometimes enables developers to write
efficientstatementsthatreplaceinasinglestrokewhatcouldformerlybeperformedonly
by a complex procedure with multiple statements. New mechanisms built into the data-
base engine may allow you to do things differently and more efficiently than in the past.
Reviewing old programs in the light of new features can often lead to substantial perfor-
mance improvements.
It would really be a brave new world if the only reason behind refactoring was the desire
to rejuvenate old applications by taking advantage of new features. A sound approach to
database applications can also work wonders on what I’ll tactfully call less-than-optimal
code.
Changing part of the logic of an application may seem contradictory to the stated goal of
keeping changes small. In fact, your understanding of whatsmall andincremental mean
depends a lot on your mileage; when you go to an unknown place for the very first time,
theroadalwaysseemsmuchlongerthanwhenyoureturntothisplace,nowfamiliar,for
the umpteenth time.
What Can We Expect from Refactoring?
Itisimportanttounderstandthattwofactorsbroadlycontrolthepossiblebenefitsofrefac-
toring (this being the real world, they are conflicting factors):
• First, the benefits of refactoring are directly linked to the original application: if the
qualityofthecodeispoor,therearegreatoddsthatspectacularimprovementiswithin
reach. If the code were optimal, there would be—barring the introduction of new fea-
tures—no opportunity for refactoring, and that would be the end of the story. It’s
exactlylikewithcompanies:onlythebadlymanagedonescanbespectacularlyturned
around.
• Second, when the database design is really bad, refactoring cannot do much. Making
things slightly less bad has never led to satisfactory results. Refactoring is an evolution-
ary process. In the particular case of databases, if there is no trace of initial intelligent
design, even an intelligent evolution will not manage to make the application fit for
survival. It will collapse and become extinct.
x PREFACE
It is unlikely that the great Latin poet, Horace, had refactoring in mind when he wrote
aboutaurea mediocritas, the golden mediocrity, but it truly is mediocre applications for
whichwecanhavethebesthopes.Theyareinamplesupply,becausemuchtoooften“the
first way that everyone agrees will functionally work becomes the design,” as wrote a
reviewer for this book, Roy Owens.
How This Book Is Organized
Thisbooktriestotakearealisticandhonestviewoftheimprovementofapplicationswith
a strong SQL component, and to define a rational framework for tactical maneuvers. The
exerciseofrefactoringisoftenperformedasafranticquestforquickwinsandspectacular
improvements that will prevent budget cuts and keep heads firmly attached to shoulders.
It’s precisely in times of general panic that keeping a cool head and taking a methodical
approachmattermost.Let’sstateupfrontthatmiracles,bydefinition,arethepreserveofa
few very gifted individuals, and they usually apply to worthier causes than your applica-
tion(whateveryoumaythinkofit).Butthereasonedandsystematicapplicationofsound
principlesmayneverthelesshaveimpressiveresults.Thisbooktriestohelpyoudefinedif-
ferenttactics,aswellasassessthefeasibilityofdifferentsolutionsandtherisksattachedto
different interpretations of the wordincremental.
Very often, refactoring an SQL application follows the reverse order of development: you
start with easy things and slowly walk back, cutting deeper and deeper, until you reach
the point where it hurts or you have attained a self-imposed limit. I have tried to follow
the same order in this book, which is organized as follows:
Chapter1,Assessment
Canbeconsideredastheprologueandisconcernedwithassessingthesituation.Refac-
toring is usually associated with times when resources are scarce and need to be allo-
cated carefully. There is no margin for error or for improving the wrong target. This
chapterwillguideyouintryingtoassessfirstwhetherthereisanyhopeinrefactoring,
and second what kind of hope you can reasonably have.
The next two chapters deal with the dream of every manager: quick wins. I discuss in
these chapters the changes that take place primarily on the database side, as opposed to
the application program. Sometimes you can even apply some of those changes to
“canned applications” for which you don’t have access to the code.
Chapter2,Sanity Checks
Deals with points that must be controlled by priority—in particular, indexing review.
Chapter3,User Functions and Views
Explains how user-written functions and an exuberant use of views can sometimes
bring an application to its knees, and how you can try to minimize their impact on
performance.
In the next three chapters, I deal with changes that you can make to the application proper.
PREFACE xi
Chapter4,Testing Framework
Shows how to set up a proper testing framework. When modifying code it is critical to
ensure that we still get the same results, as any modification—however small—can
introducebugs;thereisnosuchthingasatotallyrisk-freechange.I’lldiscusstacticsfor
comparing before and after versions of a program.
Chapter5,Statement Refactoring
DiscussesindepththeproperapproachtowritingdifferentSQLstatements.Optimizers
rewrite suboptimal statements. That is, this is what they are supposed to do. But the
cleverest optimizer can only try to make the best out of an existing situation. I’ll show
you how to analyze and rewrite SQL statements so as to turn the optimizer into your
friend, not your foe.
Chapter6,Task Refactoring
Goes further in Chapter 5’s discussion, explaining how changing the operational
mode—and in particular, getting rid of row-based processing—can take us to the next
level. Most often, rewriting individual statements results in only a small fraction of
potentialimprovements.Boldermoves,suchascoalescingseveralstatementsorreplac-
ing iterative, procedural statements with sweeping SQL statements, often lead to awe-
inspiring gains. These gains demand good SQL skills, and an SQL mindset that is very
differentfromboththetraditionalproceduralmindsetandtheobject-orientedmindset.
I’ll go through a number of examples.
If you are still unsatisfied with performance at this stage, your last hope is in the next
chapter.
Chapter7,Refactoring Flows and Databases
Returns to the database and discusses changes that are more fundamental. First I’ll dis-
cuss how you can improve performance by altering flows and introducing parallelism,
and I’ll show the new issues—such as data consistency, contention, and locking—that
you have to take into account when parallelizing processes. Then I’ll discuss changes
that you sometimes can bring, physically and logically, to the database structure as a
last resort, to try to gain extra performance points.
And to conclude the book:
Chapter8,How It Works: Refactoring in Practice
Providesakindofsummaryofthewholebookasanextendedchecklist.Inthischapter
Idescribe,withreferencestopreviouschapters,whatgoesthroughmymindandwhat
I do whenever I have to deal with the performance issues of a database application.
This was a difficult exercise for me, because sometimes experience (and gut instinct
acquired through that experience) suggests shortcuts that are not really the conscious
product of a clear, logical analysis. But I hope it will serve as a useful reference.
AppendixA,Scripts and Sample Programs, andAppendixB,Tools
Describe scripts, sample programs, and tools that are available for download from
O’Reilly’s website for this book,http://www.oreilly.com/catalog/9780596514976.
xii PREFACE