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

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.