Product Cover Image

Data Access Handbook, The: Achieving Optimal Database Application Performance and Scalability

By John Goodson, Robert A. Steward

Published by Prentice Hall

Published Date: Mar 16, 2009

Description

Drive breakthrough database application performance by optimizing middleware and connectivity

 

Performance and scalability are more critical than ever in today’s enterprise database applications, and traditional database tuning isn’t nearly enough to solve the performance problems you are likely to see in those applications. Nowadays, 75-95% of the time it takes to process a data request is typically spent in the database middleware. Today’s worst performance and scalability problems are generally caused by issues with networking, database drivers, the broader software/hardware environment, and inefficient coding of data requests. In The Data Access Handbook, two of the world’s leading experts on database access systematically address these issues, showing how to achieve remarkable improvements in performance of real-world database applications.

 

Drawing on their unsurpassed experience with every leading database system and database connectivity API, John Goodson and Rob Steward reveal the powerful ways middleware affects application performance and guide developers with designing and writing API code that will deliver superior performance in each leading environment. In addition to covering essential concepts and techniques that apply across database systems and APIs, they present many API examples for ODBC, JDBC, and ADO.NET as well as database system examples for DB2, Microsoft SQL Server, MySQL, Oracle, and Sybase.

Coverage includes

  • Clearly understanding how each component of database middleware can impact performance and scalability
  • Writing database applications to reduce network traffic, limit disk I/O, optimize application-to-driver interaction, and simplify queries–including examples for ODBC, JDBC, and ADO.NET
  • Managing connections, transactions, and SQL statement execution more efficiently
  • Making the most of connection and statement pooling
  • Writing good benchmarks to predict your application’s performance
  • Systematically resolving performance problems–including eight start-to-finish case-study examples

If you’re a software architect, system designer, or database application developer, The Data Access Handbook will be your most indispensable database application performance resource. It’s the one book that focuses on the areas where you can achieve the greatest improvements–whether you’re designing new database applications or troubleshooting existing ones.

 

Visit www.dataaccesshandbook.com to get the code examples presented in this book and other supplemental information for DB2, MicrosoftSQL Server, MySQL, Oracle, and Sybase.

Table of Contents

Preface

CHAPTER 1 Performance Isn’t What It Used To Be

Where Are We Today?

    The Network

    The Database Driver

    The Environment

    Your Database Application

Our Goal for This Book

CHAPTER 2 Designing for Performance: What’s Your Strategy?

Your Applications

    Database Connections

    Transaction Management

    SQL Statements

    Data Retrieval

    Extended Security

Static SQL Versus Dynamic SQL

The Network

The Database Driver

Know Your Database System

Using Object-Relational Mapping Tools

Summary

CHAPTER 3 Database Middleware: Why It’s Important

What Is Database Middleware?

How Database Middleware Affects Application Performance

Database Drivers

    What Does a Database Driver Do?

    Database Driver Architecture

    Runtime Performance Tuning Options

    Configuring Database Drivers/Data Providers

Summary

CHAPTER 4 The Environment: Tuning for Performance

Runtime Environment (Java and .NET)

    JVM

    .NET CLR

Operating System

Network

    Database Protocol Packets

    Network Packets

    Configuring Packet Size

    Analyzing the Network Path

    Reducing Network Hops and Contention

    Avoiding Network Packet Fragmentation

    Increasing Network Bandwidth

Hardware

    Memory

    Disk

    CPU (Processor)

    Network Adapter

    Virtualization

Summary

CHAPTER 5 ODBC Applications: Writing Good Code

Managing Connections

    Connecting Efficiently

    Using Connection Pooling

    Establishing Connections One at a Time

    Using One Connection for Multiple Statements

    Obtaining Database and Driver Information Efficiently

Managing Transactions

    Managing Commits in Transactions

    Choosing the Right Transaction Model

Executing SQL Statements

    Using Stored Procedures

    Using Statements Versus Prepared Statements

    Using Arrays of Parameters

    Using the Cursor Library

Retrieving Data

    Retrieving Long Data

    Limiting the Amount of Data Retrieved

    Using Bound Columns

    Using SQLExtendedFetch Instead of SQLFetch

    Determining the Number of Rows in a Result Set

    Choosing the Right Data Type

Updating Data

    Using SQLSpecialColumns to Optimize Updates and Deletes

Using Catalog Functions

    Minimizing the Use of Catalog Functions

    Avoiding Search Patterns

    Using a Dummy Query to Determine Table Characteristics

Summary

CHAPTER 6 JDBC Applications: Writing Good Code

