10.3 Tuning SQL Statements
One of the main strengths of SQL is that you do not have to tell the database exactly how to obtain the data requested; you simply tell it what information you want, and the database figures out the best way to get it. Sometimes you can improve the performance of your SQL statements by tuning them. This section is not intended to be a comprehensive guide to SQL tuning, as this is a vast and complex subject, but it will give you a simple introduction. For further details, I recommend the books Oracle Performance Tuning and Oracle SQL: The Essential Reference (both published by O'Reilly).
Three simple ways to improve the performance of SQL queries are as follows:
Following these three simple tuning tips, which I describe in detail in the next few sections, should improve the performance of your SQL queries.
10.3.1 Using the WHERE Clause
Rather than selecting all the rows from a table into a SQLJ iterator and then accessing the iterator for just the rows you need, you should use a WHERE clause in your SELECT statement to limit the number of rows selected in the first place. In the following example, an iterator is used to retrieve all the rows from the products table. However, only the first two rows in the iterator are actually used by the program.
#sql product_iterator = { SELECT id, name, description, price FROM products }; while (product_iterator.next( )) { int id = product_iterator.id( ); if ((id == 1) || (id == 2)) { System.out.println("name = " + product_iterator.name( )); } } // end of while loop If you modify the query in this program to use a WHERE clause to restrict the rows retrieved to just those for products #1 and #2, you'll see an improvement in performance. This improvement will come about because you are no longer wasting the time and system resources required to retrieve all the rows in the table. For example, the following SQLJ statement populates an iterator with rows from the product table, but this time it includes a WHERE clause to restrict the iterator to only the rows for products #1 and #2:
#sql product_iterator = { SELECT id, name, description, price FROM products WHERE id <= 2 }; 10.3.2 Joining Tables
It is generally more efficient to perform table joins rather than using multiple queries when retrieving data from multiple tables. In the following code, two queries are used to get the product name and the product type name for product #1. The first query gets the name and type_id column values from the products table for product #1. These column values are selected into the host variables product_name and product_type_id. The second query then uses the product_type_id value to get the name column from the product_types table for the product type in question.
String product_name = null; int product_type_id = 0; #sql { SELECT name, type_id INTO :product_name, :product_type_id FROM products WHERE id = 1 } String product_type_name = null; #sql { SELECT name INTO :product_type_name FROM product_types WHERE id = :product_type_id } But rather than using the two queries just shown, you can write one query that uses a join between the products and product_types tables to retrieve the same information. The following example uses a join on the type_id column in the products table and the id column in the product_types table:
#sql { SELECT product.name, product_type.name INTO :product_name, :product_type_name FROM products, product_types WHERE product.type_id = product_types.id AND product.id = 1 } This results in the same product name and product type name being selected as before, but they are obtained using only one query. Executing one query is generally more efficient than executing two.
10.3.3 Using Indexes
When looking for a particular topic in a book, you can either scan the whole book looking for your topic, or you can use the book's index to find the exact location of the topic directly. An index for a database table is similar in concept to a book index, except that database indexes are used to find specific rows in a table. The downside of indexes is that when a row is added to the table, additional time is required to update the index for the new row.
Generally, you should only create an index on a column when you find that you are retrieving a small number of rows from a table containing many rows. A good rule of thumb is that an index is useful when you expect any single query to retrieve 10% or less of the total rows in a table. This means that the candidate column for an index should be used to store a wide range of values. A good candidate for indexing is a column containing a unique number for each record, while a poor candidate is a column containing only a small range of numeric codes such as 1, 2, 3, or 4. This consideration applies to all database types, not just numbers. One point worth noting is that Oracle automatically creates an index for the primary key column of a table.
Normally, the DBA is responsible for creating indexes. However, as an application developer, you can provide the DBA with feedback on which columns are good candidates for indexing, since you know more about the application than the DBA does.
The SQL DDL statement CREATE INDEX is used to create an index on a column. In this section, I guide you through the thought processes you should follow when creating an index, and show you an example of a CREATE INDEX statement being used to create an index.
Assume for the moment that the customers table contains a large number of rows and that you regularly retrieve rows from that table using the last_name column in a WHERE clause:
SELECT id, first_name, last_name FROM customers WHERE last_name = 'Stevens';
Also assume that the last_name column contains fairly unique values, so that any query using the last_name column in a WHERE clause will return 10% or less of the total number of rows in the table. Therefore, the last_name column is a good candidate for an index on the customers table. The following CREATE INDEX statement, then, creates an index named customers_last_name_index on the last_name column of the customers table:
CREATE INDEX customers_last_name_index ON customers(last_name);
Once this index is created, queries such as the previous one that search for specific last names should take less time to complete than was required without the index.
