Refactoring SQL Applications

by Stephane Faroult, Pascal L'Hermite

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.

Product Details

ISBN-13:	9780596551773
Publisher:	O'Reilly Media, Incorporated
Publication date:	08/22/2008
Sold by:	Barnes & Noble
Format:	eBook
Pages:	298
File size:	3 MB

About the Author

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.

Table of Contents

Preface     vii
Assessment     1
A Simple Example     2
Assessing Possible Gains     19
Sanity Checks     37
Statistics and Data Skewness     38
Indexing Review     44
Parsing and Bind Variables     55
Bulk Operations     70
Transaction Management     73
User Functions and Views     75
User-Defined Functions     76
Views     103
Testing Framework     115
Generating Test Data     116
Comparing Alternative Versions     132
Statement Refactoring     147
Execution Plans and Optimizer Directives     148
Analyzing a Slow Query     152
Refactoring the Query Core     158
Rebuilding the Initial Query     176
Task Refactoring     179
The SQL Mindset     180
Restructuring the Code     185
Refactoring Flows and Databases     211
Reorganizing Processing     212
Shaking Foundations     233
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
Scripts and Sample Programs     251
Tools     261
Index     269