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

Appendix A. Scripts and Sample Programs

THE BODY OF THIS BOOK CONTAINS MANY CODE SAMPLES AND SNIPPETS. I TOOK QUITE A NUMBER OF them (and slightly adapted them for confidentiality reasons) from code I have audited and modified; because these samples cannot run without a full array of tables and indexes filled with classified data, providing them makes little sense.

This book also includes many other programs and examples that I wrote specifically for it (which I sometimes merely allude to), as well as utility scripts that query the data dictionary and are of general use. These programs and scripts are available for download. You will find them on the O’Reilly website for this book, http://www.oreilly.com/catalog/9780596514976.

This appendix describes what you will find for download and provides a number of additional comments, which I hope you’ll find interesting.

Because most people are interested in one particular DBMS, I have regrouped the code samples based on DBMS, with one archive file per DBMS. Within each archive file, I organized the code samples by chapter (one directory per chapter).

Note that some of the programs I wrote specifically for one DBMS and did not port them to the others (e.g., this is the case in all the Chapter 7 examples that were written for MySQL exclusively). In several cases, you can adapt them to other products with some minor changes. Therefore, don’t hesitate to look at the code for the other products if you are interested in a program that isn’t provided ...