Refactoring SQL Applications

by Faroult, Stephane

• ISBN13: 9780596514976
• ISBN10: 0596514972
• Format: Paperback
• Copyright: 2008-08-01
• Publisher: Oreilly & Associates Inc

Summary

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. 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.

Author Biography

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	p. vii
Assessment	p. 1
A Simple Example	p. 2
Assessing Possible Gains	p. 19
Sanity Checks	p. 37
Statistics and Data Skewness	p. 38
Indexing Review	p. 44
Parsing and Bind Variables	p. 55
Bulk Operations	p. 70
Transaction Management	p. 73
User Functions and Views	p. 75
User-Defined Functions	p. 76
Views	p. 103
Testing Framework	p. 115
Generating Test Data	p. 116
Comparing Alternative Versions	p. 132
Statement Refactoring	p. 147
Execution Plans and Optimizer Directives	p. 148
Analyzing a Slow Query	p. 152
Refactoring the Query Core	p. 158
Rebuilding the Initial Query	p. 176
Task Refactoring	p. 179
The SQL Mindset	p. 180
Restructuring the Code	p. 185
Refactoring Flows and Databases	p. 211
Reorganizing Processing	p. 212
Shaking Foundations	p. 233
How it Works: Refactoring in Practice	p. 243
Can You Look at the Database?	p. 243
Queries of Death	p. 245
All These Fast Queries	p. 247
No Obvious Very Wrong Query	p. 248
Time to Conclude	p. 249
Scripts and Sample Programs	p. 251
Tools	p. 261
Index	p. 269
Table of Contents provided by Ingram. All Rights Reserved.