User's Guide

Part III. Using SQL Anywhere

Chapter 21. Monitoring and Improving Performance

This section uses sample queries to illustrate how SQL Anywhere selects an optimal processing route for each query. If you execute each of the commands in this section in ISQL, the statistics window display shows you the execution plan chosen by SQL Anywhere to process each query.

Using a key join

The following simple query uses a key join to search more than one table:

     SELECT customer.company_name, sales_order.id
     FROM sales_order
     KEY JOIN customer

The Statistics window displays the following:

     Estimated 711 rows in query (I/O estimate 2)

     PLAN> customer(seq), sales_order(ky_so_customer)

When this query is executed, the ISQL statistics window display indicates that SQL Anywhere first examines each row in the customer table, then finds the corresponding sales order number in the sales_order table using the ky_so_customer foreign key joining the sales_order and customer tables.

The order that the tables are listed in the statistics window is the order that the tables are accessed by the database.

Adding a WHERE clause

If you modify the query by adding a WHERE clause, as follows, SQL Anywhere carries out the search in a different order:

     SELECT customer.company_name, sales_order.id
     FROM sales_order
     KEY JOIN customer
     WHERE sales_order.id = 2583

The Statistics windows displays the following plan:

     PLAN> sales_order(sales_order), customer(customer)

Now, SQL Anywhere looks in the sales_order table first, using the primary key index. Then, for each sales order numbered 2583 (there is only one), it looks up the company_name in the customer table using the customer table primary key to identify the row. The primary key can be used here because the row in the sales_order table is linked to the rows of the customer table by the customer id number, which is the primary key of the customer table.

The tables are examined in a different order depending on the query. The SQL Anywhere built-in query optimizer estimates the cost of different possible execution plans, and chooses the plan with the least estimated cost.

For some more complicated examples, try the following commands which each join four tables. The ISQL statistics window shows that SQL Anywhere processes each query in a different order.

Example 1

To list the customers and the sales reps they have dealt with.

• Type the following:

       SELECT customer.lname,
       employee.emp_lname
  

       FROM customer
       KEY JOIN sales_order
       KEY JOIN sales_order_items
       KEY JOIN employee
  

lname	emp_lname
Colburn	Chin
Smith	Chin
Sinnot	Chin
Piper	Chin
Phipps	Chin

The plan for this query is as follows:

     PLAN> employee (seq), sales_order (ky_so_employee_id),

     customer (customer), sales_order_items (id_fk)

Example 2

The following command restricts the results to list all sales reps that the customer named Piper has dealt with:

     SELECT customer.lname,
     employee.emp_lname
     FROM customer
     KEY JOIN sales_order
     KEY JOIN sales_order_items
     KEY JOIN employee
     WHERE customer.lname = 'Piper'

The plan for this query is as follows:

     PLAN> customer (ix_cust_name), sales_order (ky_so_customer),

employee (employee), sales_order_items (id_fk)

Example 3

The third example shows all customers who have dealt with sales reps of the same name:

     SELECT customer.lname,
     employee.emp_lname
     FROM customer
     KEY JOIN sales_order
     KEY JOIN sales_order_items
     KEY JOIN employee
     WHERE customer.lname = employee.emp_lname

The plan for this query is as follows:

     PLAN> employee (seq), customer (ix_cust_name),

     sales_order (ky_so_employee_id), sales_order_items (id_fk)

For information on how SQL Anywhere's optimizer selects a strategy for each search, see "How the optimizer works".