Managing Connections

    Connecting Efficiently

    Using Connection Pooling

    Establishing Connections One at a Time

    Using One Connection for Multiple Statements

    Disconnecting Efficiently

    Obtaining Database and Driver Information Efficiently

Managing Transactions

    Managing Commits in Transactions

    Choosing the Right Transaction Model

Executing SQL Statements

    Using Stored Procedures

    Using Statements Versus Prepared Statements

    Using Batches Versus Prepared Statements

    Using getXXX Methods to Fetch Data from a Result Set

    Retrieving Auto-Generated Keys

Retrieving Data

    Retrieving Long Data

    Limiting the Amount of Data Retrieved

    Determining the Number of Rows in a Result Set

    Choosing the Right Data Type

    Choosing the Right Cursor

Updating Data

    Using Positioned Updates, Inserts, and Deletes (updateXXX Methods)

    Using getBestRowIdentifier() to Optimize Updates and Deletes

Using Database Metadata Methods

    Minimizing the Use of Database Metadata Methods

    Avoiding Search Patterns

    Using a Dummy Query to Determine Table Characteristics

Summary

CHAPTER 7 .NET Applications: Writing Good Code

Managing Connections

    Connecting Efficiently

    Using Connection Pooling

    Establishing Connections One at a Time

    Disconnecting Efficiently

    Obtaining Database and Data Provider Information Efficiently

Managing Transactions

    Managing Commits in Transactions

    Choosing the Right Transaction Model

Executing SQL Statements

    Executing SQL Statements that Retrieve Little or No Data

    Using the Command.Prepare Method

    Using Arrays of Parameters/Batches Versus Prepared Statements

    Using Bulk Load

    Using Pure Managed Providers

Selecting .NET Objects and Methods

    Avoiding the CommandBuilder Object

    Choosing Between a DataReader and DataSet Object

    Using GetXXX Methods to Fetch Data from a DataReader

Retrieving Data

    Retrieving Long Data

    Limiting the Amount of Data Retrieved

    Choosing the Right Data Type

Updating Data

Summary

CHAPTER 8 Connection Pooling and Statement Pooling

Connection Pool Model for JDBC

    Configuring Connection Pools

    Guidelines

Connection Pool Model for ODBC

    Connection Pooling as Defined in the ODBC Specification

    Configuring Connection Pools

    Guidelines

Connection Pool Model for ADO.NET

    Configuring Connection Pools

    Guidelines

Using Reauthentication with Connection Pooling

    Configuring Connection Pooling with Reauthentication in a JDBC Environment

Using Statement Pooling

    Using Statement Pooling with Connection Pooling

    Guidelines

Summary: The Big Picture

CHAPTER 9 Developing Good Benchmarks

Developing the Benchmark

    Define Benchmark Goals

    Reproduce the Production Environment

    Isolate the Test Environment

    Reproduce the Workload

    Measure the Right Tasks

    Measure over a Sufficient Duration of Time

    Prepare the Database

    Make Changes One at a Time

    Assess Other Factors

Benchmark Example

Summary

CHAPTER 10 Troubleshooting Performance Issues

Where to Start

Changes in Your Database Application Deployment

The Database Application

The Database Driver

    Runtime Performance Tuning Options

    Architecture

The Environment

    Runtime Environment (Java and .NET)

    Operating System

    Network

    Hardware

Case Studies

    Case Study 1

    Case Study 2

    Case Study 3

    Case Study 4

    Case Study 5

    Case Study 6

    Case Study 7

    Case Study 8

Summary

CHAPTER 11 Data Access in Service-Oriented Architecture (SOA) Environments

What Is Service-Oriented Architecture (SOA)?

Data Access Guidelines for SOA Environments

    Involve Data Experts in Addition to SOA Experts

    Decouple Data Access from Business Logic

    Design and Tune for Performance

    Consider Data Integration

Summary

Glossary

 

 

0137143931    TOC    2/19/2009

 

Purchase Info

ISBN-10: 0-13-265046-0

ISBN-13: 978-0-13-265046-5

Format: eBook (Watermarked)?

This eBook includes the following formats, accessible from your Account page after purchase:

ePubEPUBThe open industry format known for its reflowable content and usability on supported mobile devices.

MOBIMOBIThe eBook format compatible with the Amazon Kindle and Amazon Kindle applications.

Adobe ReaderPDFThe popular standard, used most often with the free Adobe® Reader® software.

This eBook requires no passwords or activation to read. We customize your eBook by discretely watermarking it with your name, making it uniquely yours.

Includes EPUB, MOBI, and PDF

$39.99 $31.99

Add to Cart