Chapter 15
What's Slowing Your Query Down?

In This Chapter

• Tune-ups, timing, and performance
• Little bites versus wolfing your data
• The SELECT clause
• The FROM clause
• The WHERE clause
• The ORDER BY clause
• NOT in the WHERE clause
• IN versus EXISTS in the WHERE clause

Perhaps you write SQL code in your spare time. If so, you actually have no spare time. I suggest you get a life. On the other hand, you may use some additional software programs that generate all your queries for you. Everything goes well until one day you create a query that runs so slowly you consider taking it out and shooting it. The database administrator (DBA) starts yelling in your face. You take three Excedrin and grab the manuals.

This chapter is all about performance tuning, or speeding up your query. I cover the bases with useful information for tuning up your SQL code. This chapter and the others in this section will have your code flying like never before.

Baby Steps versus Giant Leaps

A SQL query or command can run slowly for many reasons. To discover important performance clues, ask yourself a couple of questions: "When did the problem start?" and "What did I change just before I started having trouble?"

When you create a complex SQL statement, you may be wise to take small bites out of the whole goal and then test each one as you go along. These steps can help you develop good habits for creating tricky SQL statements:

1. Build the statement one table at a time. Choose the main table and build the beginning statement using only the main table.
2. Test your beginning statement and make corrections.
3. Add another table, test the statement, and make corrections.
4. Continue adding tables, testing, and correcting until the SQL statement is completed.

Often you can catch trouble spots in your code when you gradually build the code. Taking baby steps gives steady, forward progress. Giant steps, in which you create an entire SQL command at once, tend to fall short.

SQL*Plus has a command to help you figure out whether you've improved the response time for your query. The next section discusses this command, called timing.

Set SQL*Plus Timing On

The timing command in SQL*Plus gives you great feedback on the performance speed of your SQL command. To turn the timing on, simply type:

set timing on

This is an environment command in SQL*Plus, meaning that it changes how SQL*Plus displays the results of your queries and other commands. An environment command stays on until you reset it or leave SQL*Plus. There are many environment commands. Some are described in this chapter, and others are described in Chapter 12.

To turn the timing setting on in SQL*Plus using Personal Oracle7 Navigator, start up SQL*Plus (refer to Chapter 4) and follow these steps:

1. Select the Options menu at the top of the window.
2. Select Set Options (Environment in Oracle 7.3).

3. This opens a window for setting all your environment settings, as shown in Figure 15-1.

4. Scroll to and select the timing setting and then click the On button. (In Oracle 7.3, first click the Current button, then click the On button.)
5. Click OK.

After you do that, every SQL command you execute is followed by statistics. If you're running Oracle7 on a PC, you see something like this:

real: 550

This is the real time in thousandths of a second. Other operating systems show different statistics, such as the CPU time or buffers used.

The first time you run a SQL command, Oracle7 reads new data into its buffers. The data stays in the buffers until the end of the session or until Oracle7 runs out of buffers and reuses the one with this data. Consequently, when you use the timing statistics to compare variations on similar queries, make sure that you run each of them a second time. By doing this, you get timing statistics that do not include the initial loading of the buffers.

Now you have a method for testing out the changes you make to your query in an attempt to speed it up. You can look at the slow query's timing statistics, make changes to the query, and compare this changed query's timing statistics. As a starting point, the next section dives into parts of the query you can explore to determine what is slowing your query down.

Looking at the SELECT Clause -- Too Much or Too Little

When your query does not work efficiently, you can take specific steps to inspect and adjust it to work efficiently. The sections here go through your query piece by piece. Each section points out the most common causes of inefficient SQL code and shows how to correct the situation.

Picking apart the FROM clause for little flaws

The FROM clause contains a list of all the tables from which you retrieve data. I think FROM clause is a perfect name for this clause, don't you? Oracle7 goes out of its way to say in its documentation that the order in which you choose to name your tables in the FROM clause is of no significance. Then, in very fine print, buried somewhere in the manuals, Oracle7 comes clean and explains a little-known quirk of the Oracle7 engine that helps you create better, faster SQL.

In most cases, Oracle7 ignores the order of the tables in the FROM clause. However, when Oracle7 needs a tiebreaker, it looks at the FROM clause. It chooses to use the index on the last table in the FROM clause and to ignore the index on the other table. If you list the larger table first, performance suffers, because Oracle7 does not use an index on this large table. That means it will scan the entire contents of the table. So you may want to rearrange the order of the tables in your query, placing large tables at the end.

When listing tables in the FROM clause, list them in ascending order according to the number of rows to be retrieved from each table when the query runs.

You are an alien visiting Earth on spec for your home company. You set up database tables for 100,000 new clients (CUSTOMER_ACCOUNT) who have shown interest in your unusual product and for the 50 countries (COUNTRY) that these clients live in. COUNTRY_ID links the two tables together. A query listing the customers and countries could take two forms.

The wrong way to query would be something like this:

select C.COUNTRY, A.NAME
from CUSTOMER_ACCOUNT A, COUNTRY C
where A.COUNTRY_ID = C.COUNTRY_ID
order by COUNTRY, NAME;

The right way to query would look like this:

select C.COUNTRY, A.NAME
from COUNTRY C, CUSTOMER_ACCOUNT A
where A.COUNTRY_ID = C.COUNTRY_ID
order by COUNTRY, NAME;

Why is the latter approach better? Because the FROM clause in the second query places the larger table (the CUSTOMER_ACCOUNT table) last and the smaller table first.

Missing table joins: The WHERE clause monster

When you write a query, the WHERE clause has two main functions:

• Listing criteria that narrow down the rows chosen (such as a date range)
• Specifying connections between two tables (matching foreign and primary keys)

If your query contains more than one table and the second function (specifying connections) is missing or incorrect, SQL*Plus does not issue an error message. Instead, SQL*Plus figures you know what you're doing and processes your query as if you wanted to match every column in the first table with every table in the second. If one table has ten rows and the other has 20, your end result has 200 rows. That's probably not what you intended! This method of matching all rows with each other is called a Cartesian join.

A Cartesian join is a set of all the possible combinations of every row in two or more tables. Its namesake is the French philosopher and mathematician René Descartes, who founded much of modern geometry. As you might imagine, a Cartesian join is usually the result of an error in your query and causes your query to return many more rows than intended, and as a result slows down your response time.

A Cartesian join occurs in these two cases:

• Two tables are named in the FROM clause and are not related to each other in the WHERE clause.
• The relationship that is defined is incorrectly formed.

Figure 15-2 shows how a Cartesian join looks with two small tables.

As the amount of data increases, the size of the Cartesian join monster grows exponentially. The number of rows produced by a Cartesian join is equal to the number of rows in each table multiplied by the number of rows in the other table.

If the query results seem to show up as repeated rows, you very likely have a Cartesian join. Review the WHERE clause for relationships between tables.

You hastily prepare a query of all your customers whose names begin with A, and their countries, but you forget to place the relationship portion of the WHERE clause into the query.

The right way to handle table connections in your WHERE clause in this example is:

select C.COUNTRY, A.NAME
from CUSTOMER_ACCOUNT A, COUNTRY C
where A.NAME like 'A%'
and A.COUNTRY_ID = C.COUNTRY_ID
order by COUNTRY, NAME;

The wrong way to connect tables might look like this:

select C.COUNTRY, A.NAME
from COUNTRY C, CUSTOMER_ACCOUNT A
where A.NAME like 'A%'
order by COUNTRY, NAME;

The first query works because you have included a proper phrase in the WHERE clause to tell SQL*Plus how to connect the two tables.

Extra table joins: Overkill that slows you down

Be careful when you create queries that join more than two tables. A common mistake is to include a condition in the WHERE clause that is unneeded and repetitive, which can happen when a hierarchy of tables exists.

Avoid extraneous joins in hierarchical table relationships. Follow relationship lines in diagrams as an aid.

You have three tables: CONTINENT, COUNTRY, and CITY. Each table's primary key builds on the previous one. Figure 15-3 shows the relationships among the tables. The line between the CONTINENT table and the CITY table is unneeded and should be omitted. However, this line shows how easily you can make an error when working with several related tables. The CONTINENT column is a foreign key column in the CITY table. You can interpret it as a foreign key to either the COUNTRY table or the CONTINENT table. Refer to Chapter 5 for a thorough explanation of primary keys and foreign keys.

As you write a query, you add a condition that relates the CITY table with the CONTINENT table. This is an unneeded condition that makes Oracle7 do more work when it runs the query -- and slows performance.

Here's your example with the proper joins in the WHERE clause:

select CITY.CONTINENT, CTRY.COUNTRY_NAME, CITY.CITY
from CONTINENT CON, COUNTRY CTRY, CITY
where CITY.COUNTRY_ID = CTRY.COUNTRY_ID
and CITY.CONTINENT = CTRY.CONTINENT
and CTRY.CONTINENT = CON.CONTINENT
order by 1,2,3

This incorrect one contains extraneous joins in the WHERE clause:

select CITY.CONTINENT, CTRY.COUNTRY_NAME, CITY.CITY
from CONTINENT CON, COUNTRY CTRY, CITY
where CITY.COUNTRY_ID = CTRY.COUNTRY_ID
and CITY.CONTINENT = CTRY.CONTINENT
and CITY.CONTINENT = CON.CONTINENT
and CTRY.CONTINENT = CON.CONTINENT
order by 1,2,3

The first query works better because it requires SQL*Plus to do less work in connecting the three tables together and still gets the right results.

The ORDER BY clause reduces Oracle7 to a crawl

The ORDER BY clause determines how Oracle7 sorts the rows it returns to you as the result of your query. The ORDER BY clause does not limit you to any particular column. In fact, you can include columns that have functions, such as concatenation or addition, on them as well. Be aware that anything in the ORDER BY clause that is not indexed slows performance.

