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

Overview

The Data Access Handbook

Achieving Optimal Database Application Performance and Scalability

John Goodson • Robert A. Steward

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

See more details below
Paperback (New Edition)
$38.23
BN.com price
(Save 23%)$49.99 List Price
Other sellers (Paperback)
  • All (11) from $27.49   
  • New (6) from $27.49   
  • Used (5) from $35.39   
The Data Access Handbook: Achieving Optimal Database Application Performance and Scalability

Available on NOOK devices and apps  
  • NOOK Devices
  • Samsung Galaxy Tab 4 NOOK
  • NOOK HD/HD+ Tablet
  • NOOK
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac

Want a NOOK? Explore Now

NOOK Book (eBook)
$22.99
BN.com price
(Save 42%)$39.99 List Price

Overview

The Data Access Handbook

Achieving Optimal Database Application Performance and Scalability

John Goodson • Robert A. Steward

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.

John Goodson is vice president and general manager of the DataDirect division of Progress Software, a leader in
data connectivity and mainframe integration. For 20 years, he has worked with Sun, Microsoft, and others to develop database connectivity standards such as J2EE, JDBC, ODBC, and ADO. He served on the ANSI H2 committee that built the SQL standard and now participates in the JDBC Expert Group and Java Rowsets standards committees.

Rob Steward, vice president of R&D at the DataDirect
division of Progress Software,
is responsible for the development, strategy, and oversight of the company’s data connectivity products. Rob has spent the past 15 years developing high-performing database driver and data providers, including ODBC, JDBC, and ADO.NET.

Both authors have spoken on database application performance at many industry events.

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.

Read More Show Less

Product Details

  • ISBN-13: 9780137143931
  • Publisher: Prentice Hall
  • Publication date: 3/30/2009
  • Edition description: New Edition
  • Pages: 360
  • Product dimensions: 6.90 (w) x 9.10 (h) x 1.00 (d)

Meet the Author

John Goodson: As the executive leader of DataDirect Technologies, John is responsible for daily operations, business development, product direction, and long-term corporate strategy.

John was a principal engineer at Data General for seven years, working on their relational database product, DG/SQL. Since joining DataDirect Technologies in 1992, he has held positions of increasing responsibility in research and development, technical support, and marketing. John is a well-known and respected industry luminary and data connectivity expert. For more than 15 years, he has worked closely with Sun Microsystems and Microsoft on the development and evolution of database connectivity standards including J2EE, JDBC, .NET, ODBC, and ADO. John has been involved with the ANSI NCITS H2 Committee, which is responsible for building the SQL standard, and the X/Open (Open Group) SQL Access Group, which is responsible for building call-level interfaces into relational databases. He is actively involved in Java standards committees, including the JDBC Expert Group. In addition, John has published numerous articles and spoken publicly on topics related to data management. John is also a patent holder in the area of distributed transactions for Microsoft SQL Server Java middleware.

John holds a Bachelor of Science in computer science from Virginia Polytechnic Institute and State University in Blacksburg,Virginia.

Rob Steward: As vice president of research and development for DataDirect Technologies, Rob is responsible for the development, strategy, and oversight of the company’s data connectivity products, including the Shadow mainframe integration suite client software and the industry-leading DataDirect Connect family of database drivers and data providers: Connect for ODBC, Connect for JDBC, and Connect for ADO.NET. Additional product development responsibilities include DataDirect Sequelink and DataDirect XQuery, as well as the management of DataDirect’s Custom Engineering Development group.

Rob has spent more than 15 years developing database access middleware, including .NET data providers, ODBC drivers, JDBC drivers, and OLE DB data providers. He has held a number of management and technical positions at DataDirect Technologies and serves on various standards committees. Earlier in his career, he worked as lead software engineer at Marconi Commerce Systems Inc.

Rob holds a Bachelor of Science in computer science from North Carolina State University, Raleigh.

Read More Show Less

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

Read More Show Less

Preface

PrefacePreface

The world is flat. For thousands of years, all the mathematicians, explorers, and philosophers of the world knew this to be true. In the sixth century, several Greek philosophers presented evidence that the world was round. Yet, the experts shunned their ideas for hundreds of years more.


All database application performance and scalability problems can be solved by tuning the database. Ask the database experts—they’ll tell you this. They’ll even convince you to spend thousands to millions of dollars a year to tune the database software to solve performance issues. When tuning doesn’t resolve the problem, they’ll tell you that the database software or hardware, or both, is simply at capacity. But, if only 5% to 25% of the time it takes to process a database request is actually spent in well-tuned database software, does it make sense that performance bottlenecks occur because of these “at capacity” systems? If a business analyst waits 10 seconds for a query to process and only half a second of this time is spent in the database, does it make sense to spend time and money figuring out how to improve that half second? Or, does it make more sense to try to figure out how to improve the other 9.5 seconds? Hundreds of books, consultants, and Web sites are dedicated to solving database tuning problems, but relatively no information is available on how to design data-centric applications, tune data access application code, select and tune database drivers, and understand and tune the flow of data to and from database applications and, eventually, the database. We wrote this book to provide information that willhelp you focus on reducing those 9.5 seconds of time and show that all database application performance and scalability problems cannot be solved by simply tuning the database.

