ASA SQL User's Guide
  Monitoring and Improving Performance

Sorting query results

Many queries have an ORDER BY clause that ensures that the rows appear in a predictable order. Indexes order the information quickly. For example, the following query can use the index on the lname column of the customer table to access the rows of the customer table in alphabetical order by last name:

SELECT *
FROM customer
ORDER BY customer.lname

Queries with WHERE and ORDER BY clauses 

A potential problem arises when a query has both a WHERE clause and an ORDER BY clause.

SELECT *
FROM customer
WHERE id > 300
ORDER BY company_name

Adaptive Server Anywhere must decide between two strategies:

1. Go through the entire customer table in order by company name, checking each row to see if the customer id is greater than 300.

2. Use the key on the id column to read only the companies with id greater than 300. The results would then need to be sorted by company name.

If there are very few id values greater than 300, the second strategy is better because only a few rows need to be scanned and quickly sorted. If most of the id values are greater than 300, the first strategy is much better because it requires no sorting.

For more information about sorting, see The ORDER BY clause: sorting query results, or The GROUP BY clause: organizing query results into groups.