Excerpt

Chapter 1: “Performance Isn’t What It Used to Be”

Odds are very good that one or more of your company’s database applications is suffering from performance issues. This should hardly come as a surprise to those of you who deal with application design, development, or deployment. What may be surprising to you is that the root cause of many of these issues is in the database middleware—the software that connects applications to databases. Before we get into why database middleware is so important to the optimal performance of your database application, let’s look at how the performance landscape has changed over the last several years.

Ten to 15 years ago, if you were having performance issues with your database application, 95% of the time the issues were in your database management software. Back then, tuning the database was considered magic to everyone except for a select group of engineers and database experts, who most likely worked for the database vendors. And, they were keeping details about their databases a secret; it was proprietary.

The performance landscape started to change when these experts began writing books about database tuning and giving seminars for the public. Today, because of the volumes of database tuning information, years of experience, and vastly improved database monitoring tools, the task of tuning databases has become less painful.

Other changes that affected database performance were taking place during these years: hardware costs decreased and computing power increased. Along with faster and less expensive hardware, came the move from monolithic to networked environments and client/server computing (two- and three-tier environments). Today, most database applications communicate with databases over a network instead of communicating directly through interprocess communication on a single computer as shown in Figure 1-1.

With the shift to networked environments, software was needed to provide connectivity between the application and database that now were located on different computers. The database vendors were the first to provide this software as proprietary database middleware components for their databases. This addition of database middleware added a new factor to the performance puzzle.

Database middleware consists of all components that handle the application’s data request until that request is handed off to the database management software, as shown in Figure 1-2. 

With the introduction of the networked environment, the database middleware layer included components such as:

• The network
• Database client software such as Oracle SQL*Net
• Other libraries loaded into the application’s address space when it connected to a database such as SSL libraries for extended security

Soon, the industry began to see a need for database connectivity standards, which would provide a common application programming interface (API) to access multiple databases. Without a common API, the industry was faced with multiple database connectivity solutions; each database vendor provided their own proprietary API. For example, to access Microsoft SQL Server, Oracle, and IBM DB2, a developer would have to know three very different database APIs: Microsoft Database Library (DBLIB), Oracle Call Interface (OCI), and IBM Client Application Enabler (CAE), as shown Figure 1-3. The advent of database connectivity standards solved this problem.

With the introduction of database connectivity standards, database drivers were added to the database middleware layer. Among many other things, a database driver processes the standards-based API function calls, submits SQL requests to the database, and returns results to the application. Read Database Middleware: Why It’s Important page 3-1 for detailed information about database drivers.

Where Are We Today?
Today, even when the database is tuned well, we know that database applications don’t always perform as well as we’d like. So, where does that leave us? Where do we find the performance issues today? For the majority of database applications, the performance issues are now found in the database middleware. For most applications, 75-95% of the time it takes to process a data request is spent in the database middleware—compared to 10 to 15 years ago, when the majority of the time was spent in the database management software.
In most cases, performance issues seen in database application deployments are caused by:

• The network
• Database drivers
• Software environment
• Poorly coded database applications

This book goes into great detail about database middleware and database application performance issues. For now, let’s look at a few examples.

The Network
One of the most common performance issues of the network is packet size. Network packets carry an application’s messages via the database middleware to the database and vice versa. The size of the packets makes a difference in the performance of your database application. The main concept to remember is that the fewer packets sent between the application and the database equates to better performance—fewer packets mean fewer trips to and from the database.

Think of it this way. Jim’s manager, Cindy, asks him to move five cases of diet soda from her second floor office to the first-floor kitchen. If Jim’s packet size is a six pack rather than a case, then he has to make 20 trips to the kitchen instead of five, which means he is taking longer to move the soda to the kitchen.

We discuss more about networks, how they affect performance, and what you can do to optimize the performance of a network in The Environment: Tuning for Performance page 4-1.