Review your ORDER BY clause for columns or expressions that are not indexes, because these slow performance. Resolve the performance problem by rewriting the ORDER BY clause to use indexes or by creating another index on the column or columns that you use. See Chapter 17 for an in-depth discussion of indexing.

Your sales staff just notified you that sales have decreased. You want to see the trend over the last 30 days, so you sort your query by descending date. The date you want in your report is converted to YY/MM/DD format, which is your preference.

The right way allows SQL*Plus to use the index on SALES_DATE:

select to_char(SALES_DATE,'YY/MM/DD'), SALES_AMOUNT
from DAILY_SALES
WHERE SALES_DATE BETWEEN
TO_DATE('01-JAN-96') and TO_DATE('31-JAN-96')
ORDER BY SALES_DATE

The wrong way has an ORDER BY clause that uses no index:

select to_char(SALES_DATE,'YY/MM/DD'), SALES_AMOUNT
from DAILY_SALES
WHERE SALES_DATE BETWEEN
TO_DATE('01-JAN-96') and TO_DATE('31-JAN-96')
ORDER BY to_char(SALES_DATE,'YY/MM/DD')

The first query works much faster because an index is used to retrieve data. Without an index, the data is retrieved by reading through every row in the table.

The danger of NOT

All the phrases or parts of your WHERE clause contain some kind of logical operator such as = (equal) or < (less than). NOT is tacked on to reverse the logic of any of these operators. NOT is used explicitly by adding parentheses around a phrase and placing NOT just in front of it, like this:

... where not (STYLE = '4-door')

NOT is contained implicitly in one logical operator -- the NOT EQUAL operator. In other words, NOT is there even though you don't type the word into your query. Here are the three forms that you can use to write NOT EQUAL:

<>

This is the preferred method, because it is accepted by all the variations of Oracle7 across all hardware platforms.

!=

This form of NOT EQUAL does not translate between all platforms! Use the first form of NOT EQUAL (<>) instead.

^=

Again, this form of NOT EQUAL does not always translate between platforms. Use the first form of NOT EQUAL (<>).

You have great flexibility when using the NOT logical term in your query's WHERE clause, but you pay a price for using NOT -- Oracle7 cannot use an index on any phrase that contains NOT. Without an index to use, a query slows down. So don't use the NOT logical term in the WHERE clause. Just imagine if your doctor said, "You only have three days to live -- NOT!"

Here's an example of a query that works without NOT:

select * from COUNTRY
where COUNTRY_ID <= 100

Here's the same query using NOT:

select * from COUNTRY
where NOT (COUNTRY_ID > 100)

The first example runs faster than the second even though both get the same results. The first one allows Oracle7 to use an index that exists on the COUNTRY_ID column, while the second one prohibits use of the index.

IN versus EXISTS: Important facts you should know

Sometimes you compare a column to a list of values. A common way to create the list is to create a sub-query inside your WHERE clause. The basic method involves figuring out a sub-query that creates a list and then including that in the WHERE clause as a sub-query.

There are two general formats for a sub-query in a WHERE clause. The first one uses the IN logical operator and looks like this:

... where column in (select ... from ... where ... )

The second format uses the EXISTS logical operator and looks like this:

... where exists (select 'X' from ... where ...)

Most people use the first format because it is a lot easier to figure out. The second format actually gives you much better performance, if you take the time to learn how to create it. You will find that nearly every sub-query you create using the IN logical operator can be converted to use the EXISTS logical operator.

In the second format, the sub-query starts with select 'X'. This looks strange, but it is correct. EXISTS looks directly at the WHERE clause and does not even care what data you pull from the table you have in the sub-query. EXISTS uses a correlated sub-query, which is why it is more difficult to construct.

Chapter 12 discusses the correlated sub-query. Refer to that chapter for a good explanation of how to construct a correlated sub-query.

When you replace IN with EXISTS, you gain performance speed. Oracle7 saves time when it uses EXISTS because it evaluates the main query first and runs the sub-query only until it finds a match. With IN, Oracle7 suspends processing of the main query while executing the sub-query to make a list. Oracle7 stores that list in an indexed temporary table and then resumes processing of the main query. While there are exceptions, Oracle7 usually performs queries that use EXISTS faster than those that use IN. Therefore, examine your WHERE clause for the IN condition. In most cases, you can replace the IN condition with EXISTS.

Here's an example of a sub-query using EXISTS:

select *
from COUNTRY C
where exists (select 'X' from CITY where CITY.COUNTRY_ID = C.COUNTRY_ID)

Here's the same query using IN for the sub-query:

select * from country C
where C.COUNTRY_ID in (select CITY.COUNTRY_ID from CITY)

The first one gets the same results (the same rows retrieved) as the second one but runs faster because it uses EXISTS rather than IN.

Whenever possible, also replace NOT IN with NOT EXISTS. Even though both have NOT in them (which can slow performance down because it prevents the use of indexes), NOT EXISTS performs faster than NOT IN.