Chapter 2. Optimizing Application Design

In this chapter, we will optimize the application design, introducing various kinds of issues and hints to improve an application's performance. We will present the following recipes:

• Optimizing connection management

• Improving performance by sharing reusable code

• Reducing the number of requests to the database using stored procedures

• Reducing the number of requests to the database using sequences

• Reducing the number of requests to the database using materialized views

• Optimizing performance with schema denormalization

• Avoiding dynamic SQL

Introduction

It is very difficult to change the application design once the development process begins.

Often the primary aim of a software and data architect is to make things work, but designing applications for optimal performance is not a marginal aspect, many applications need to meet specific timing requirements to be useful.

In this chapter, we will investigate some aspects to keep in mind when designing an application and some tips on specific database features, which can help us in this task.

We will start inspecting the database connection phase, and then move on to general use of SQL statements in our applications for performance enhancement.

Recipes on useful database objects will follow, and the chapter will close with schema denormalization and dynamic SQL.

Optimizing connection management

In this recipe, we will see how to manage a database connection in our application, using Java.

Getting ready

To execute the source code we need the java compiler javac and the java runtime environment installed.

Note

Make sure that jdbc\lib\ojdbc6.jar is in the CLASSPATH environment variable. The jdbc folder is located under the Oracle home directory.

To set environment variables in Microsoft Windows environments, right-click on My Computer, select Properties, then navigate to the Advanced button or link—depending on the OS version—and click on Environment Variables and find the CLASSPATH environment variable. If you don't find it, click on the New button and enter the variable name CLASSPATH and variable value %ORACLE_HOME%\jdbc\lib\ojdbc6.jar. If the variable is already defined, click on the Edit button and enter the string %ORACLE_HOME%\jdbc\lib\ojdbc6.jar after the current value.

In Linux environments, export the variable CLASSPATH using the following command...

Improving performance sharing reusable code

In this recipe, we will see how to share reusable code in our application to improve performance.

Getting ready

To demonstrate the performance gain by sharing reusable code, the following example is written in Java, similar to the one presented in the previous recipe.

How to do it...

The following steps will demonstrate how to share reusable code:

1. Create a OraclePerformanceTuningCookbook directory and a chapter02 directory inside it.

2. Open your preferred text editor.

3. Create a class called SharedCode in the package chapter02 using the following code and save it in a file named SharedCode.java in the previously created chapter02 directory:

   package chapter02;
   import java.sql.*;
   
   public class SharedCode {
       private static final String driver = 
        "oracle.jdbc.driver.OracleDriver";
       private static final String connectionString = 
        "jdbc:oracle:thin:@localhost:1521:TESTDB";
       private static final String user = "hr";
       private static final String...

Reducing the number of requests to the database using stored procedures

To achieve better performance, we should reduce the number of requests made to the database, especially if those requests have to be routed to a network. There are many strategies to reduce these requests. In this recipe, we discuss the use of stored procedures and packages for achieving this goal.

In this recipe, we execute a simple query in the SH schema. In the first script, we will use SQL*Plus to test the SQL statement and the corresponding stored procedure execution. In the Java program, we will use the same query and stored procedure. For each of these tests, record the execution time.

How to do it...

Th e following steps will demonstrate how to reduce the number of requests to the database:

1. Open your preferred text editor and copy the following script, and save it as StoredProcedure.SQL:

   SET ECHO OFF
   SET FEEDBACK OFF
   SET PAGESIZE 80
   CREATE OR REPLACE PROCEDURE SH.SALES_BY_PRODUCT(P OUT SYS_REFCURSOR) IS
   BEGIN
    ...

Reducing the number of requests to the database using sequences

In this recipe, we continue to explore ways to reduce the number of requests made to the database, illustrating how the use of sequences can help us in achieving this as well as improved database scalability.

Sequences are used to assign a sequential number—unique until the sequence is recreated or reinitialized. In many non-Oracle databases, there are tools that allow developers to automatically assign a sequential number to a field—often the primary key—the so-called autoinc fields (Microsoft® SQL Server® and IBM® DB2® can define a field IDENTITY, MySQL™ has the AUTO_INCREMENT attribute, and so on).

Oracle database doesn't have a specific IDENTITY field, to achieve the same result developers have to write a trigger for the table to assign a value to the "autoinc" field, using a sequence. This behavior, however, allows developers to implement whatever policy they want while generating the autoinc field. Sequences can also be...

Reducing the number of requests to the database using materialized views

In this recipe, we will see how to increase the performance of the database—especially in a data warehousing environment—but the same recipe can be used with small changes in an OLTP environment as well by using materialized views.

Materialized views can be seen as snapshots of the data in one or more tables, on which a computation has been applied, for example, a join or a group. This summary data can be used to answer client queries readily, instead of reading all the data in the original table(s). An example is worth a thousand words. For example, we have a SALES table in SH schema, containing around 1 million rows, and we want a report of sales by product. We will see how materialized views, in such cases, can help a lot in reducing access to the database, specially the I/O.

How to do it...

We will use SQL*Plus to test a simple script:

1. Connect to the database TESTDB as user SH and execute a simple query on the sales...

Optimizing performance with schema denormalization

In this recipe, we will see how schema denormalization can help improve database performance, and what should be done before executing this operation.

Getting ready

We will implement a database schema representing a group of friends and their phone numbers. The following are the requirements for the database:

• For each friend, we want to store the name, surname, and gender

• Each friend may have multiple phone numbers

• For each phone number, we want to know its type of usage (home, work, mobile, and so on)

• A phone number can be shared by more than one friend, for example, Mrs. and Mr. Smith will share the same home number—at least until they get divorced

• For each phone number we want to store, we need to know its availability, that is, working hours, evening, afternoon, weekend only, and so on

The following is the logic schema that we will implement to satisfy the requirements mentioned earlier:

How to do it...

The following steps will demonstrate schema...

Avoiding dynamic SQL

The title of this recipe should be extended to say "… when you can do your stuff without using it". In this recipe, we will see when and how to use dynamic SQL.

Dynamic SQL is the only choice when:

• We want to execute DDL statements in our application.

• We have to code different queries depending on user input, for example, a search form with different search criteria that the user can choose from. This leads to different predicates in the WHERE clause.

• We want to code generic procedures, which can act on any table, for example, a generic "print" procedure, which shows the content of a table in a certain format.

For each of these situations, there are drawbacks to be taken care of.

How to do it...

To execute DDL statements in our application, we cannot use static SQL inside PL/SQL code. So, if we want to grant the RESOURCE role to the user SH, we have to do something similar to the following:

BEGIN
  EXECUTE IMMEDIATE 'GRANT RESOURCE TO SH'
END;

To search the EMPLOYEES table...