The Database Driver
All database drivers are not created equal. The choice of which driver to use in database application deployments can have a large impact on performance.

The following real-world scenario explains how one company solved their performance issues by changing only the database driver. DataBank serves the informational needs of companies, both large and small, through the retrieval, storage, and delivery of data. DataBank’s reputation and financial security depends on response time and system availability. They have contracts with their customers that require specific response times and system availability. If these requirements are not met, they must pay their customers a fine.

After a mandated upgrade to a new version of their database and its accompanying middleware, DataBank began having serious performance issues. They were routinely paying over $250,000 a month in fines due to missed contractual obligations.

The situation was unacceptable; the company had to find the performance issues in their database application deployment. They started by making sure that their database was optimally tuned. Even with the database performing well, the company was still missing their contractual service level requirements.

So, the system architect made a phone call to a database consultant, and the consultant asked, “Have you considered trying a different database driver?” The architect responded, “I didn’t even know that was an option.” The consultant recommended a database driver that he had used with success.

Losing no time, the architect had the recommended database driver installed in a test environment. Within two days, the QA department reported a 3x improvement in average response time between the new and the currently deployed database drivers and the elimination of stability problems.

Based on the results of their performance testing, DataBank moved forward to purchase the new database driver. After the new database driver had been deployed for a couple of months, Data-Bank analyzed the revenue they were saving.

DataBank was paying $250,000 in fines in September and reduced that to $25,000 by November. That is a savings of 90% in two months by simply changing the database driver. The new driver handled connection pooling and memory management more effectively than the old driver.

DataBank solved several issues by deploying a new database driver: loss of revenue, customer dissatisfaction, and overworked IT personnel, to name a few.

We discuss in detail the many ways that database drivers can affect performance and what you can do about it in Database Middleware: Why It’s Important page 3-1.

The Software Environment
To show how the software environment can affect performance, see Figure 1-4 for an example of how different Java Virtual Machines (JVMs) can cause different performance results for a JDBCenabled database application. In this example, the same benchmark application was run three times using the same JDBC driver, database server, hardware, and operating system. The only variable was the JVM. The JVMs tested were from different vendors, but were the same version and had comparable configurations. The benchmark measured the throughput and scalability of a database application.

As you can see in Figure 1-4, the throughput and scalability of the JVM represented by the bottom line is dramatically less than the other two JVMs.
We discuss how your software environment (such as the operating system and virtual machines) can affect performance in The Environment: Tuning for Performance page 4-1.

Your Database Application
Another important performance-related component is your database application. If your application is not coded efficiently, the data request that your application passes along to the database middleware can negatively affect performance. One common example is transaction management.

With most standards-based applications, the default transaction mode requires the database middleware to process expensive commit operations after every API request. This default “auto commit” mode can impose severe performance constraints on applications.

To illustrate this, consider the following real-world example. ASoft Corporation coded a standards- based database application and experienced poor performance in testing. Their performance analysis showed that their problem resided in the bulk five million Insert statements sent to the database. With auto-commit mode on, this meant an additional five million Commit statements were being issued across the network and that every inserted row was written to disk immediately following the execution of the insert. When auto-commit mode was turned off in the application, the number of statements issued by the driver and executed on the database server was reduced from ten million (5 million Inserts + 5 million Commits) to five million and one (5 million Inserts + 1 Commit). As a consequence, application processing was reduced from 8 hours to 10 minutes. Why such a dramatic difference in time? There was significantly less disk I/O required by the database server and there were 50% fewer network round trips.

In general, your database application should be written to:

• Reduce network traffic
• Limit disk input/output
• Optimize application-to-driver interaction
• Simplify queries

See the following chapters to learn about some general guidelines for good coding practices that improve database application performance:

• For ODBC users, see ODBC Applications: Writing Good Code page 5-1.
• For JDBC users, see JDBC Applications: Writing Good Code page 6-1.
• For ADO.NET users, see .NET Applications: Writing Good Code page 7