Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Performance improvements, diagnostics, and monitoring » Performance » Tips for improving performance

Tip: Use indexes effectively

The database server can evaluate search conditions with the aid of indexes.

Using indexes speeds optimizer access to data and reduces the amount of information read and processed from base tables. For example, if a query contains a search condition WHERE column-name=value, and an index exists on the column, an index scan can be used to read only those rows of the table that satisfy the search condition. Indexes also improve performance dramatically when joining tables.

When executing a query, the database server chooses how to access each table. When the database server cannot find a suitable index, it resorts to scanning the table sequentially, a process that can take a long time.

For example, suppose you search a large database for employees, and you only know their first or last name, but not both. If no index exists, the database server scans the entire table. However, if you created two indexes (one that contains the last names first, and a second that contains the first names first), the database server scans the indexes first, and can return the information faster.

Proper selection of indexes can make a large performance difference

Although indexes let the database server locate information very efficiently, exercise some caution when adding them. Each index creates extra work every time you insert, delete, or update a row because the database server must also update all affected indexes.

Consider adding an index when it allows the database server to access data more efficiently. In particular, add an index when it eliminates unnecessarily accessing a large table sequentially. If, however, you need better performance when you add rows to a table, and finding information quickly is not an issue, use as few indexes as possible.

Use the Index Consultant to guide you through the selection of an effective set of indexes for your database.

Query optimization

Whenever possible, the optimizer attempts index-only retrieval to satisfy a query. With index-only retrieval, the database server uses only the data in the indexes to satisfy the query, and does not need to access rows in the table. The optimizer automatically chooses to use the indexes it determines will lead to the best performance. However, you can also use index hints in your query to specify the indexes you want the optimizer to use. If any of the specified indexes cannot be used, an error is returned. Index hinting can result in poor performance and should only be attempted by experienced users. Use the Index Consultant to determine whether additional indexes are recommended for your database.

Clustered indexes

Using clustered indexes helps store rows in a table in approximately the same order as they appear in the index.