Using keys to improve query performance

Chapter 21. Monitoring and Improving Performance
Using keys to improve query performance

The foreign key and the primary key are used for validation purposes. However, these keys are also used by SQL Anywhere to improve performance where possible.

Example

The following example illustrates how keys are used to make commands execute faster.

     SELECT *
     FROM employee
     WHERE emp_id = 390

The simplest way for SQL Anywhere to perform this query would be to look at all 75 rows in the employee table and check the employee ID number in each row to see if it is 390. This does not take very long since there are only 75 employees, but for tables with many thousands of entries the search can take a long time.

The emp_id column is the primary key for the employee table. SQL Anywhere has a built-in index mechanism for finding primary and foreign key values quickly. (This mechanism is used for the validation you saw in "Validity checking".)

SQL Anywhere automatically uses the same mechanism to find the employee number 390 quickly. This quick search takes almost the same amount of time whether there are 100 rows or 1,000,000 rows in the table.

Using ISQL to examine query performance

The ISQL statistics window tells you when keys are being used to improve performance.

The number of lines in the statistics window can be adjusted or the window can be turned off altogether.

Changing the size of the statistics window

Under Windows, Windows NT and OS/2, the window can be resized in the standard manner. Click the mouse on the window border and drag it to the desired size. The window can be turned off by minimizing it. Restore the window by choosing Window->Statistics from the menu bar.

Under DOS and QNX, you can control the size of the statistics window with the SET OPTION STATISTICS command. The statistics window can also be turned on or off from the configuration window (choose Options->Configure from the menu bar). The Statistics option contains the number of lines in the statistics window. Use 0 to turn off the statistics window.

Information in the statistics window

If you execute a query to look at every row in the employee table:

     SELECT *
     FROM employee

two lines appear in the Statistics window:

75 rows in query (I/O estimate 1)

PLAN> employee (seq)

The first line indicates the number of rows in the query. Sometimes the database knows exactly, as in this case where there are 75 rows; other times it estimates the number of rows. The first line also indicates an internal I/O estimate of how many times SQL Anywhere will have to look at the database on your hard disk to examine the entire employee table.

The second line summarizes the execution plan for the query: the tables that are searched, any indexes used to search through a table. This plan says that SQL Anywhere will look at the employee table sequentially (that is, one page at a time, in the order that the rows appear on the pages). The letters seq inside parentheses mean that SQL Anywhere will examine all the rows of the table. This makes sense since the query fetches the entire table.

Resetting statistics

You may notice that when working through the tutorial yourself that the statistics window contains estimates than are different from what is given here. This may happen because the SQL Anywhere optimizer has decided to optimize a query differently. The optimizer maintains statistics as it evaluates queries and uses these statistics to optimize subsequent queries. These statistics can be reset by executing the following statement:

     DROP OPTIMIZER STATISTICS

Note that you must have DBA authority to execute this statement.

Using primary keys to improve query performance

SQL Anywhere uses a primary key to improve performance on the following statement:

     SELECT *
     FROM employee
     WHERE emp_id = 390

Statistic window information

The statistics window contains the following two lines:

Estimated 1 rows in query (I/O estimate 13)

PLAN> employee (employee)

Whenever the name inside the parentheses in the ISQL statistics window PLAN description is the same as the name of the table, it means that SQL Anywhere will use the primary key for the table to improve performance. Also, the ISQL statistics window shows that the database optimizer estimates that there will be one row in the query and that it will have to go to the disk 13 times.

Using foreign keys to improve query performance

The following query lists the orders from customer with customer ID 113:

     SELECT *
     FROM sales_order
     WHERE cust_id = 113

Statistic window information

The statistics window contains the following information:

Estimated 5 rows in query (I/O estimate 10)

PLAN> sales_order (ky_so_customer)

Here ky_so_customer refers to the foreign key that the sales_order table has for the customer table.

Primary and foreign keys are just special indexes that also maintain entity and referential integrity. The integrity is maintained by extra information that is placed in the indexes.