The journey of writing this book started in different ways for John and Rob but ended up in the same place.

John Goodson: Several years ago, I was at an IBM conference making a presentation on how to improve the performance of JDBC applications. After my presentation, an IT director approached me and said, “Where do I get more information on this? This type of information is impossible to find.” I thought about it for a moment, and I told him there really was no one place you can obtain this type of information—it was stuck in the heads of a few people scattered throughout the world. This incident was followed by many others involving IT director after IT director telling me, “I never knew a database driver could make such a big difference” or, “I always thought that database application performance was a database problem.” Every technical paper we wrote on the subject was in great demand, and every presentation we gave on the subject was to a full audience. We wrote this book because it was time to dispel the myths about performance and scalability being exclusively a database problem. The guidelines, tips, and techniques for improving performance that are presented in this book should be available to everyone.

Rob Steward: Writing a book is something I thought I would never do. I once asked an author friend of mine who writes software books whether it was really worth it to write one. He told me emphatically that there is only one reason that I should ever write a book about software. He said “Only write a book if there is something you strongly feel compelled to say.” Having been in the database middleware business for 15 years now, I have seen a lot of badly written data access code, and consequently, a lot of applications that ran way too slowly and had to be fixed. I have literally spent years of my life helping people fix the problems they have created due to their lack of knowledge about what really happens on the client side when they make a call to the database. John and I have talked on the subject at many conferences and written a number of articles and white papers in an effort to help as many developers as possible understand the intricacies of data access code. When John approached me about coauthoring this book, I immediately agreed. I instantly felt compelled to share on a much broader scale that knowledge that we have been sharing in bits and pieces in various forums over the years. It’s my hope that every reader will find something in this book that makes the difference between “too slow” and “blazing fast” in all their future applications.

The authors hope that this book will be used by software architects, IT staff, DBAs, and developers in their daily work to predict, diagnose, and solve performance issues in their database applications. Tuning the database is essential to good performance and scalability. We know that to be true. However, in an environment with a well-tuned database system, most performance problems are caused by the following:


  • Poorly designed data access architecture

  • Poorly optimized data access source code

  • Inefficient or poorly tuned database drivers

  • Lack of understanding about the environment in which database applications are deployed

This book addresses all these issues—the world is round.

This book contains the following chapters:

Chapter 1, “Performance Isn’t What It Used to Be,” describes the evolution of database middleware and identifies where performance bottlenecks can appear.

Chapter 2, “Designing for Performance: What’s Your Strategy?,” provides guidelines for designing your database application and tuning the database middleware that connects your application to the database server for optimal performance.

Chapter 3, “Database Middleware: Why It’s Important,” explains what database middleware is, what it does, and how it affects performance. It also describes what you should look for in a database driver, one of the most important components of database middleware.

Chapter 4, “The Environment: Tuning for Performance,” describes the different environment layers that data requests and responses flow through, explains how they affect performance, and provides guidelines for making sure the environment does not become a performance bottleneck.

Chapter 5, “ODBC Applications: Writing Good Code,” describes some good coding practices that can provide optimal performance for your ODBC applications.

Chapter 6, “JDBC Applications: Writing Good Code,” describes some good coding practices that can provide optimal performance for your JDBC applications.

Chapter 7, “.NET Applications: Writing Good Code,” describes some good coding practices that can provide optimal performance for .NET applications.

Chapter 8, “Connection Pooling and Statement Pooling,” provides details about different connection pool models, describes how reauthentication works with connection pooling, and tells how using statement pooling with connection pooling might consume more memory on the database server than you realize.

Chapter 9, “Developing Good Benchmarks,” provides some basic guidelines for writing benchmarks that many developers don’t follow but absolutely should.

Chapter 10, “Troubleshooting Performance Issues,” walks you through how to troubleshoot performance issues and provides case studies to help you think through some varied performance issues and figure out how to resolve them.

Chapter 11, “Data Access in Service-Oriented Architecture (SOA) Environments,” provides some general guidelines to make sure that your database applications perform well in SOA environments.

The Glossary defines terms used in this book.


© Copyright Pearson Education. All rights reserved.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Posted April 14, 2009

    more from this reviewer

    optimising use of a database

    Many books about relational databases go into considerable details about optimising the use. Like the way you build the tables and how they are interrelated and joined. In contrast, Goodson and Steward describe a different type of optimisation. Here there are at least 2 computers. On 1 runs your application, while on the other is the database it uses. The application talks to a database driver on its machine, and the driver makes the network conversation with the database. The book describes ways to better configure the application's use of the driver.

    The key recommendations are thus:

    Reduce the number of network connections. Network delays can be considerable, especially if the machines are far apart.

    Reduce the amount of data returned over the network by the database. Discard fields [columns] in rows that are unlikely to be needed by the user; all the more so if the fields are large, like images. If the results are large, it takes time to convert these into network packets and then to reassemble the packets on the application machine. Here the penalty is in the time taken on the database server to find the results, the time to transmit these, and the time to reassemble them on your machine.

    There are more recommendations, but the 2 above seem the most important. The book goes into details about the others. It also has a useful chapter towards the end where several scenarios of actual non-optimal situations are given, along with the causes and resolutions. Worth careful reading for you to get inspiration and possibly clues about your situation.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)